Creating DTS package will create a job in Sqlserver Agent?

Question

Creating DTS package will create a job in Sqlserver Agent?

AAkula

SSCertifiable

Points: 5394
More actions
February 24, 2009 at 12:45 pm

#155323

Hi,
I have DTS package in sql 2000 and moved to sql 2005.In sql 2000, I have a job which runs the dts package. So de we need to create this job or it will be created automatically when the dts package is created in sql 2005?
Job Step:
dtsrun /S ABC /E /N agent_import /A gstrServer:8=ABC/A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"
Thanks

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

suzanne-892457 SSCommitted Points: 1744 More actions · Answer 1

Jobs are not created automatically in SQL2000 or SQL2005 when creating a DTS Package. You will have to create a schedule in SQL2005 once the DTS Package is created.

AAkula SSCertifiable Points: 5394 More actions · Answer 2

Here Iam not creating a new DTS package in sql 2005. Iam Moving the DTS package from sql 2000 to 2005. In this case, I think the job will not be created automatically and we need to create manually by taking script from sql 2000 and run it in sql 2005.

Please clarufy me this...

Mike - CI SSCarpal Tunnel Points: 4146 More actions · Answer 3

Moving a package from a SQL 2000 over to a SQL 2005 server will definitely not create a job for you to run that package. That is something you will have to do on your own.

homebrew01 SSC Guru Points: 55548 More actions · Answer 4

You can script the job in 2000, then paste & run the script in 2005.

AAkula SSCertifiable Points: 5394 More actions · Answer 5

Hi,

I have moved the DTS package from sql 2000 to 2005 successfully.Now I also created the DTS job in sql 2005 as it is in sql 2000.

1.In sql 2000:Job Step(Default instance)

dtsrun /S ABC/E /N agent_import /A gstrServer:8=ABC/A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"

2.In sql 2005:Job Step:(Named instance INS1)

dtsrun /S XYZ\INS1 /E /N agent_import /A gstrServer:8=XYZ\INS1/A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"

But when I executed the above job sql 2005, Iam getting the below error

The job failed. The Job was invoked by User ABC\admin. The last step to run was step 1 (Agent_Import).Unable to open Step output file. The step failed.

Please advice me

AAkula SSCertifiable Points: 5394 More actions · Answer 6

could you please explain me what this command mean

dtsrun /S ABC/E /N agent_import /A gstrServer:8=ABC/A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"

I do not much knowledge about dts packages, I went through BOL...

/S->servername

/N->packagename

/A->global variable name

''d:\''-> ? how to know what we are getting from d:\ and what is number 8 mean in gstrServer:8=ABC/A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"

thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 7

Did you install the SQL Server 2005 Legacy Components for DTS?

Your D:\ is an argument being passed in which is sending a value in for a global variable. Ensure your DTS package has logging enabled and view the output. Either send it to the file system or log locally to the server and query msdb.dbo.sysdtspackages + sysdtspackagelog to take the next steps to find out where your error resides.

AAkula SSCertifiable Points: 5394 More actions · Answer 8

Hi,

1.I have moved the dts package from sql 2000 to sql 2005 using Method1A from http://blogs.msdn.com/psssql/archive/2009/02/19/how-to-copy-dts-2000-packages-between-servers-and-from-sql-2000-to-sql-2005-and-sql-2008.aspx

2.Edited the source and destion connection strings in dynamic properties task and source and destination.

3.Take the job script(which runs dts package) from sql 2000 and run it in sql 2005.

The sqlagent job in sql 2000 configured as to runs Operationg system and the job step as below

dtsrun /S ABC /E /N agent_import /A gstrServer:8=ABC /A gstrClient:8=CF /A gstrEnvironment:8=QA /A gstrPath:8="d:\"

4.In 2005, the job has been created by running the job script which is taken from sql 2000.

and configured as below

dtsrun /S XYZ\INS1 /E /N agent_import /A gstrServer:8=XYZ\INS1 /A gstrClient:8=CF /A

gstrEnvironment:8=QA /A gstrPath:8="d:\"

5.Running above job in sql 2005 resulted in error as Executed as user: ABC\Admin. Unable to open Step output file. The step failed.

My question is can we use DTSRUN in sql 2005 when configuring the job and choose run as Operating system command?

thanks

Adam Bean One Orange Chip Points: 26631 More actions · Answer 9

Yes you can.

I've never used the import/export thing ... just insert into msdb.dbo.sysdtspackages from your source into your destination. Use opendatasource or a linked server. This is by far the quickest way to move over your DTS packages.

Also, as I said, enable logging and run it again to see if you can get a better error.