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)

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

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.

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) (3 June 2001)

Other Recent Forum Posts

Object cannot be cast from DBNULL to other types coming randomly in SSIS Package-Migrated from VS 2008 to 2015,SQL 2008R2 to SQL2016 on 1st run only (3h)

Error in sp procedure- Msg 50000, Level 16, State 1, Procedure spCheckDBInfo, Line 193 [Batch Start Line 0 (6h)

Two records into a single record? (7h)

Convert integer 0101 into 1010 and 11 to 00 in SQL server (10h)

Basic SQL Join Error – HELP! (19h)

Dynamic SQL Problem (1d)

T-SQL -- store common definitions (1d)

SQL - Finding duplicate rows of data (1d)

- Advertisement -