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:

SELECT MAX(SALES_ORG) AS SALES_ORG,
 MAX(DISTR_CHANNEL) AS DISTR_CHANNEL,
 DATEADD( hh, [% fixutc %], MIN( START_DT ) ) AS START_DATE,
 DATEADD( hh, [% fixutc %], MIN( START_DT ) ) AS EFFECTIVE_START_DATE,
 PRODUCT_NAME AS PLI_PROD_NAME,
 MAX( RATE_ZPMC ) AS MAX_PRICE,
 VENDR_NAME,
 VENDR_LOC,
 VENDR_BU,
 DATEADD( hh, [% fixutc %], MAX( END_DT ) ) AS END_DATE,
 DATEADD( hh, [% fixutc %], MAX( END_DT ) ) AS EFFECTIVE_END_DATE
FROM TMP_PRI_LST
WHERE ROW_STATUS = 'FOR_IMPORT'
 AND ERROR_MSG IS NULL
GROUP BY PRODUCT_NAME, 
 VENDR_NAME, 
 VENDR_LOC, 
 VENDR_BU

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 )
'validations
 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 )

  next

 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

 next

 '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

 next

 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.

Conclusion

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.