Programmatically Save DTS Packages to Files

By Bill Graziano on 24 May 2004 | 10 Comments | Tags: DTS


One of the things that has always bugged me about DTS is how difficult it was to transfer the DTS packages from SQL Server to structured storage (aka a file on the disk). In Yukon, DTS packages are always stored as files but in SQL Server 2000 they are stored in SQL Server by default. It's possible to use the DTSRUN command to automatically save a DTS package in a file. So wrote a little script to generate the statements to save every DTS package as a file.

DTSRUN.EXE is the command line utility that lets you execute DTS packages. I use it regularly to scehdule DTS packages or run them from batch files. The basic syntax is something like:
DTSRUN /S ServerName /N PackageName /E

You provide it the server name and package name and use the /E to have it use a trusted connection and it will run the package. You can also use DTSRUN to run packages stored in a structured storage file:

DTSRUN /F FileName /N PackageName /E

A strucuted storage file can have many DTS packages stored in it thus you need to provide the package name also. The other switch we're interested in is the /!X switch which tells DTSRUN not to execute the package. And as it turns out, if you provide both the structured storage file name and the SQL Server name, DTSRUN will save a copy of the DTS package in the structured storage file. That syntax looks something like this:

DTSRUN.EXE /S ServerName /E /N PackageName /F FileName /!X

Keep in mind that every time you run this statement it will append your package to the structured storage file so its probably a good idea to reset the file each time. Knowing this I constructured a little script to extract all the packages to a structured storage file. It looks something like this:

DECLARE @TARGETDIR varchar(1000)
SET 	@TARGETDIR = 'C:\DTSTest\'

SELECT	distinct  
	'DTSRUN.EXE /S '
	+ CONVERT(varchar(200), SERVERPROPERTY('servername')) 
	+ ' /E ' 
	+ ' /N '
	+ '"' + name  + '"'
	+ ' /F '
	+ '"' + @TARGETDIR + name + '.dts"'
	+ ' /!X'
FROM	msdb.dbo.sysdtspackages P

CommandLine
--------------------------------------------------------------------------------------
DTSRUN.EXE /S L30 /E /N "Import Snitz #2" /F "C:\DTSTest\Import Snitz #2.dts" /!X
DTSRUN.EXE /S L30 /E /N "Import Snitz Data" /F "C:\DTSTest\Import Snitz Data.dts" /!X
. . . .
DTSRUN.EXE /S L30 /E /N "Script" /F "C:\DTSTest\Script.dts" /!X

This script creates the DTSRUN statements. I just copy these into a batch file and then run the batch file. You could easily create a cursor around this and run each statement through xp_cmdshell.

Hopefully this little script will help you get more value out of your DTS packages.

Discuss this article: 10 Comments so far. Print this Article.

If you like this article you can sign up for our weekly newsletter. There's an opt-out link at the bottom of each newsletter so it's easy to unsubscribe at any time.

Email Address:

Related Articles

How to Asynchronously Execute a DTS package from ASP or ASP.NET (27 March 2005)

A Practical Guide to SQL Server Yukon Beta 1 DTS (4 February 2004)

DTS and C# (12 September 2003)

SQL Server DTS Best Practices (27 May 2003)

Using DTS and FTP to Push Files (13 January 2003)

Using DTS to Automate a Data Import Process (11 August 2002)

How to overwrite DTS package logs everytime the package executes? (17 July 2002)

SQLDTS.com (3 June 2001)

Other Recent Forum Posts

trx date time = system date time (1 Reply)

7 day sales analysis query (3 Replies)

sql every hour sales total by different shop (3 Replies)

Sum where date is between two dates (3 Replies)

Query into text file: bcp, dynamic sql & tables (0 Replies)

Convert DateTime to Int (1 Reply)

adding a date seems to remove any null values (1 Reply)

Truncate trans logs (3 Replies)

Subscribe to SQLTeam.com

Weekly SQL Server newsletter with articles, forum posts, and blog posts via email. Subscribers receive our white paper with performance tips for developers.

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -