Biography:

Location: Dayton, OH
College: Cedarville University, Cedarville, OH
Degree: B.A. Management Information Systems (2002)
Certifications: Microsoft Certified Professional (MCP) 2002

Interests:

Music, Playing Guitar, Working on Cars, Home Improvement, Photography, Computers, Website Programming, Driving in the snow, NHL Hockey, Sand Volleyball, Settlers of Catan board game

Favorites:

Foods: Lasagna, Carrot Casserole, Chicken Parmesan
Sports: Hockey, Football, Volleyball, Soccer
Desserts: Peanut Butter Pie, Peanut Butter Passion ice cream

Employment Information:

Current Resume: Download PDF Resume


Contact Me:
Use this area to send me a note or message:
Name:
Email:
Comments:
Security Code:

Fun links you should probably check out:


Fun games you might want to play:


Current Code Samples:


Future Code Samples:

  • LINQ-to-XML Dynamic Where Clauses Using Ternary Operators
  • C# List delegate alphanumeric sort
  • Using HTML form input arrays with ColdFusion 7
  • Capture selected text or cursor position in a TEXTAREA with JavaScript
  • Show/Hide all SELECT input items on a page with JavaScript
  • Show/Hide element with opacity level fading using JavaScript
  • CAPTCHA form-protection of images using PHP and GD 2.0 library
  • Resizing images using PHP and the GD 2.0 library
  • Rotating images using PHP and the GD 2.0 library
  • Cropping images using JavaScript, PHP, and the GD 2.0 library
  • Add image watermarks using PHP and the GD 2.0 library
CODE TUTORIAL: SSIS Packages, Dynamic Parent/Child Variables
Lately, I've been working more with SSIS Packages in SQL Server 2005. It has been a frustrating, and at some times daunting task to get acclimated to developing data migration and data transformation packages, but overall seems to have good use for the production business environment.

Today we'll talk more about how we can use SSIS Packages in a grandparent/parent/child scope, and about how we can pass variables between packages in the easiest fashion. There is a great deal of confusion out there on newsgroups and message boards about how to work with variables in SSIS, and even though I'm no master, I'll lend what I've discovered for you to view.

Part 1: Variable Scope

First, let's talk about the concept of variable scope within SSIS Packages. Variables in SSIS have scope in the package or task they are created for. It is easy to view what variables are available within which scope by viewing the "Variables" toolbar in Visual Studio or Business Intelligence Development Studio (BIDS). You'll notice that in the screenshot below there is a column provided that shows "Scope". Scope in SSIS can be set to variables existing at the "package" level, or scope can be set to variables existing an the individual "task" level. Task-Level variables are really not too handy in my personal opinion, but I'm sure there are folks out there who are using them for some reason or another.



As you can see, I have (4) variables created here in my test SSIS package which I've named "Grandparent.dtsx". I've tried to stick to Hungarian notation as much as possible for this demonstration, using the "_" prefix to signify a global variable and using the "s" prefix to signify a local variable that is a string.

I have a variable called sVariableList that is essential to include in all packages for this example, whether parent or child. Since SSIS (and VB Script Tasks) are annoying in that they will not automatically allow you access to any parent variables in other parent packages that you should (as a child package) have access to, we have to get creative and do the work ourselves. The solution I discovered is helpful for programmers who have the following requirements:
  1. You want to make your child packages "self aware" of any parent variables that may exist. In your scenario, parent variables superceed and take precedence over any child package variables that are set in the Code Designer view of Visual Studio (or BIDS)
  2. You want your child packages to run properly WITHOUT requiring use of a Parent package to fire them. You desire to create SSIS Packages as functional pieces to a Business Logic "puzzle" and want to re-use them however you wish or combine them with other packages
  3. You don't mind having to set a single package-level variable that contains the comma-separated names of all of the package-level variables


Part 2: SSIS Package Debugging Shortcomings

