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. 

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