SSIS tip: Set variables’ values interactively in SSIS packages

Posted in: Technical Track

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.



    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()
                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
                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
Want to talk with an expert? Schedule a call with our team to get the conversation started.

3 Comments. Leave new

Thnx! everybody says input can’t be done in ssis, but it can!


if we configure this ssis package in sql job then message box does not comes and job fails.
any suggestion?

Mohamed Mawla
July 1, 2011 9:51 am


As stated , U should only use this tip when “debugging or testing” Packages in interactive mode , not when putting it in production.

if you put that in job , make sure u disable the Script task first.



Leave a Reply

Your email address will not be published. Required fields are marked *