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.


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

As I gain experience and get older, I'm working much slower, but producing better quality, but (2h)

Master DB 2019 problem (17h)

Please help, I can't login remote to MS SQL 2019 without sysadmin role (1d)

SSMS Cannot Connect to Newly Installed Instance (2017) (1d)

SQL server 2019 alwayson problem (2d)

Finding Possible Duplicates (4d)

SQL Agent Service will not start - timeout error (5d)

Adding a SQL connection to Microsoft Visual Studio (5d)

- Advertisement -