SSIS tip: Set variables' values interactively in SSIS packages

2 min read
Nov 11, 2009

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

DTSXThe 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

Get Email Notifications

No Comments Yet

Let us know what you think