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:
Here are the necessary steps:
- In an SSIS package, create a package-scoped Boolean variable named Is32Bit, this will be used to store the result of the script task
- 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.
- 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
- After saving the script, add two Data Flow Tasks in the Control Flow: one for 32-bit execution, and another for 64-bit execution.
- 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.
During the runtime check, a message box will pop up with the runtime mode.
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: