SSIS tip: Set variables’ values interactively in SSIS packages

Nov 11, 2009 / By Mohammed Mawla

Tags: ,

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

    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
    

3 Responses to “SSIS tip: Set variables’ values interactively in SSIS packages”

  • g says:

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

  • Farhan says:

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

  • Mohamed Mawla says:

    Farhan,

    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.

    HTH

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>