DTS Scheduling in SQL 2005

Hi all,

I am trying to create scheduled job for DTS. I have created new DTS in SQL 2005 but not able to create job out of it.

I tried creating Credential and then to create proxy with this:
Use master

CREATE CREDENTIAL [MyCredential] WITH IDENTITY = 'yourdomain\myWindowAccount', secret = 'WindowLoginPassword'

Use msdb

Sp_add_proxy @.proxy_name='MyProxy', @.credential_name='MyCredential'

Sp_grant_login_to_proxy @.login_name=' devlogin', @.proxy_name='MyProxy'

Sp_grant_proxy_to_subsystem @.proxy_name='MyProxy', @.subsystem_name='SSIS'

but could not get the job to schedule it. when i try to search for the DTS package(TestRev) it could not find it:

Is there a way that i can just call TSQL statement to run that dam DTS because it is too confusing to create job for DTS.

ThanksI have created new DTS in SQL 2005No, you didn't. DTS is not supported in 2005. Please clarify, and maybe we can answer you question.|||I mean to SSIS packages and would like to schedule it to run in job,|||OK, the job scheduler is a bit confusing, but it is going to be around for a while so you might as well bite the bullet and jump in.

And scheduling a package is not very difficult.

In the job scheduler, name your package and then set the type to "SQL Server Integration Services Package".

Set the package source in the General tab at the bottom of the window. (Not the General tab on the left of the window. I agreed it was a bit confusing...)|||i tried that and it says that the specified package could not be loaded from SQL server Database.

I select SQL server and then name of server and then name but not able to locate it.

thanks|||Where did you save your package? If you saved it to a file, you will need to point to the file name.|||I just started playing with SSBIDS

What a gdman pig|||i saved it on Server.

DTS Scheduled job always fails

Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!Did you ever find a solution to this problem? I am having the same problem.

Originally posted by multiplex77
Hi,

I created and saved a DTS package to "SQL Server". When I "Execute Package" under the "Data Transformation -> Local Packages" folder in Enterprise Mgr, it executes perfectly.

However, when I Schedule it, the scheduled job Fails. The View Job History shows "The job failed. The Job was invoked by User sa. The last step to run was step 1 (ImportTest3)."

Could someone please help me with this? I don't understand why the manual execution works, but the scheduled job doesn't. Thanks for the help!|||You execute under different security contexts when executing a dts package manually versus scheduling it as a job. What is your dts script doing ?|||I set up a number of packages to process selected cubes in Analysis Services. The first job processes all dimensions and even that one fails.|||What message is returned for the failure ?|||ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||True, the problem is exactly as described by multiplex77. the only jobs that fail are the ones that are processing OLAP cubes/dimensions

Originally posted by rnealejr
ptcooper - In your case, you can execute the package and it succeeds - but when you schedule it as a job, it fails - is this true ?|||Who is the owner of the job ?|||The owner is sa and the password was specified when the DTS packages were saved. SLQ Server Agent is logged on as administrator.

Originally posted by rnealejr
Who is the owner of the job ?|||log the package and post the message(s) returned in the log.|||I don't see a way to log the package. I've never had to do that.
Originally posted by rnealejr
log the package and post the message(s) returned in the log.|||If the SQL Agent is running as the local administrator, is the local administrator a member of the local Olap Administrators group? Not sure if it defaults to this.|||The SQlSERVERAGENT does not log on as the local administrator. It logs on as a system wide administrator. We have been looking for the OLAP administrator group in the 'security' tab and the 'member of' tab for the administrator, but don't find it listed in any of the drop down lists. We have all the latest service packs installed.|||The Olap Administrators group is a local group created by the Analysis Services install. Anyone who is not a member of this group can not use the SQL Server tools to manage, modify, or otherwise peek at the cubes on that machine. Remember this only applies to the MMC tool. The OLAP Administrators group will not show up in the domain-wide dropdown lists, since it is local only to one machine. Does that help?|||Right click your "My Computer" -> Manage -> Local Users and Groups -> Groups -> Olap Admin ... Add the account that is used to run sql server agent.|||UPDATE: We discovered that the OLAP Administrator group had somehow been deleted from the Active directory. We tried re-installing service pack 3 for Analysis Services, recreating the OLAP Administrators group and setting the members, properties etc. None of this worked. We just re-installed Analysis Services and service pack 3 on the server and now the jobs will run from the workstation in EM|||The OLAP administrators group is not in AD. Never was. It is local to the Analysis Services machine.|||Our Analysis Services server is a Domain controller, and as such cannot use the Local Users.. snap in. You get directed to the Active Directory Users and Computers when you try opening it. (also ref. MS KB article 829738 'More Information')|||Once you installed it as a domain controller in ad, the local users/groups do not transfer - which is why the olap admin group never appeared. I am a little curious that other problems did not appear before this problem.

Thanks for posting the follow-up with the solution.

DTS Scheduled Job

I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.DTS is a client tool and thus when you run it from EM it's running
on your pc. If the server doesn't have an S drive then you'll get this
error. It's best to use UNC paths to avoid ambiguity. Also check out
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q269074
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"L Dominguez" <ldominguez@.cardionet.com> wrote in message
news:02af01c34730$9a2823b0$a601280a@.phx.gbl...
I am getting the following error whenever I execute
the job via sql server job agent. When I execute
the job package manually via dts the result is good.
Should I mount the physical drive to the main server?
Executed as user: card\sql. DTSRun: Loading... DTSRun:
Executing...
DTSRun OnStart: Copy Data from Results to S:\test.xls
Step DTSRun OnError: Copy Data from Results to
S:\test.xls Step,
Error = -2147467259 (80004005)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
Error Detail Records: Error: 3 (3); Provider Error:
3 (3)
Error string: Error opening datafile: The system cannot
find the path specified.
Error source: Microsoft Data Transformation Services Flat
File Rowset Provider
Help file: DTSFFile.hlp Help context: 0
DTSRun OnFinish: Copy Data from Results to S:\test.xls
Step
DTSRun: Package execution complete. Process Exit Code
1.
The step failed.

DTS schedule job fails urgent pls help!

Hi all,

We have created DTS jobs which copies data from one server to another and truncates the destination before copying the data from source. when we run the job manually it runs fine, but when schedule its failing with the below errors.

DTSRun: Loading...DTSRun: Executing...DTSRun
OnStart: DTSStep_DTSExecuteSQLTask_1DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1DTSRun OnStart: Copy Data
from CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun OnError: Copy Data from CustomerMaster
to [OUTBOUND].[dbo].[CustomerMaster]
Step, Error = -2147008507 (80074005) Error string:
Unspecified error Error source: Microsoft Data
Transformation Services (DTS) Package Help file:
sqldts.hlp Help context: 1100Error Detail
Records:Error: -2147008507 (80074005); Provider
Error: 0 (0) Error string: Unspecified error
Error source: Microsoft Data Transformation Services
(DTS) Package Help file: sqldts.hlp Help context:
1100Error: -2147467259 (80004005); Provider Error:
0 (0) Error string: Timeout expired Error source:
Microsoft OLE DB Provider for SQL Server Help file:
Help context: 0DTSRun OnFinish: Copy Data from
CustomerMaster to
[OUTBOUND].[dbo].[CustomerMaster]
StepDTSRun: Package execution complete.
please suggest to over come this problem.

TIAMy guess is that the DTS job is scheduled to run as a different NT logon than the one you are using to test it interactively. The scheduled logon is probably missing one or more permissions needed for the DTS package.

-PatP

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
Thank you,
Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,
|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>
|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution..
.
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:

> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> it
> Agent
> solution...
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows[vbcol=seagreen]
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
>
job,[vbcol=seagreen]

DTS runs OK, but not scheduled job

Hi,
When I run DTS manually, it works fine. But when I run the scheduled job, it
failes.
The error said cannot find a file specified. It imports Excel file to
SQL2000 Server database. I set same domain user id for DTS creater and Agent
executer and job owner.
I read several articles same problem like this, but I haven't get solution...
--
Thank you,Masako
Where is located the EXCEL File?
It should be located on server and not on the your workstation.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> Hi,
> When I run DTS manually, it works fine. But when I run the scheduled job,
it
> failes.
> The error said cannot find a file specified. It imports Excel file to
> SQL2000 Server database. I set same domain user id for DTS creater and
Agent
> executer and job owner.
> I read several articles same problem like this, but I haven't get
solution...
> --
> Thank you,|||Hi Uri,
Does it have to? The Excel file is located on another server.
I had no problem like this job flow previous SQL Server. We used to Windows
NT server + SQL7, now new server is Windows2000 english version + SQL2000
Japanese version.
"Uri Dimant" wrote:
> Masako
> Where is located the EXCEL File?
> It should be located on server and not on the your workstation.
>
> "Masako" <Masako@.discussions.microsoft.com> wrote in message
> news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > Hi,
> >
> > When I run DTS manually, it works fine. But when I run the scheduled job,
> it
> > failes.
> > The error said cannot find a file specified. It imports Excel file to
> > SQL2000 Server database. I set same domain user id for DTS creater and
> Agent
> > executer and job owner.
> >
> > I read several articles same problem like this, but I haven't get
> solution...
> >
> > --
> > Thank you,
>
>|||Maskao
Make sure that SQL Server Agent is running under Domain Account not a Local
account.
"Masako" <Masako@.discussions.microsoft.com> wrote in message
news:CC48C0DC-A9F9-4E3A-8277-F6C55BF6C588@.microsoft.com...
> Hi Uri,
> Does it have to? The Excel file is located on another server.
> I had no problem like this job flow previous SQL Server. We used to
Windows
> NT server + SQL7, now new server is Windows2000 english version + SQL2000
> Japanese version.
> "Uri Dimant" wrote:
> > Masako
> > Where is located the EXCEL File?
> > It should be located on server and not on the your workstation.
> >
> >
> > "Masako" <Masako@.discussions.microsoft.com> wrote in message
> > news:949CB8A5-FC52-4D2D-9208-F8FD750EE40C@.microsoft.com...
> > > Hi,
> > >
> > > When I run DTS manually, it works fine. But when I run the scheduled
job,
> > it
> > > failes.
> > > The error said cannot find a file specified. It imports Excel file to
> > > SQL2000 Server database. I set same domain user id for DTS creater and
> > Agent
> > > executer and job owner.
> > >
> > > I read several articles same problem like this, but I haven't get
> > solution...
> > >
> > > --
> > > Thank you,
> >
> >
> >

DTS runs manually, fails as scheduled job

I've mined countless threads all over the Net where people are having my problem, but none of the solutions I've seen seem to apply.

When I run a DTS package from Enterprise Manager, everything works great. When I schedule the same package as a job, it fails immediately and reports this error:

Executed as user: SERVER-NAME\SYSTEM. DTSRun: Loading... Error: -2147221005 (800401F3); Provider Error: 0 (0) Error string: Invalid class string Error source: Microsoft Data Transformation Services (DTS) Package Help file: sqldts80.hlp Help context: 713. Process Exit Code 1. The step failed.

My hunch is the SERVER-NAME\SYSTEM.DTSRun account doesn't have sufficient privileges, but I can't find how/where to fix that.

Help, please.

Thanks,

Doug

Checkout the following links

http://www.sqlmag.com/articles/index.cfm?articleid=46723& - Security in SSIS
http://support.microsoft.com/?kbid=918760 - An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

Thanks,
Loonysan

|||

"Invalid class string" is not strictly a SQL Server error in DTS. Essentially it means in cannot find the CLSID for a required object when reading the package. This error can also occur when loading a package that contains a CustomTask which has not been registered on the current machine. (http://www.databasejournal.com/features/mssql/article.php/1461391)

PRB: "Invalid class string" error message when opening DTS package
(http://support.microsoft.com/?scid=kb%3Ben-us%3B288910&x=15&y=16)

The top two hits I got from Google, both say pretty much the same thing. Something is missing from your server, which causes the "Invalid class string", or "I cannot find your task".

FYI, general DTS info on jobs-

INF: How to Run a DTS Package as a Scheduled Job
(http://support.microsoft.com/kb/269074/en-us)

DTS runs great... but not from the job list

I have a DTS package that updates a database from a text file and it works
great when I run it manually from the DTS designer. However, I right click
on it and set it up to run automatically and it will not run. I went to
view the history of the job messages and it appears that the following is
the error for this task:
Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
Error = -2147467259 (80004005) Error string: Error opening datafile:
The network name cannot be found. Error source: Microsoft Data
Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 Error Detail Records: Error:
67 (43); Provider Error: 67 (43) Error string: Error opening
datafile: The network name cannot be found. Error source: Microsoft
Data Transformation Services Flat File Rowset Provider Help file:
DTSFFile.hlp Help context: 0 DTSRun OnFinish:
DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
Exit Code 1. The step failed.
Can anyone give me any clues as to what the problem could be?
Schoo
Have a look at this KB that details the common reasons a scheduled DTS
package will fail
INF: How to Run a DTS Package as a Scheduled Job
http://support.microsoft.com/default...;en-us;Q269074
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Schoo" <scott.schuman@.nospam.ma-hc.com> wrote in message
news:%23enkcwUrEHA.452@.TK2MSFTNGP09.phx.gbl...
>I have a DTS package that updates a database from a text file and it works
> great when I run it manually from the DTS designer. However, I right
> click
> on it and set it up to run automatically and it will not run. I went to
> view the history of the job messages and it appears that the following is
> the error for this task:
> Executed as user: MAHC_SQL2\SYSTEM. DTSRun: Loading... DTSRun:
> Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun
> OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1,
> Error = -2147467259 (80004005) Error string: Error opening datafile:
> The network name cannot be found. Error source: Microsoft Data
> Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 Error Detail Records: Error:
> 67 (43); Provider Error: 67 (43) Error string: Error opening
> datafile: The network name cannot be found. Error source:
> Microsoft
> Data Transformation Services Flat File Rowset Provider Help file:
> DTSFFile.hlp Help context: 0 DTSRun OnFinish:
> DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process
> Exit Code 1. The step failed.
> Can anyone give me any clues as to what the problem could be?
> Schoo
>
|||Hi Schoo,
Thanks for you post and I would love to show my gratidue for MVP Jasper
Smith's providing that great KB:Q269074
From your Error Message, I foudn the error was "The network name cannot be
found" so that please check whether there is anything wrong with your
source and target of database. Make sure they are "online", also please
check the permission issue. It seems strange and I need more information to
make further troubleshooting.
Would you please generate a small DTS package and see whether it works
fine? To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||Hi Schoo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

DTS re-uses old text file input

Hi All,

A simple DTS job I have is giving me fits. It is a straight copy column job from a pipe delimited text file into a table. The input file comes from a mapped drive linked to a shared filesystem on a sun solaris box.

The typical scenario. I run the DTS job to load 8000 rows from the input text file. Job succeeds.

A week later, the text file is updated with 9000 new rows. I run the DTS job with no changes and it loads 8000 rows from last week.

I reboot my Win XP pc and run DTS again. It now loads the 9000 new rows.

I tried mapping to a UNC to no avail.

Is it buffering the old file somewhere? I need help.

current environment:
SQL Server 2000 with all latest SP's and patches
Windows 2000 Server with all latest SP's and patches
Drive 'G' mapped to a shared filesystem on sun solaris via Samba?I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.|||I don't think DTS will use such type of cache and read old settings, ensure the source file path is defined correctly and oin the next execution it may be reading old settings.

Thanks Satya,

I'm not sure what you mean by "old settings"? Here's what I do know.

Drive 'G' is mapped to \\sun001\data

The input file name is weekly_updates.txt and does not change.

My DTS connection 1 properties uses
G:\weekly_updates.txt in the File Name box.

It always works correctly after a reboot, and more specifically, from any pc, not just mine. Any idea's?|||Might it be somehow related to Enterprise Manager? I wonder if there is a way to automatically force everything in EM to refresh. I dislike having to submit a job and then keep hitting refresh to see when it completes. I'm wondering if the "old" information stored in EM is related to my DTS issue?

DTS question.

Hi,

i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.

Thanks in advance.Originally posted by johan_gr
Hi,

i want to transfer data from an Access database to
a SQL Server database. I have created a DTS package
that does that job. The problem is that every time it is
executed, it transfers all the data of Access db, and
because it tries to add data that already exist in a table,
results in failure (because of keys...).
So, my question is if there is any way of transfering only
the updated data which (the way) is not based on checking
row by row the destination and source tables.
In example if from access could know which rows has updated
during last day.

Thanks in advance.

If you have a date and time value it could be done in your query..

WHERE date = > 'dateyourchecking'

Ken|||GA_KEN is right; it will generally mean substantially more work for you than just using the DTS Wizard however. For each table that you want to port to SQL Server, you will have to write a query (after determining what logic to use to assure that duplicate records are not brought across). Although often times this is a pretty straightforward task, there are a lot of complicating factors. In some cases, it may be preferable to simply truncate (delete * from mytable in Access) the existing table and re-load it from scratch.

Good luck,

Hugh Scott|||but im not an expert on that, i dont have time for that kindof thing...

cant you just reimport the whole table from scratch?

or do some sort of 2-stage import, where you just import recently changed records and then you push these into the production server on the database side (by using a stored procedure-- insert into dbo.productionTbl select * from AccessImport where date > (Select Min(Date) from myDateCatcher)

DTS Question

I have a DTS job that exports into a tab-delimited file. The tab delimiting
is working great BUT it also using quotes around the fields as well. (I
assume because they are string vars). Can this quoting "feature" be turned
off?

ThanksThank you, Ray. That did it!
"Ray Higdon" <rayhigdon@.higdonconsulting.com> wrote in message
news:3f280abb$0$197$75868355@.news.frii.net...
> Yes, open the connection to your text file in dts designer and go to
> properties, there you will be able to specify if you want double-quotes
> as a text qualifier,row-delimiters, column delimiters, etc.
> HTH
> Ray Higdon MCSE, MCDBA, CCNA
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

DTS Pkg Job error

I recently installed a new SQL Server and am having problems scheduling my DTS packages. They will work fine if I manually execute them, but none of them will fire from a job.

Regardless of the DTS package scheduled it fails with the following error:

Executed as user: STANDREWS\SYSTEM. The process could not be created for step 1
 of job 0x8D49E4684F14CB49BFC9223F4E47FC16 (reason: The system cannot find the file
specified). The step failed.

I have tried all kinds of stuff to no avail.What does step 1 look like? Did you try specifying the entire path for dtsrun.exe?

Terri|||when they fire from a job, they are firing under the security context of the account that the SQL Agent uses.

Therefore i would conclude that your account has access to something that the SQL Agent account does not.

you could set the SQL Agent to use a specific machine account...|||Yeah, created the job in various fashions, by name, by GUID, etc, etc. I am not certain of the absolute file path though, where is that found?

I will also check the SQL Agent security.|||The absolute file path is the physical path to dtsrun.exe on the SQL Server. On my local SQL Server, e.g., it can be found here: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\dtsrun.exe

I am bringing this up because I have come across this problem in the past.KB 322746 - PRB: Data Transformation Services Jobs May Fail Because the Path for Dtsrun.exe Cannot Be Found addresses this.

Terrisqlsql

DTS Packages are not working as job

Hello,

I scheduled a dts package and it created automatically a job but the job fails with following error :
(reason: The system cannot find the file specified).

dts package is connecting the databases which exist on the server and I created this dts package on the same server as user sa.

I don't use a file as a data source or for something else in the package.

Server is SQL 2000 with SP3 and operating system is win2k.

I hope there is someone who has an idea...

elifElif:

What EXACTLY is you package doing? In other words, are you using ActiveX + VBS to create and Run the job?

RobbieD|||My package is copying a database into another one and changing some characters in some fields. I added a vb code to transformations screen for this.
The package works fine when executed. Problem is scheduling. I want to execute it at night because the people are working one of the databases which dts uses.

If I can not schedule this I have to schedule my own night or weekend. This is ridiculous.|||...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?|||Originally posted by robbied111
...SO you have already right-clicked the package in Enterprise Manager, selected Schedule Package - but that doesn't work, right?

It worked and created the job. The problem is the "job". That does not work.|||I recall our DBA having the same issue:

I believe that the job may need to be scheduled directly on the server with the logged in user having admin priviledges on the server. It had less to do with SQL Server but was an effect of Windows 2000.

I may be offbase but that is what he found.

Originally posted by eguven
It worked and created the job. The problem is the "job". That does not work.|||The weather was stormy on friday, so we did not work.
Thanks for responses.
I'm working directly on the server and I'm user 'sa'.
I also prepared and scheduled the dts package on the server.
And this job does not work.
I have read something on the msdn related the user priviliges problem with this issue. That's why I did everything on the server as they said.
There is something else in my problem. I don't really understand.|||Dear friends,

I solved by myself this crazy problem. I wanted to share solution with you. When I scheduled the dts package, a job was created automatically. But it uses DTSRUN utility to run the package. This utility can only be run in the "c:\Program Files\Microsoft SQL Server\80\Tools\Binn " directory. I don't know why... But there is no path information in the job definition. That's why I recevied "File not found" message. I added this path to job and it worked. No need to be smart when working with a microft. We must just think simple.

bye

elif|||You might have removed it from the path variable while making some config settings ... otherwise it is automatically added when you install sql server .. You can view and set the path variables in System properties .. Advanced Tab ... Environment Variables ... No need to blame MS for your own fault. :)

DTS Packages

I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three step
SQL job that:
1. truncates data
2. runs a DTS package
3. runs a stored procedure
In 2000 I ran DTSRUNUI to generate an encripted command that generated a
GUID line. This was the line in I inserted into step two listed above.
In SQL 2005 I installed the dts compatability stuff but it does not give me
the DTSRUNUI application. Any suggestions other than use SSIS?
Thanks
Titan
Hi
Why don't you run the package using it's name?
John
"Titan" <Titan@.discussions.microsoft.com> wrote in message
news:380EC2B4-133A-4F4A-BA11-2677D1F066DD@.microsoft.com...
> I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three
> step
> SQL job that:
> 1. truncates data
> 2. runs a DTS package
> 3. runs a stored procedure
> In 2000 I ran DTSRUNUI to generate an encripted command that generated a
> GUID line. This was the line in I inserted into step two listed above.
> In SQL 2005 I installed the dts compatability stuff but it does not give
> me
> the DTSRUNUI application. Any suggestions other than use SSIS?
> --
> Thanks
> Titan

DTS Packages

I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three step
SQL job that:
1. truncates data
2. runs a DTS package
3. runs a stored procedure
In 2000 I ran DTSRUNUI to generate an encripted command that generated a
GUID line. This was the line in I inserted into step two listed above.
In SQL 2005 I installed the dts compatability stuff but it does not give me
the DTSRUNUI application. Any suggestions other than use SSIS?
--
Thanks
TitanHi
Why don't you run the package using it's name?
John
"Titan" <Titan@.discussions.microsoft.com> wrote in message
news:380EC2B4-133A-4F4A-BA11-2677D1F066DD@.microsoft.com...
> I'm moving to SQL Server 2005 on a new box. In SQL 2000 I have a three
> step
> SQL job that:
> 1. truncates data
> 2. runs a DTS package
> 3. runs a stored procedure
> In 2000 I ran DTSRUNUI to generate an encripted command that generated a
> GUID line. This was the line in I inserted into step two listed above.
> In SQL 2005 I installed the dts compatability stuff but it does not give
> me
> the DTSRUNUI application. Any suggestions other than use SSIS?
> --
> Thanks
> Titansqlsql

DTS package won't run as a scheduled job

Hi,
We have recently begun to experience a problem that is stumping us. We
have a DTS package that is run every morning as a scheduled job but
always fails. The job runs perfectly fine when kicked off manually
(either all at once from the Enterprise Manager or one step at a time
from DTS). The job history says it failed on step 1, but we've
determined that at least the first few steps run successfully. I've
attempted to turn package logging on but we never get any results (I've
tried to save the log both in the SQL Server log as well as a local
text file ... nothing ever appears in either location).
This is extremely frustrating and we're all getting tired of waking up
at 3am to kick the job off. Any ideas?
Thanks!!This article may help.
http://support.microsoft.com/kb/269074/EN-US/
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi,
|
| We have recently begun to experience a problem that is stumping us. We
| have a DTS package that is run every morning as a scheduled job but
| always fails. The job runs perfectly fine when kicked off manually
| (either all at once from the Enterprise Manager or one step at a time
| from DTS). The job history says it failed on step 1, but we've
| determined that at least the first few steps run successfully. I've
| attempted to turn package logging on but we never get any results (I've
| tried to save the log both in the SQL Server log as well as a local
| text file ... nothing ever appears in either location).
|
| This is extremely frustrating and we're all getting tired of waking up
| at 3am to kick the job off. Any ideas?
|
| Thanks!!
||||Hi Dave,
Thanks for the article. I've read it and checked a few things out on
our SQL Server 2000 installation. The job was running under a different
account than I would have expected, but the account has full access to
the system and is a member of the system administrator group. Just to
be safe, I changed the owner of the job to the Administrator account
(which is what our other DTS packages run under). However, this didn't
work either.
I then deleted the scheduled job and recreated it on the server
desktop. The owner is again the Administrator account. It is scheduled
to run tonight, but I highly doubt anything is going to change.
Are there some security settings I could double-check perhaps?
Thanks,
-Rich
Dave Patrick wrote:
> This article may help.
> http://support.microsoft.com/kb/269074/EN-US/
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi,
> |
> | We have recently begun to experience a problem that is stumping us. We
> | have a DTS package that is run every morning as a scheduled job but
> | always fails. The job runs perfectly fine when kicked off manually
> | (either all at once from the Enterprise Manager or one step at a time
> | from DTS). The job history says it failed on step 1, but we've
> | determined that at least the first few steps run successfully. I've
> | attempted to turn package logging on but we never get any results (I've
> | tried to save the log both in the SQL Server log as well as a local
> | text file ... nothing ever appears in either location).
> |
> | This is extremely frustrating and we're all getting tired of waking up
> | at 3am to kick the job off. Any ideas?
> |
> | Thanks!!
> ||||Something here may help.
http://www.sqldts.com/?106
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi Dave,
|
| Thanks for the article. I've read it and checked a few things out on
| our SQL Server 2000 installation. The job was running under a different
| account than I would have expected, but the account has full access to
| the system and is a member of the system administrator group. Just to
| be safe, I changed the owner of the job to the Administrator account
| (which is what our other DTS packages run under). However, this didn't
| work either.
|
| I then deleted the scheduled job and recreated it on the server
| desktop. The owner is again the Administrator account. It is scheduled
| to run tonight, but I highly doubt anything is going to change.
|
| Are there some security settings I could double-check perhaps?
|
| Thanks,
| -Rich|||Thanks Dave,
I've looked at a few things suggested in the link you provided but
nothing worked. Finally, I created a new login for the windows
Administrator account and added it to the SysAdmin role. This worked!
Now, this stumps me, because the Windows workgroup "Administrators" is
already a member of SysAdmin, and the Windows Administrator account is
already assigned to the Administrators role... so it should have had
permissions already. Any thoughts on this?
Additionally, I mentioned that the job was not recording its log file.
This was mistaken - I was looking under the SQL Server logs (under
"Management"), but I just found them under DTS, "package logs".
However, there are no entries for when the package ran as a scheduled
job ... a log entry was recorded only when I ran the job manually. This
was really puzzling to me, but then I realized that it had to be a
security problem. The job wasn't getting anywhere, because the SQL
Agent was running under an account that didn't have the right
permissions applied (apparently).
-Rich
Dave Patrick wrote:
> Something here may help.
> http://www.sqldts.com/?106
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi Dave,
> |
> | Thanks for the article. I've read it and checked a few things out on
> | our SQL Server 2000 installation. The job was running under a different
> | account than I would have expected, but the account has full access to
> | the system and is a member of the system administrator group. Just to
> | be safe, I changed the owner of the job to the Administrator account
> | (which is what our other DTS packages run under). However, this didn't
> | work either.
> |
> | I then deleted the scheduled job and recreated it on the server
> | desktop. The owner is again the Administrator account. It is scheduled
> | to run tonight, but I highly doubt anything is going to change.
> |
> | Are there some security settings I could double-check perhaps?
> |
> | Thanks,
> | -Rich|||They may know more about this here.
http://www.microsoft.com/communitie...oft.com/protect
"Rich" wrote:
| Thanks Dave,
|
| I've looked at a few things suggested in the link you provided but
| nothing worked. Finally, I created a new login for the windows
| Administrator account and added it to the SysAdmin role. This worked!
| Now, this stumps me, because the Windows workgroup "Administrators" is
| already a member of SysAdmin, and the Windows Administrator account is
| already assigned to the Administrators role... so it should have had
| permissions already. Any thoughts on this?
|
| Additionally, I mentioned that the job was not recording its log file.
| This was mistaken - I was looking under the SQL Server logs (under
| "Management"), but I just found them under DTS, "package logs".
| However, there are no entries for when the package ran as a scheduled
| job ... a log entry was recorded only when I ran the job manually. This
| was really puzzling to me, but then I realized that it had to be a
| security problem. The job wasn't getting anywhere, because the SQL
| Agent was running under an account that didn't have the right
| permissions applied (apparently).
|
| -Rich

DTS package won't run as a scheduled job

Hi,
We have recently begun to experience a problem that is stumping us. We
have a DTS package that is run every morning as a scheduled job but
always fails. The job runs perfectly fine when kicked off manually
(either all at once from the Enterprise Manager or one step at a time
from DTS). The job history says it failed on step 1, but we've
determined that at least the first few steps run successfully. I've
attempted to turn package logging on but we never get any results (I've
tried to save the log both in the SQL Server log as well as a local
text file ... nothing ever appears in either location).
This is extremely frustrating and we're all getting tired of waking up
at 3am to kick the job off. Any ideas?
Thanks!!This article may help.
http://support.microsoft.com/kb/269074/EN-US/
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi,
|
| We have recently begun to experience a problem that is stumping us. We
| have a DTS package that is run every morning as a scheduled job but
| always fails. The job runs perfectly fine when kicked off manually
| (either all at once from the Enterprise Manager or one step at a time
| from DTS). The job history says it failed on step 1, but we've
| determined that at least the first few steps run successfully. I've
| attempted to turn package logging on but we never get any results (I've
| tried to save the log both in the SQL Server log as well as a local
| text file ... nothing ever appears in either location).
|
| This is extremely frustrating and we're all getting tired of waking up
| at 3am to kick the job off. Any ideas?
|
| Thanks!!
||||Hi Dave,
Thanks for the article. I've read it and checked a few things out on
our SQL Server 2000 installation. The job was running under a different
account than I would have expected, but the account has full access to
the system and is a member of the system administrator group. Just to
be safe, I changed the owner of the job to the Administrator account
(which is what our other DTS packages run under). However, this didn't
work either.
I then deleted the scheduled job and recreated it on the server
desktop. The owner is again the Administrator account. It is scheduled
to run tonight, but I highly doubt anything is going to change.
Are there some security settings I could double-check perhaps?
Thanks,
-Rich
Dave Patrick wrote:
> This article may help.
> http://support.microsoft.com/kb/269074/EN-US/
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi,
> |
> | We have recently begun to experience a problem that is stumping us. We
> | have a DTS package that is run every morning as a scheduled job but
> | always fails. The job runs perfectly fine when kicked off manually
> | (either all at once from the Enterprise Manager or one step at a time
> | from DTS). The job history says it failed on step 1, but we've
> | determined that at least the first few steps run successfully. I've
> | attempted to turn package logging on but we never get any results (I've
> | tried to save the log both in the SQL Server log as well as a local
> | text file ... nothing ever appears in either location).
> |
> | This is extremely frustrating and we're all getting tired of waking up
> | at 3am to kick the job off. Any ideas?
> |
> | Thanks!!
> ||||Something here may help.
http://www.sqldts.com/?106
--
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Hi Dave,
|
| Thanks for the article. I've read it and checked a few things out on
| our SQL Server 2000 installation. The job was running under a different
| account than I would have expected, but the account has full access to
| the system and is a member of the system administrator group. Just to
| be safe, I changed the owner of the job to the Administrator account
| (which is what our other DTS packages run under). However, this didn't
| work either.
|
| I then deleted the scheduled job and recreated it on the server
| desktop. The owner is again the Administrator account. It is scheduled
| to run tonight, but I highly doubt anything is going to change.
|
| Are there some security settings I could double-check perhaps?
|
| Thanks,
| -Rich|||Thanks Dave,
I've looked at a few things suggested in the link you provided but
nothing worked. Finally, I created a new login for the windows
Administrator account and added it to the SysAdmin role. This worked!
Now, this stumps me, because the Windows workgroup "Administrators" is
already a member of SysAdmin, and the Windows Administrator account is
already assigned to the Administrators role... so it should have had
permissions already. Any thoughts on this?
Additionally, I mentioned that the job was not recording its log file.
This was mistaken - I was looking under the SQL Server logs (under
"Management"), but I just found them under DTS, "package logs".
However, there are no entries for when the package ran as a scheduled
job ... a log entry was recorded only when I ran the job manually. This
was really puzzling to me, but then I realized that it had to be a
security problem. The job wasn't getting anywhere, because the SQL
Agent was running under an account that didn't have the right
permissions applied (apparently).
-Rich
Dave Patrick wrote:
> Something here may help.
> http://www.sqldts.com/?106
> --
> Regards,
> Dave Patrick ...Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]
> http://www.microsoft.com/protect
> "Rich" wrote:
> | Hi Dave,
> |
> | Thanks for the article. I've read it and checked a few things out on
> | our SQL Server 2000 installation. The job was running under a different
> | account than I would have expected, but the account has full access to
> | the system and is a member of the system administrator group. Just to
> | be safe, I changed the owner of the job to the Administrator account
> | (which is what our other DTS packages run under). However, this didn't
> | work either.
> |
> | I then deleted the scheduled job and recreated it on the server
> | desktop. The owner is again the Administrator account. It is scheduled
> | to run tonight, but I highly doubt anything is going to change.
> |
> | Are there some security settings I could double-check perhaps?
> |
> | Thanks,
> | -Rich|||They may know more about this here.
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.dts&cat=en_US_2b8e81a3-be64-42fa-bd81-c6d41de5a219&lang=en&cr=US
I just threw out that article 269074 and the link to the sqldts site since
they both helped me understand a problem I was having with trying to call a
DTS package from VB
Regards,
Dave Patrick ...Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
"Rich" wrote:
| Thanks Dave,
|
| I've looked at a few things suggested in the link you provided but
| nothing worked. Finally, I created a new login for the windows
| Administrator account and added it to the SysAdmin role. This worked!
| Now, this stumps me, because the Windows workgroup "Administrators" is
| already a member of SysAdmin, and the Windows Administrator account is
| already assigned to the Administrators role... so it should have had
| permissions already. Any thoughts on this?
|
| Additionally, I mentioned that the job was not recording its log file.
| This was mistaken - I was looking under the SQL Server logs (under
| "Management"), but I just found them under DTS, "package logs".
| However, there are no entries for when the package ran as a scheduled
| job ... a log entry was recorded only when I ran the job manually. This
| was really puzzling to me, but then I realized that it had to be a
| security problem. The job wasn't getting anywhere, because the SQL
| Agent was running under an account that didn't have the right
| permissions applied (apparently).
|
| -Rich

Dts package to run every 10 seconds

Does anyone know if you can shedule a dts package to run every 10 seconds ?
I can see that you can create a job but this only has the option for the earliest run set to 1 minute.
Any advice
IanYou would probably have to write a batch/script file that runs with some form of a loop that will sleep for 10 seconds, and then exec the packaged via DTSRun within that while loop.

Question though, why do you need to run this every 10 seconds ?

Here is a simple batch file that could do it, but you would really need to add some error checking, being it can run endlessly.

run.cmd file
@.ECHO OFF
:begin
sleep 10
echo DTSRun command goes here
GOTO END

:END
goto BEGIN|||Nice batch file.

You could also do this by having your job (scheduled once per minute) call a stored procedure that executes the DTS package six times in 10 second intervals. Plus, I wouldn't be surprised if there was a way you could rig this up in the DTS package yourself, either using coding or by simply repeating the process six times in a series of linked steps.|||Nice batch file.

You could also do this by having your job (scheduled once per minute) call a stored procedure that executes the DTS package six times in 10 second intervals. Plus, I wouldn't be surprised if there was a way you could rig this up in the DTS package yourself, either using coding or by simply repeating the process six times in a series of linked steps.

This way seems way safer instead of using the .cmd file.|||Another alternative is that you could run your
DTS package as step 1,3,5,7,9, and 11 in the scheduled job,
with step 2,4,6,8, and 10 being a T-SQL command:

WAITFOR DELAY '00:00:10'

See BoL ...

WAITFOR
{
DELAY 'time_to_pass'
| TIME 'time_to_execute'
| ( receive_statement ) [ , TIMEOUT timeout ]
}sqlsql

DTS Package scheduled to run as Job Fails

Hi,

I have a DTS package which when I execute it manually, it executes perfectly. I need to schedule it and therefore had to use the scheduler which would creata a job for the package. The problem is that job. It does not execute; it fails every time. The error message is: Sql server does not exist or access is denied.

I have tried to trouble-shoot by setting up an alias for the server in the client network utility for sql server. I also create a new dsn with the new alias. It still failed with the same error message. I edited the properties of the dts package to connect to the server's ip instead of it's alias or logical name. Still fails.

Any ideas anyone?

MariaIs sa the owner of the job ? If not make sa the owner