In this article we’ll be looking at how to add a simple runtime detection component to your SSIS packages. Although it’s possible to query the use32bitruntime value in the SSISDB catalog.executions table with an Execute SQL task, this only works for packages that have been deployed to IS Server. It’s better use a more universal solution that isn’t going to depend solely on the project deployment model.

The best way to detect the runtime of a package execution is using a script task. After the runtime has been detected, precedence constraints can be used to tell SSIS what to do in the case of 32-bit, or 64-bit execution.

In this example, the solution will end up looking like this:

runtimedesignerview

Here are the necessary steps:

  1. In an SSIS package, create a package-scoped Boolean variable named Is32Bit, this will be used to store the result of the script task
  2. Drag a Script Task into the Control Flow, in this case we name it Runtime Check.
    In Script Task Editor (properties), set ScriptLanguage to Visual Basic, and add User::Is32Bit to ReadWriteVariables.
  3. Click Edit script…, add the following code:
    Public Sub Main()
        Dim runtimemsg As String
        Dim runtimebits As String
        If (Environment.Is64BitProcess) Then
            runtimebits = "64-bit"
            Dts.Variables("User::Is32Bit").Value = False
        Else
            runtimebits = "32-bit"
            Dts.Variables("User::Is32Bit").Value = True
        End If
        runtimemsg = "Runtime: " + runtimebits
        MsgBox(runtimemsg)
        Dts.TaskResult = ScriptResults.Success
    End Sub
    
  4. After saving the script, add two Data Flow Tasks in the Control Flow: one for 32-bit execution, and another for 64-bit execution.
  5. Add precedence constraints between Runtime Check and each of the Data Flow Tasks. The evaluation operation for both precedence constraints must be set to Expression and Constraint, with value as Success. The expression will be slightly different between the two precedence constraints.
    For the one connecting to the 32-bit data flow: @[User::Is32Bit]
    For the one connecting to the 64-bit data flow: !@[User::Is32Bit]

The package is now set to run.

When executed in debug mode in SSIS, my package follows the 64-bit runtime path, and the 32-bit data flow is ignored.

runtime64bit

During the runtime check, a message box will pop up with the runtime mode.

runtimemsg

The message box feature is useful for debugging but should be removed for production.

Executing the task using the 32-bit DTExec gives the following result:

runtimedtexec32

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s