SavePkgToFile  

Home
Up
What's New
SQL-DMO
SQL-NS
XP's
SQL Agent
Misc. Tools
Events
FAQ
Links
About...

 

SavePkgToFile

version: 1.0.0.0

last updated: 28 November 2002

DTS Packages can be stored in three different locations, file, SQL Server (msdb) and in the Repository (a.k.a. Meta Data Services). The first two formats: file and SQL Server share the same physical format.

The package is persisted as a COM structured storage file. In case of a file it is really a file on disk that can be opened with the COM structured storage file interfaces directly. In case the package is stored inside SQL Server (msdb) the file is serialized into the packagedata column of the sysdtspackages table in the msdb database.

The layout of the msdb.dbo.sysdtspackages table:

CREATE TABLE [dbo].[sysdtspackages]
(
    [name]        sysname NOT NULL,
    [id]          uniqueidentifier NOT NULL,
    [versionid]   uniqueidentifier] NOT NULL,
    [description] nvarchar(1024) NULL,
    [categoryid]  uniqueidentifier NOT NULL,
    [createdate]  datetime NULL,
    [owner]       sysname NOT NULL,
    [packagedata] image NULL,
    [owner_sid]   varbinary(85) NOT NULL,
    [packagetype] int NOT NULL
)

The main difference between storing the package in a file or in SQL Server is that versioning is handled different. When using a file to store your package, every time you save the package a new version stream is created inside the package, when you store the package inside SQL Server, a new row in the sysdtspackages table is created to represent the new version of the package. All the columns remain the same information except the versionid, createdate and packagedata columns.

If you want to deploy package that are stored inside sysdtspackages or archive them for backup purposes, you  could use the DTS Designer Save As functionality, which works well if you need to save one package from the database to file. If you want to do this from the command line it becomes more tricky. In SQL Server 2000 the Application object is added to the DTS Package object model, however what to do in SQL Server 7.0

The "easiest" way to save a package from the database to file is to de-serialize the content of the packagedata image column in the sysdtspackages table to file. Since the package is stored inside an image column the TEXTCOPY.EXE utility can be used to save the package to file.

@rem SavePkgToFile.cmd
@echo off
if "%DEBUG%"=="1" @echo on

setlocal

SET SVR=%1
SET UID=%2
SET PWD=%3
SET DB=msdb
SET TABLE=dbo.sysdtspackages
SET COLUMN=packagedata
SET PACKAGENAME=%4
SET FILENAME=%5

textcopy /S %SVR% /U %UID% /P %PWD% /D %DB% /T %TABLE% /C %COLUMN% /W "where name = '%PACKAGENAME%' and createdate = (select max(createdate) from sysdtspackages where name = '%PACKAGENAME%')" /O /F %FILENAME%

endlocal

Download SavePkgToFile.zip simple command line script using TEXTCOPY.EXE

NOTE: The problem with TEXTCOPY.EXE is that since it is build on top of DB-Library, it does not support connecting to a SQL Server 2000 named instance without creating an alias to connect to.

***

Questions or problems regarding this web site should be directed to webmaster@sqldev.net.
Copyright © 1991-2003 SQLDev.Net. All rights reserved.
Last modified: 07/06/03.