I would dare to venture that most people are frustrated with SSIS Packages (and debugging them) for the following reasons:

  1. SSIS Package debugging is limited to the following Events which can be captured for each "task" within a "package":
  2. SSIS Package "Script Task" debugging is limited to the parent package that is executed (Child packages with Script Tasks in them will not stop execution at any Breakpoint you set within the Code Designer, only at generic breakpoints set on specific "tasks" in the child package)
  3. SSIS Package debugging actually works on a COPY of your original source code at runtime, so any changes made to your code during Debug will NOT BE SAVED when you close or stop the Debug session. (This is REALLY frustrating for new SSIS Package Developers, as it is not readily clear that the code you are modifying is a COPY of the script you originally wrote, not your actual code)
  4. Because of the issue above, not all breakpoint locations that are set within Script Tasks will fire on the lines that you set them on. Sometimes if there is a difference of whitespace or commented green lines at the top of your VB Script code, your breakpoints will be "shifted" to another line that was not even close to the line you desired to break on. This issue is also quite annoying for the beginner who is learning SSIS Package Development.


One other issue that I feel is a shortcoming with SSIS packages is the need to manually specify (at Design time) which variables a Script Task will have read access to, and which variables it will have read & write access to. Unfortunately, SSIS is limited in that you are unable to just start accessing variables in your Script Tasks without "locking" them so you can read from them or so you can write to them. "Locking" variables effectively allows you to prevent other processes from modifying the contents of that variable until you are done reading or writing to it. Below is a screenshot of the area you see at Design time where you have to manually and explicitly enter all of the variable names you would like to access at this package level:


Brian Knight (A Microsoft SQL Server MVP) shows two different examples of how to reference/unlock variables in SSIS Packages for use within Script Tasks in a nice blog entry on his website. This page gave me hope that there could be a better way to access variables inside Script Tasks than to manually have to specify all of the variables you want read and write access to at Design time.


You can assign the value for ReadOnlyVariables and for ReadWriteVariables using the "Expressions" option on this propery panel, but unfortunately, you have to reference a variable that contains the names of the variables you want to use. I find this terribly cumbersome and frustrating to deal with, and therefore have spent a good amount of time working on a solution that should work well for almost anyone seeking to use the child/parent package relationship.


Part 3: Grandparent/Parent/Child SSIS Package Demo

To get started with this demo, feel free to download the Visual Studio or BIDS solution file (.sln) from this download link (129K)

Inside this .sln solution file, I have (3) main packages:
  • Grandparent.dtsx

  • Parent.dtsx

  • Child.dtsx



These are VERY basic packages. Just a "Script Task" and then a "Execute Package Task" in each one. Let's open the code for the "Script Task" inside Grandparent.dtsx:
Visual Basic CODE: "Grandparent.dtsx Script Task"
Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

  Public Sub Main()
  
    MsgBox(CStr("Main() inside Grandparent.dtsx"))

    Dim _VariableList As String
    Dim sVariableList As String
    Dim vars As Variables
    
    Try
        ' Try to lock the global _VariableList for reading (if exists)
        Dts.VariableDispenser.LockOneForRead("_VariableList", vars)
        _VariableList = vars("_VariableList").Value.ToString()
    
        ' Try to lock the local sVariableList for reading (if exists)
        Try
            Dts.VariableDispenser.LockOneForRead("sVariableList", vars)
            sVariableList = vars("sVariableList").Value.ToString()
        Catch ex As Exception
            Throw New Exception("sVariableList could not be locked for reading, please ensure that a local string variable named sVariableList exists for this package")
        End Try
    
        ' Concatenate _VariableList with sVariableList
        If (Not _VariableList = Nothing) Then
            _VariableList = _VariableList + ","
        End If
        sVariableList = _VariableList + sVariableList
    
        ' Write the new sVariableList value to our global _VariableList
        Dts.VariableDispenser.LockOneForWrite("_VariableList", vars)
        vars("_VariableList").Value = sVariableList
    
    Catch
        Try
            ' Try to lock the local sVariableList for reading (if exists)
            Dts.VariableDispenser.LockOneForRead("sVariableList", vars)
            sVariableList = vars("sVariableList").Value.ToString()
        Catch
            ' If neither the global _VariableList or the local sVariableList exist
            Throw New Exception("The global _VariableList and local sVariableList were not found and could not be unlocked for reading. (Ensure that sVariableList exists as a String variable in this SSIS Package, and that it contains a comma-separated list of all variables in this SSIS Package)")
        End Try
    End Try
    
    MsgBox(CStr("Grandparent.dtsx sVariableList: " + sVariableList))
    
    Dts.TaskResult = Dts.Results.Success

  End Sub

End Class

You can see by my comments in orange that I'm first trying to access the "_VariableList" variable using a Try/Catch block. If the global "_VariableList" variable doesn't exist, this package will skip it and will look for the "sVariableList" local variable. If the "sVariableList" local variable does not exist, then we throw an Exception that has a detailed explanation of what went wrong and how the issue can be remedied. If we ARE able to lock the "_VariableList" variable for reading, then we read the value in to a local string variable. We then try to load the "sVariableList" variable into a local string variable, and then attempt to concatenate these two strings of comma separated variables together. If all goes well, we'll have a "sVariableList" string that contains all of the inherited global "_VariableList" variable names, as well as the local "sVariableList" variable names.

In the next section of code, I create a local multi-dimensional array to use to store all of the variable names and their values that I can retrieve. I used a multi-dimensional array here, but this could be modified to use a more handy struct or key-value pair lookup list. I will split the comma-separated string into an array, then loop through the items in that arrray and try to lock them for reading, then retrieve their value and insert it into my multi-dimensional array. When this loop is complete, I should have access to all variables in my current package, as well as all variables in packages above this one, in it's "package ancestry".
Visual Basic CODE: "Grandparent.dtsx Script Task"
' Split the comma-separated list of variables into an Array
Dim sVariableList_arr As String()
sVariableList_arr = Split(sVariableList, ",")

Dim _variableArray(0 To sVariableList_arr.Length, 0 To 1) As Object

' Loop to set value of multi-dimensional array sVariableList_arr
Dim i As Integer
Dim sVariableDataType As String
For i = 0 To sVariableList_arr.Length - 1
    Dim item As String
    item = sVariableList_arr(i)
    ' Need to lock one variable for reading, copy to vars variable
    Dts.VariableDispenser.LockOneForRead(item, vars)
    ' Need to determine what DataType this variable is
    sVariableDataType = vars(item).DataType.ToString()
    ' Cast the variable to a usable format and then store it for use
    If (sVariableDataType = "Int32") Then
        _variableArray(i, 0) = item
        _variableArray(i, 1) = Convert.ToInt32(vars(item).Value)
    ElseIf (sVariableDataType = "String") Then
        _variableArray(i, 0) = item
        _variableArray(i, 1) = vars(item).Value.ToString()
    End If
Next


So now you have a _variableArray() multi-dimensional array that has values in it from ALL package-level variables for the current package, as well as ALL ancestor variables that were passed down using the _VariablesList variable. To output all of the variables that you have at this point (along with their corresponding value), you can use this code:
Visual Basic CODE: "Grandparent.dtsx Script Task"
' Output values of all items in our multi-dimensional array
Dim OutputString As String
For i = 0 To _variableArray.GetUpperBound(0) - 1
    Dim name As String
    Dim value As String
    name = _variableArray(i, 0).ToString()
    value = _variableArray(i, 1).ToString()
    OutputString = OutputString + "Name: " + name + vbCrLf + "Value: " + value + vbCrLf
Next
MsgBox(CStr("_variableArray Contains:" + vbCrLf + vbCrLf + OutputString))


Putting all of the code from this tutorial together, you end up with something like this:
Visual Basic CODE: "Grandparent.dtsx Script Task"
Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

  Public Sub Main()
  
    MsgBox(CStr("Main() inside Grandparent.dtsx"))

    Dim _VariableList As String
    Dim sVariableList As String
    Dim vars As Variables
    
    Try
        ' Try to lock the global _VariableList for reading (if exists)
        Dts.VariableDispenser.LockOneForRead("_VariableList", vars)
        _VariableList = vars("_VariableList").Value.ToString()
    
        ' Try to lock the local sVariableList for reading (if exists)
        Try
            Dts.VariableDispenser.LockOneForRead("sVariableList", vars)
            sVariableList = vars("sVariableList").Value.ToString()
        Catch ex As Exception
            Throw New Exception("sVariableList could not be locked for reading, please ensure that a local string variable named sVariableList exists for this package")
        End Try
    
        ' Concatenate _VariableList with sVariableList
        If (Not _VariableList = Nothing) Then
            _VariableList = _VariableList + ","
        End If
        sVariableList = _VariableList + sVariableList
    
        ' Write the new sVariableList value to our global _VariableList
        Dts.VariableDispenser.LockOneForWrite("_VariableList", vars)
        vars("_VariableList").Value = sVariableList
    
    Catch
        Try
            ' Try to lock the local sVariableList for reading (if exists)
            Dts.VariableDispenser.LockOneForRead("sVariableList", vars)
            sVariableList = vars("sVariableList").Value.ToString()
        Catch
            ' If neither the global _VariableList or the local sVariableList exist
            Throw New Exception("The global _VariableList and local sVariableList were not found and could not be unlocked for reading. (Ensure that sVariableList exists as a String variable in this SSIS Package, and that it contains a comma-separated list of all variables in this SSIS Package)")
        End Try
    End Try
    
    MsgBox(CStr("Grandparent.dtsx sVariableList: " + sVariableList))
    
    ' Split the comma-separated list of variables into an Array
    Dim sVariableList_arr As String()
    sVariableList_arr = Split(sVariableList, ",")
    
    Dim _variableArray(0 To sVariableList_arr.Length, 0 To 1) As Object
    
    ' Loop to set value of multi-dimensional array sVariableList_arr
    Dim i As Integer
    Dim sVariableDataType As String
    For i = 0 To sVariableList_arr.Length - 1
        Dim item As String
        item = sVariableList_arr(i)
        ' Need to lock one variable for reading, copy to vars variable
        Dts.VariableDispenser.LockOneForRead(item, vars)
        ' Need to determine what DataType this variable is
        sVariableDataType = vars(item).DataType.ToString()
        ' Cast the variable to a usable format and then store it for use
        If (sVariableDataType = "Int32") Then
            _variableArray(i, 0) = item
            _variableArray(i, 1) = Convert.ToInt32(vars(item).Value)
        ElseIf (sVariableDataType = "String") Then
            _variableArray(i, 0) = item
            _variableArray(i, 1) = vars(item).Value.ToString()
        End If
    Next
    
    ' Output values of all items in our multi-dimensional array
    Dim OutputString As String
    For i = 0 To _variableArray.GetUpperBound(0) - 1
        Dim name As String
        Dim value As String
        name = _variableArray(i, 0).ToString()
        value = _variableArray(i, 1).ToString()
        OutputString = OutputString + "Name: " + name + vbCrLf + "Value: " + value + vbCrLf
    Next
    MsgBox(CStr("_variableArray Contains:" + vbCrLf + vbCrLf + OutputString))
    
    Dts.TaskResult = Dts.Results.Success

  End Sub

End Class

This is a simple demo, but one that appears to work. The nice part about this code, is that it will work perfectly with only a single package, but will also work with many parent packages, and each child package will not have to have code modified in order to see variables from the new parent package. This essentially means that you CAN write SSIS Packages where each package is "self aware" and can determine if it has access to variables in parent packages above it. Unfortunately, I've not tested this beyond the Grandparent -> Parent -> Child relationship, but I'm sure it is extensible to be used to even more levels of packages.

Let me know what you think, and leave me a note if this code helps you sort out an issue you were having!

- Justin Tubbs