How to Call a DTS package from T-SQL 1

[DougP]

I have a DTS pacakge that copies data from an Access database to SQL Server table

I want to automate this
Can I call the DTS or can I convert it to T-SQL then save it as a Storedprocedure and call it with EXEC sp_ ???

TIA DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[RickCole]

You can't convert your DTS packaage to TSQL or an SP but you can quite easily schedule it to run.

Right click on the package and go to Schedule package.

Is that what you were after?

Rick.

 

[tlbroadbent]

Use xp_cmdshell (if available to you) to execute the DTSRUN.EXE utility. The DTSRUN.EXE Utility is documented in BOL.

For a more comprehensive solution read articles at the following links.

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14230

http://support.microsoft.com/support/kb/articles/Q240/2/21.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DougP]

OK here is what I've got in query analyser:
xp_cmdshell "dtsrun CopyPartsAccess.dts"

I'm getting this error in the results pane.

output -------------------------------------------------
DTSRun: Must specify a package source server or filename.

DTSRun: Invalid command options

DTSRun Options ('/?' shows this screen; '-' May be substituted for '/'):

Package retrieval and contents:

/~S ServerName /~U UserName /~P Password /E <Use trusted connection instead of /U /P>

/~N PackageName /~M PackagePassword /~G PackageGuidString /~V PackageVersionGuidString

etc
etc
-------------
Any ideas

TIA

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[tlbroadbent]

According to SQL BOL you need at least two parameters.

dtsrun /Ffilename /Npackage_name

I don't use DTSRUN except in Jobs that start DTS packages. I also save my DTS packages to SQL Server rather than a file so I've no experience with the utility. Sorry I can't be of more help.

I just read another solution which I consider to be superior to using xp_cmdshell.

1) Schedule that DTS package which creates a Job.
2) Disable the Job or remove the schedule.
3) Execute sp_Start_Job to start the job from your stored procedure.

Simplicity! And doesn't pose the security risk of xp_cmdshell. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[DougP]

Great!! I did that and it worked just fine
how 'bout a star DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive