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:
[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.
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
"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 /~Z0x2D35A86F276F249E84839CBBFB3F964CB97886C12A0B29069E392586DE48995E77DF
17953697B2381635BBE582FDFF85D6A1D577F5B287478FFE28B0724A28CB64D271A37B10B06A7CA5
9BAF9DAED4FC6ECC0D2B1E1D83311B39628C467320C13CEBD60C92B1745C0D1108BD67F7A93EB473
282B91057AFE9BB228CFB3597F2D1D5AD85F4C408B601841560A5BC0F4161A4F9053BB661A136996
151C855043BE8803C199D3F003D3B8C1B97D86013C385121036F86CBA501F9652B95CD9256F1E4FC
5097DC0D2EFEEB616374B12D41781A21258B74DFB94828586FE032AE49C42846A3F287178EF414EB
9D8E95EB731BFF11E82159FEF0C4C5FFB226856A3728733BF29D94B9A823576FD848AEAE01CA4690
A1FE4AF7D77694DAFCE25E181DB645170DC526557851790E77369240266E07661598FB17E9D328AD
98CE2021A2B628EE0DB0DD89E1C4CB7E64AF7AB6D3612ACE3D0B27660F09DD3AB19A2B7DFAB052E1
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
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.
