Deploying DTS package in SQL Server 2000
Published: 04 May 2009
Unedited - Community Contributed
One of the challenging activities in working with DTS packages in SQL server 2000 is deployment. Hard coding in DTS objects such as connections would appear inevitable and would require changes every time you deploy depending on the environment. Though Enterprise Manager provides extremely good UI to deploy, it is tedious to deploy using it when there are several packages. This article explains the process of removing hard coding with the help of parametrized global variables and also reading configurable values for external resources such as database table or INI file during runtime and a code snip for automated deployment.
by Cyril Beschi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 21129/ 43

Challenges in deployment of DTS packages

When developing DTS packages, the DTS connection objects and task objects are set with server names (could be DB, FTP, SMTP, etc), credentials, source/target tables, and source/target file path and names in their properties.  This imposes the challenge of changing such hard coded values at the time of deployment of the DTS packages in different environments with environment-specific server names, credentials, etc.  With deployment being an activity of server management group, deployment of such DTS packages becomes impossible without help from development group, though the, server management group does not want to share sensitive information such as server credentials to any other group.  The second challenge of deployment is the manual intervention required in deployment.  Although Enterprise Manager provides an uncomplicated user interface to deploy DTS, it is manual; so it is tedious and error-prone.  This article explains how such hard coded values can be externalized so that changing such values becomes an easy job, and how the deployment of DTS packages can be automated.

Setting values dynamically

Dynamic Properties Task

"Dynamic Properties Task" is one of the DTS task objects.  This task object helps to set properties dynamically at runtime for data sources and data pumps.  To add "Dynamic Properties Task" to the DTS package, just double click on the "Dynamic Properties Task" located in the DTS designer toolbar or drag and drop the task into the designer window.



When added to DTS designer window, the task opens the Properties window where you will have to select the properties of the DTS objects to be made dynamic.  To set properties of FTP task such as FTP site, username, and password, we will have to add those properties by clicking Add button of the Dynamic Properties Task properties window.



When clicking Add button, all the DTS objects used in the DTS packages are displayed on the left pane of the Package Properties window and properties of the selected object are displayed on the right pane of the same.


The values can be externalized to an INI file, a table in a database, global variable, or environment variable.


Reading from INI file

An INI file contains keys grouped under sections.  Each section is enclosed in a square bracket and keys appear grouped below the section.  To set FTP task properties, we will have to create INI file as below:




To set a property from an INI file, select the property on the right pane of the "Package Properties" window and click Set button to open Add/Edit Assignment window.  Select the Source as INI file.  Enter the path of the INI file by browsing and select Section and Key from the dropdown values.



Whenever, you want to change any FTP properties based on environment, you can do so by modifying them in the INI file leaving the DTS unchanged.  Make sure to have the dynamic properties task executed before FTP task by altering the execution flow.


Remember however that the path of the INI file still is hard coded.


Reading from database table

In order to read from a database table, the DTS package must have a database connection object in it.


To set a property from a database table, select the Source as Query in the Add/Edit Assignment window.  Select a database connection object from the Connection dropdown and enter a select query in the Query text area.



We have not yet fully solved the hard coding problem yet.  Now, the FTP task properties are made dynamic by setting them from a database table, but the connection itself will have hard coded values of database name, database name and credentials.  To set values dynamically using database query, the connection object must already exist with correct data source values and credentials; so, you cannot set properties of connection objects using database query.  However, you can set connection object properties using global variables.


Reading from Global Variables

Reading from Global Variables is straightforward.  Just select source as Global Variable and select the desired global variable from the Variable dropdown.  Global variables can be created and set with default value by clicking "Create Global Variables" button.



Typically, DTS packages are executed by calling them from a SQL Job, from command window, or from a process.  When executing DTS packages, values for the global variables can be passed as arguments thus changing the default value of the global variables:


DTSRun /S "DBServerName" /N "PackageName" /E /A "FTPSite":"8"="ftp://otherhost/shared" 


To set values for connection object, the properties of connection object set through global variables and they can be overridden as below for different environment.


DTSRun /S "DBServerName" /N "PackageName" /E /A "glbvarDBName":"8"="TestDB" /A 
"glbvarDBServer":"8"="DBServerName" /A "glbvarDBUser":"8"="sa" /A 


Using DTSRun or DTSRunUI utility, the command can be encrypted for security reasons.  The encrypted command for the above, will appear as below:


DTSRun /~Z0x2D35A86F276F249E84839CBBFB3F964CB97886C12A0B29069E392586DE48995E77DF 


The global variables also provide an advantage of ability of being utilized in ActiveX scripts.  They can read as below:


FtpSiteUrl = DTSGlobalVariables("FTPSite").Value


Reading from Environment Variables


The environment variable is set from System Properties.  Setting a system variable makes the variable available for all the users including the account in which SQL Agent runs.



Reading from environment variable is as easy as selecting source as "Environment Variable" and selecting the environment variable from the variable dropdown.




The disadvantage however in reading from environment variable is that adding a new environment variable or modifying an existing environment variable may require server restart.  When database is clustered, the variables have to be set in all the clustered servers.


Automated Deployment


Using VBScript


The DTS.Package object of VBScript exposes two methods - LoadFromStorageFile and SaveToSQLServer - which help to deploy DTS packages from physical files to DB server.  The following script serves the purpose.  The script loops through all the files with DTS extension in the DTS subdirectory where the script is located, and install the DTS packages one by one.


Option Explicit
Dim DBServerName, DBUserName, DBPassword
DBServerName = "\ARD54, 1297"
DBUserName = "sa"
DBPassword = "welcome01*"
Dim objFSO, objFile, file, objDTSPackage, DTSPath
DTSPath = ".\DTS"
Set objFSO = CreateObject("Scripting.FileSystemObject")
For Each file In objFSO.GetFolder(DTSPath).Files
      If LCase(objFSO.GetExtensionName(file)) = "dts" Then
            Set objDTSPackage = CreateObject("DTS.Package")
            WScript.Echo "Creating DTS: " &
            objDTSPackage.LoadFromStorageFile DTSPath & "\" &, ""
            WScript.Echo Chr(9) & "DTS loaded successfully"
            objDTSPackage.SaveToSQLServer DBServerName, DBUserName, DBPassword
            WScript.Echo Chr(9) & "DTS created in the server successfully"
            Set objDTSPackage = Nothing
      End If
WScript.Echo "END"


When there are multiple versions in the physical DTS file, then GUID of the version to be deployed must be mentioned.  When there is only one version in the physical file, then the available version will be deployed.

User Comments

No comments posted yet.

Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2022  |  Page Processed at 2022-09-30 8:56:09 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search