SSIS tip: Set variables’ values interactively in SSIS packages

This is a little tip for those who develop or debug SSIS packages.

In SQL Server Integration services, User namespace variables are assigned values that are used across the package.

When developing, testing or debugging packages, we assign multiple values to those variables to test different scenarios. This is done using the variables window.

However, SSIS Script tasks can allow us to key in values for selected variables in run time. This looks more fun and keeps us from taking chances when we forget assigning variables’ values.

A tiny example:

• Return list of processes running on SQL server instance from SYS.SYSPROCESSES (I know it is legacy) where process ID (Spid) is larger than or equal to a value ( >50 for example to return user processes).
• Dump the returned list of SPIDs and associated info (cmd, waittype, loginame,…etc) into a flat file.I’m using a script task and a Data flow task. The Data Flow task contains an OLE DB Source and a flat file destination.
  

  Package

  The script task idea is just simple :

  • Pass a variable to the script indicating that we need to set some variables(s) value(s) interactively (Interactive mode).
  • In the script body, we will check for interactive mode and if it is true then we will use an InputBox to receive variables values.
  • Set the package variable(s) value(s) to the value(s) returned from inputbox(s).

  I have attached two DSTX files, one for SS2005 and one for SS2008; the 2005 file can be easily upgraded with a tiny change in the function declaration, but I opted to attach a 2008 file for simplicity.

  Interactive_Variables_value_2005.dtsx

  Interactive_Variables_value_2008.dtsx

  Here is also the code for the script task (2008). I hope this helps.

  ‘ Microsoft SQL Server Integration Services Script Task
  ‘ Write scripts using Microsoft Visual Basic
  ‘ The ScriptMain class is the entry point of the Script Task.
  Imports System
  Imports System.Data
  Imports System.Math
  Imports Microsoft.SqlServer.Dts.Runtime
  Imports System.Text
  Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
    Partial Public Class ScriptMain
      Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
      Enum ScriptResults
          Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
          Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
      End Enum
      Dim PackageName As String
      Public Sub Main()
          PackageName = Dts.Variables("PackageName").Value.ToString()
          If Dts.Variables("interactive_values").Value.Equals(True) Then
              Dts.TaskResult = PromptForParams()
          Else
              Dts.TaskResult = ScriptResults.Success
          End If
      End Sub
      Public Function PromptForParams() As ScriptResults
          Dim Response As String
          Dim spidint As Integer
          ‘ Default Task output to failure
          Dts.TaskResult = ScriptResults.Failure
          Response = InputBox("Get a list of SQl server processes that begin with SPID X " " Enter SQL server SPID, must be integer and > 0:", PackageName)
          If Response = String.Empty Then
              MsgBox("Cancelled! ", MsgBoxStyle.OkOnly, PackageName)
              Return ScriptResults.Failure
          End If
          If Integer.TryParse(Response, spidint) And spidint > 0 Then
              Dts.Variables("spid").Value = spidint
          Else
              MsgBox("Invalid value spid, must be integer and larger than Zero ", MsgBoxStyle.Critical, PackageName)
              Return ScriptResults.Failure
          End If
          Return ScriptResults.Success
      End Function
  End Class