May 1, 2009 at 7:53 am
I migrated sql server 2000 to sql server 2008. There are many DTS package is scheduled on SQL Server 2000 as jobs under sql server agent.
then I move all those databases and DTS Packages to new instance of sql server 2008. Then I try to schedule all DTS package as jobs that would be running at night as routine.
In SQL Server 2000 enterprise manager, I can click on any DTS Package --> Schedule Package... to create a job under sql server agent. but there is no such Menu in Microsoft SQL Server Management Studio for sql server 2008 any more.
How to create job to schedule those legacy DTS package in SQL server 2008?
May 1, 2009 at 9:50 am
In SQL 2005 and 2008 Management Studio, you have to create the jobs in SQL Server Agent and add the DTSRUN statements to job steps.
If your SQL 2000 servers are still available, you can script the jobs and run the scripts in a Management Studio query window to create the jobs in the 2008 instance. That will save you the trouble of recreating all the jobs from scratch.
Greg
May 1, 2009 at 10:26 am
Great! Thank you very much.
With your first suggestion:
In SQL 2005 and 2008 Management Studio, you have to create the jobs in SQL Server Agent and add the DTSRUN statements to job steps.
When add DTSRUN command in steps like:
DTSRun /~Z0xD3CB5FC1FC62DED.....
If I want to set the step manually, there are following items need to be set:
Step Name:My Package Name
Type: Operating System Command(CmdExec)
Process exis code of a successful command:0
Command: DTSRun /~Z0xD3CB5FC1FC.....
Question is: how do I know the hex for the specific package used for command?
When schedule package as a job in EM of sql server 2000, it will create this hex automatically.
How can I get those Hex for the specific DTS Package in command setting box in management studio? For example, if my DTS package name is "Moving Data At 1:00AM"?
May 4, 2009 at 8:47 am
Greg Charles (5/1/2009)
In SQL 2005 and 2008 Management Studio, you have to create the jobs in SQL Server Agent and add the DTSRUN statements to job steps.If your SQL 2000 servers are still available, you can script the jobs and run the scripts in a Management Studio query window to create the jobs in the 2008 instance. That will save you the trouble of recreating all the jobs from scratch.
Hi Greg,
When I move jobs from sql server 2000 to 2008 using the script, the job is created in indeed. But when I run the job, I got following error:
Message
Executed as user: MyDomain\sqlservice. The process could not be created for step 1 of job 0xFCCA3061B22F8A49BCA76D2FF7C1BB6F (reason: The system cannot find the file specified). The step failed.
Help please.
May 4, 2009 at 10:52 am
Kent Zhou (5/1/2009)
Great! Thank you very much.With your first suggestion:
In SQL 2005 and 2008 Management Studio, you have to create the jobs in SQL Server Agent and add the DTSRUN statements to job steps.
When add DTSRUN command in steps like:
DTSRun /~Z0xD3CB5FC1FC62DED.....
If I want to set the step manually, there are following items need to be set:
Step Name:My Package Name
Type: Operating System Command(CmdExec)
Process exis code of a successful command:0
Command: DTSRun /~Z0xD3CB5FC1FC.....
Question is: how do I know the hex for the specific package used for command?
When schedule package as a job in EM of sql server 2000, it will create this hex automatically.
How can I get those Hex for the specific DTS Package in command setting box in management studio? For example, if my DTS package name is "Moving Data At 1:00AM"?
You can generate the encrypted DTSRun command by running DTSRunui at a command prompt on the server where SQL Server is installed. I believe it's installed when you install the DTS Runtime components. To run it, open a command prompt and type >dtsrunui. When it opens, input "SQL Server" as the location, the name of the SQL instance, and the name of the package. Click on the "Advanced..." button, check the "Encrypt the command" box and click the "Generate..." button. Copy the generated string into the job step. This will solve the problem in your last post.
Greg
May 4, 2009 at 12:03 pm
Hi Greg, Thank you very much.
I following your advice and got the command like
DTSRun /~Z0xB9A94C2AA29A29C9B....
Then I run it in MS-DOS command line on server, it works.
But when I put it to the command box for the step for the job, and then run the job, it failed. the error message is:
Message
Executed as user: myDomain\sqlservice. The process could not be created for step 1 of job 0x811094B3EEFE7F4C8BB0F0584FFD3A19 (reason: The system cannot find the file specified). The step failed.
very confused. Help please.
May 4, 2009 at 3:12 pm
Maybe all the DTS runtime components aren't present. See if this article helps: http://support.microsoft.com/kb/919131.
Greg
May 5, 2009 at 9:27 am
Thank you, Greg.
Finally find out solution here: http://support.microsoft.com/kb/322746
May 5, 2009 at 10:07 am
Glad you figured it out.
Greg
May 17, 2010 at 3:16 pm
I'm having a similar problem but I'm receving the following error. I'm doing to the same as the original poster moving from SQL 2000 to 2008. I have other jobs and dts packages on this server that work fine and this error is throwing me off.
Executed as user: MARKETING\SQL03$. 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: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 0 (0) Error string: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified Error source: Microsoft OLE DB Provider for ODBC Drivers Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.
Thank you!
June 17, 2010 at 10:43 am
Hi Kent,
I have the same issue after moving the DTS package from SQL Server 2005 to SQL Server 2008. Could you please advice me what you did to resolve the issue?
and I went through the link you given. But it's only applied to SQL Server 7.0 & 2000. But we are facing this issue in SQL Server 2008. So how did you solve this issue?
http://support.microsoft.com/kb/322746
Thanks
June 17, 2010 at 11:36 am
That is a story long time ago for me. So what's your problem when you create job for legacy DTS package?
July 13, 2010 at 1:06 am
I'm getting the below error when I run the SQLAgent job:
Message
Executed as user: ABC\mssqlsrv. The process could not be created for step 1 of job 0x5003F9CB7CA4CB469EFBE981D011B942 (reason: The system cannot find the file specified). The step failed.
I have installed a SQL Server 2008 64 bit Standard Edition with SP1 and moved all the jobs and DTS package.
The SQLAgent job step is as below:
dtsrun /S SQL1 /E /N import /A gstrServer:8=SQL1 /A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"
thanks
August 10, 2010 at 2:35 pm
Using the encrypted call is not necessary. By default SQL 2000 will encrypt the call when you use it to schedule. The encrypted call on the new server won't work, so simply use a unencrypted call:
DTSRun /S "ServerName" /N "SomeDTSPackage" /E
I assume you already have a work-around, so this is more the next guy googling a solution.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply