How to Import and Export DTS packages quickly

CrucisCo
16 Jun 2011
0

This article applies to DTS packages on SQL Server 2008 R2, but will probably work on most other versions. Yes I know, I know - DTS is deprecated. But bear with me. There is a lot of DTS still hanging around out there.

How to export DTS packages to binary file

The slow way

Open SQL Server Management Studio (SSMS), open the Object Explorer and navigate down the server tree, Management, Legacy, Data Transformation Services. Right click on a package and select Export.... If there are multiple versions select the version required and click OK. In the file dialog, enter a file name and click Save. I must emphasize that if a particular version is required, this may be simplest way to get the binary. Another advantage is that this export will create a binary that retains the layout and annotations of the package.

The fast way

The problem with this technique is that it completely loses the layout of the package and the annotations. Not such a big deal with simple packages, but if you have a large package with scores of steps and complex workflows it could be a problem. The first thing is to create a script that you can execute. For this you use a SQL script to create a batch script, based on the DTSRUN utility.


--first some variables - change as you see fit
DECLARE @servername varchar(200)
SET @servername = CONVERT(varchar(200), SERVERPROPERTY('servername'))
DECLARE @targetfolder varchar(200)
SET @targetfolder = 'DTSPackages'

--now some general batch-y stuff
SELECT '@ECHO OFF'
SELECT 'MD ' + REPLACE(@servername, '\', '-') + '\' + @targetfolder

SELECT 
	'DTSRUN.EXE /S '
	+ @servername
	--+ ' /U username /P password'
	+ ' /E '
	+ ' /V ' + CONVERT(varchar(200), PKG.versionid)
	+ ' /F ' + '"' + REPLACE(@servername, '\', '-') + '\' + @targetfolder + '\' + Replace(PKG.name,'\','') + '.dts"'
	+ ' /!X'
	+ CHAR(13) + CHAR(10)
	+ 'ECHO Saved ' + PKG.name + CHAR(13) + CHAR(10)
	+ 'ECHO.'
FROM msdb.dbo.sysdtspackages PKG
JOIN
(
	select name, MAX(createdate) as CreateDate from msdb.dbo.sysdtspackages
	group by name
) IJ
ON
IJ.name = PKG.name 
AND 
IJ.CreateDate = PKG.createdate

Ensure that you set the output of the Query window to be 'text' so you can easily copy the output into a text editor. Use this script to get the to produce a batch file like the following:


@ECHO OFF
MD MYSQLSERVER\DTSPackages

DTSRUN.EXE /S MYSQLSERVER /E  /V 3FA50B67-DB4C-4793-8053-2068EC8A6FE9 /F "MYSQLSERVER\DTSPackages\TheFirstExport.dts" /!X
ECHO Saved TheFirstExport
ECHO.
DTSRUN.EXE /S MYSQLSERVER /E  /V 16A0B43B-ABEF-49BE-B18D-93300AA0EF9A /F "MYSQLSERVER\DTSPackages\The First Import.dts" /!X
ECHO Saved The First Import
ECHO.
DTSRUN.EXE /S MYSQLSERVER /E  /V 9AB1B4FC-D468-4A62-8008-A81BDB402A50 /F "MYSQLSERVER\DTSPackages\The Import and Export of more data.dts" /!X
ECHO The Import and Export of more data
ECHO.

You can save this text into a .cmd or .bat file which can be executed at a command prompt. Remember, this process will remove layout and annotations from your packages, so don't use this method on complex packages if you can help it.

How to import DTS packages

Um... I'm sorry there doesn't appear to be a quick way or at least not a quick an easy way. The first challenge nonetheless is to get rid of the ones already hogging your database server (if you're aiming to replace the existing ones). When you use the GUI, you'll quickly find that every time you do your right-click -> Delete, the whole navigation tree in your SQL Server Management Studio will suddenly collapse. No biggie if you're just deleting a couple - bloody irritating if you're trying to delete several dozen. Wouldn't it be nice if it could be scripted?


USE msdb
GO

DECLARE @PackagesToRemove TABLE (Package VARCHAR(100))
-- Place insert statements here to build up a list of the packages you want removed
INSERT INTO @PackagesToRemove (Package) VALUES ('The Nth Data Export')

DECLARE @packageUID UNIQUEIDENTIFIER
DECLARE packages CURSOR FOR
SELECT DISTINCT id
FROM sysdtspackages AS SP
INNER JOIN @PackagesToRemove AS PTR 
ON (SP.Name = PTR.Package)
OPEN packages
FETCH NEXT FROM packages INTO @packageUID
WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC sp_drop_dtspackage NULL, @packageUID, null
 
	FETCH NEXT FROM packages INTO @packageUID
END
CLOSE packages
DEALLOCATE packages

After the packages are dropped the way is clear to import from the binary files. To do this you have to use the GUI unfortunately. And a similar issue confounds this process: every time you import a package from a file, the entire server tree in SSMS will collapse. If anyone has a simple way to script the import of DTS binary files into SQL server, please get in touch.