Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

VBscript hacks for DTS packages

By Alceu Rodrigues de Freitas Junior,

Limitation 1: using several properties with Execute Package task

Whenever the developer needs to use the Execute Package Task, and provides information as parameters, using Inner Package or Outer Package Global Variables are the unique alternatives to do parameters. If the package to be executed just needs one or two parameters, that's fine. But if it needs lots of them, then the developer will be in trouble, having to use as many global variables as necessary to pass the parameters.

A nice way to do it is, instead of using several global variables, is using just one global variable as a Dictionary (Scripting.Dictionary) with all keys and values that are necessary and then receiving this Dictionary in the package that will be executed.

There is a limitation in DTS that using a Dictionary will force the user to switch off the option "Explicit Global Variables", which is always a good practice of programming DTS packages since DTS will accept only "primitive" variables, not objects (it's possible to set the Global Variables with type "other" an associate a Dictionary to it).

The workaround about this limitation and still use "Explicit Global Variables" is transforming the Dictionary in a VBscript string, passing the parameter as a string and them compiling again the given code in the package that will be executed by using the VBscript function Execute to compile the received code into a new Dictionary object. This method is something like a crude object serialization and it will be shown in the commented code below:

Option explicit
Function Main()
 Dim oParams
Dim sTempTable sTempTable = "TMP_PRI_LST" Set oParams = CreateObject("Scripting.Dictionary") oParams.Add "PKG_LOG_FILE", DTSGlobalVariables("package_log_file").Value oParams.Add "TABLE_NAME", sTempTable oParams.Add "PACKAGE_NAME", DTSGlobalVariables("process_name").Value oParams.Add "MOVE_DIR_ERROR", DTSGlobalVariables("move_dir_error").Value oParams.Add "INI_FILENAME", DTSGlobalVariables("ini_filename").Value oParams.Add "FILE_TO_LOAD", DTSGlobalVariables("file_to_load").Value oParams.Add "EMAIL_ADDRESS", DTSGlobalVariables("addressee").Value DTSGlobalVariables("err_ctl_pkg_parameters").Value = createCode( oParams ) Main = DTSTaskExecResult_Success End Function Function createCode( aEntries ) Dim sCode
Dim sKey if ( not ( aEntries.Count = 7 ) ) then Err.Raise 1, "PackageName", "createCode expects a Dictionary with 7 key entries" end if sCode = "Function createDictionary()" & vbCRLF sCode = sCode & "Set dictionary = CreateObject(" & chr(34)_ & "Scripting.Dictionary" & chr(34) & ")" & vbCRLF For Each sKey in aEntries.Keys() sCode = sCode & "dictionary.Add " & chr(34) & sKey & chr(34) & ", " _ & chr(34) & aEntries.Item(sKey) & chr(34) & vbCRLF Next sCode = sCode & "set createDictionary = dictionary" & vbCRLF
sCode = sCode & "End Function" createCode = sCode End Function

In the code above, the result of code compilation will be kept in a global variable declared as string type (err_ctl_pkg_parameters).

After that, is just a matter to setup the Execute Package Task using this global variable as a Outer Package Global Variable, as shown below:

In the DTS package that will be executed by the new Execute Package Task, is necessary to add an additional ActiveX Task to compile the Dictionary again and recover its keys/values, adding it to previous created Global Variables with the proper values.

Here goes the code to recover the object:

Function Main()
Dim oParameters
'GET the Dictionary Object and use it later to extract parameters from it
 set oParameters = compileCode(DTSGlobalVariables("err_ctl_pkg_parameters").Value )
 If ( Err.Number <> 0 ) Then

  Main = DTSTaskExecResult_Failure

 End if
 DTSGlobalVariables("PKG_LOG_FILE").Value = oParameters.Item("PKG_LOG_FILE")
 DTSGlobalVariables("TABLE_NAME").Value = oParameters.Item("TABLE_NAME")
 DTSGlobalVariables("PACKAGE_NAME").Value = oParameters.Item("PACKAGE_NAME")
 DTSGlobalVariables("MOVE_DIR_ERROR").Value = oParameters.Item("MOVE_DIR_ERROR")
 DTSGlobalVariables("INI_FILENAME").Value = oParameters.Item("INI_FILENAME")
 DTSGlobalVariables("FILE_TO_LOAD").Value = oParameters.Item("FILE_TO_LOAD")
 DTSGlobalVariables("EMAIL_ADDRESS").Value = oParameters.Item("EMAIL_ADDRESS")

Main = DTSTaskExecResult_Success
End Function

Function compileCode( sCode )

 Execute( sCode )

'execute new function by name
set compileCode = createDictionary End Function

Advantages of using this method is that the developer can add very easily a new parameter or change a old one, since the Dictionary will just create a new key and assign a value to it automatically. Of course, it may be necessary to create Global Variables in the called package per Dictionary key, but at least the developer will not need to expose those variables to caller packages. Anyway, just one Global Variable will need to be created in the caller package.

The drawback is that since Execute function can compile anything (or at least tries to do it) some bad code can be injected here. If you have a controlled development environment, this can be a fair price to pay.

Limitation 2: using complex queries with parameters in Execute SQL Task

Whenever a developer starts using the Execute Query Task in DTS packages, sooner or later he will find a situation where the given query will not be accepted by the (very restrictive) parser of the task. This is especially true when it is necessary to use queries with binding parameters.

While it's quite frustrating (because the query can even work outside the DTS package) if the query needs to be build dynamically (some values need to be fetch from the database or a INI file, for example) the developer is in trouble: using VBscript to build the query doing lots of concatenation operators may be a pain if the query is large and quite often some problems will happen with code compilation if a string was not escaped correctly (the VBscript parser in the code editor will not help much when something like this is wrong).

The hack for this is using query templates, like the example below:

DATEADD( hh, [% fixutc %], MIN( START_DT ) ) AS START_DATE,
DATEADD( hh, [% fixutc %], MAX( END_DT ) ) AS END_DATE,

The query is one that the developer will fetch from the Query Analyzer (holpefully well tested) with the binding parameters replaced with some funny characters (the ones in this article were borrowed from the nice Template Toolkit template system used by Perl programmers) and a parameter name. In the example, the binding parameter is [% fixutc %]. Then the developer will need just to define a global variable as constant and paste the query as the value.

Later, some code in an ActiveX Script task will be necessary to process such templates, replacing the parameters with some useful values. An implementation in VBscript is shown below:

Function generateQuery( oParamDic )
Dim oPackage
Dim sLogKey

Set oPackage = DTSGlobalVariables.Parent

if not ( IsEmpty( DTSPackageLog ) ) then

DTSPackageLog.WriteStringToLog vbCRLF
DTSPackageLog.WriteStringToLog "Logging received parameters for generateQuery function"
DTSPackageLog.WriteStringToLog vbCRLF

for each sLogKey in oParamDic.Keys

DTSPackageLog.WriteStringToLog "{" & sLogKey & "} => " _ & oParamDic.Item( sLogKey )


end if

if ( oParamDic.Count < 2 ) then

Err.Raise 1, oPackage.Name, "Dictionary must have at least two keys.", "", 0

end if

if ( not( oParamDic.Exists("SQL") ) or IsNull( oParamDic.Item("SQL") ) or IsEmpty( oParamDic.Item("SQL") ) or oParamDic.Item("SQL") = "" ) then

Err.Raise 1, oPackage.Name, "Dictionary SQL key must exists and be defined.", "", 0

end if Dim oPlaceHolder
Dim sQuery, sKey

sQuery = oParamDic.Item("SQL")

set oPlaceHolder = new RegExp
oPlaceHolder.IgnoreCase = TRUE
oPlaceHolder.Global = TRUE

for each sKey in oParamDic.Keys

'do not process SQL key
if not sKey = "SQL" then

if ( ( oParamDic.Item( sKey ) = "" ) or IsNull( oParamDic.Item( sKey ) ) ) then

Err.Raise 1, oPackage.Name, "Key '" & sKey & "' does not have a valid value", "", 0

end if oPlaceHolder.pattern = "\[\%\s" & sKey & "\s\%\]"
sQuery = oPlaceHolder.replace( sQuery, oParamDic.Item( sKey ) )

end if


'validating output
oPlaceHolder.pattern = "\[\%\s\w+\s\%\]"

Dim oMatches, oMatch
Dim iUnreplacedCounter

set oMatches = oPlaceHolder.execute( sQuery )
iUnreplacedCounter = 0

for each oMatch in oMatches

'To avoid error when running in Designer window
if not ( IsEmpty( DTSPackageLog ) ) then

DTSPackageLog.WriteStringToLog "ERROR: the placeholder '" & oMatch.Value & " was not replaced by " _
& "generateQuery function with the given parameters"

end if

iUnreplacedCounter = iUnreplacedCounter + 1


if iUnreplacedCounter > 0 then

Err.Raise 1, oPackage.Name, "Found a total of " & iUnreplacedCounter _
& " unreplaced placeholders. This will probably cause the SQL query to be invalid. Aborting...", "", 0

end if generateQuery = sQuery End Function

The example shows some verification code, which is a nice idea to avoid problems or at least provide some debugging information.

Once the query is ready, is just a matter to use a Dynamic Properties Task to assign the query to an Execute Query Task.

Advantages of this method are that editing the query and pasting it as a global variable is much simpler and avoids VBscript compilations errors with an unescaped character. It's easy too to edit the query using Disconnect Edit directly (see screenshot below):

The drawback is if the query needs to use LIKE statements the developer will need to replace the template binding parameter characters used as identifiers by the template parser.

Calling the functions is just as easy as it gets:

Dim oDic 
set oDic = CreateObject("Scripting.Dictionary") oDic.Item("SQL") = DTSGlobalVariables("extract_temporary").Value
oDic.Add "fixutc", DTSGlobalVariables("fix_utcdatetime").Value

where v_extract_temporary is a global variable with a text type that holds the query template.


It's possible to overcome some limitations of DTS by using some tricks with VBscript code. As is not very easy to share such functionality (without using VB or VC++) it's highly recomended to use a source control server (like CVS or SourceSafe) to hold the last versions of "stable" code.

Total article views: 9210 | Views in the last 30 days: 1
Related Articles

How to use table valued function with parameter in ole db source

table valued function which accepts parameter from package variable.


Passing SSIS Package Variable To Stored Procedure As Parameter

Passing getdate() variable to a Stored Proc's Input parameter


SSIS Parameter in Oracle query

Pass parameter to an oracle query in a package


Using Variables and Parameters SSIS

When creating an SSIS package it is always a best practice to use variables, and parameters in 201...