Programmatically Save DTS Packages to Files

By Bill Graziano on 24 May 2004 | 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.