This article had already been posted by me in ASP Alliance in May 2009. Since the images are not loading anymore in the article, that is of no use for anyone. So, I am posting the same again in my blog.
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.
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:
[FTP]
Site=ftp://myhost/shared
UserName=admin
Password=sD8ek46B
DirectoryPath=\\fileshare\shared\input
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.
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.
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
"glbvarDBPassword":"8"="welcome01*"
Using DTSRun or DTSRunUI utility, the command can be
encrypted for security reasons. The
encrypted command for the above, will appear as below:
DTSRun
/~Z0x2D35A86F276F249E84839CBBFB3F964CB97886C12A0B29069E392586DE48995E77DF17953697B2381635BBE582FDFF85D6A1D577F5B287478FFE28B0724A28CB64D271A37B10B06A7CA59BAF9DAED4FC6ECC0D2B1E1D83311B39628C467320C13CEBD60C92B1745C0D1108BD67F7A93EB473282B91057AFE9BB228CFB3597F2D1D5AD85F4C408B601841560A5BC0F4161A4F9053BB661A136996151C855043BE8803C199D3F003D3B8C1B97D86013C385121036F86CBA501F9652B95CD9256F1E4FC5097DC0D2EFEEB616374B12D41781A21258B74DFB94828586FE032AE49C42846A3F287178EF414EB9D8E95EB731BFF11E82159FEF0C4C5FFB226856A3728733BF29D94B9A823576FD848AEAE01CA4690A1FE4AF7D77694DAFCE25E181DB645170DC526557851790E77369240266E07661598FB17E9D328AD98CE2021A2B628EE0DB0DD89E1C4CB7E64AF7AB6D3612ACE3D0B27660F09DD3AB19A2B7DFAB052E1
DA39D88E12975DBED439D3F468845AF0C1D6C032FB7E2AC6EF
The global variables also provide an advantage of ability of
being utilized in ActiveX scripts. They
can read as below:
FtpSiteUrl =
DTSGlobalVariables("FTPSite").Value
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.
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 = "10.142.117.202\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: " & file.name
objDTSPackage.LoadFromStorageFile DTSPath & "\" & file.name, ""
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
Next
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.
No comments:
Post a Comment