Click here to Skip to main content
15,851,514 members
Articles / Web Development / ASP.NET

How to use global variable in Microsoft SSIS (DTS) package

Rate me:
Please Sign up or sign in to vote.
2.06/5 (9 votes)
28 Apr 2006CPOL3 min read 177.6K   572   28   7
How to use global variable in Microsoft SSIS (DTS) package


Microsoft SQL Server 2005 Integration Services has new interface about DTS ( data transformation service). It has no many helpful examples for developers to follow. One of sticky things is to how to use global variables. Hopefully the following will save you a lot of frustrations.

the scenario is like this: (1). I want to dump the data from one table (table1, primary key is theId - int, fields are FirstName - varchar(50), LastName - varchar(50)) in database 1 (db1) to another same structure table(table2, primary key is theId - int, fields are FirstName - varchar(50), LastName - varchar(50)) in database 2(db2) based on table1.theId > max(table2.theId). This is a very common scenario to transfer the data. (2). I need to pass table2.theId to the SQL statement: SELECT * FROM table1 WHERE table1.theId > max(table2.theId) (3) you make sure table1 has more rows and TheId is bigger than table2

Instead of thinking of table2.theId as a variable to pass, we can pass all the SQL statement as a global string variable.

Here are steps how to achieve this :

  1. start Microsoft SQL Server 2005 -> SQL Server Business Intelligence Development Studio
  2. click File -> New -> Project -> choose "Integration Services Project" -> name your project name like ssisTest
  3. click SSIS -> Variable
  4. Under Variable window, Add variable:
    name = strSQL
    scope = Package ( this means global variable for this package )
    Data Type = string
    value = select * from table1
  5. At the left bottom corner, you will connection manager, put your cursor at the area, right mouse click to add New OLE DB connection. You add one connection for db1 and another connection for db2.
  6. click control Flow tab, ctrl+alt+x to get toolbox, drag a Script Task under Control Flow Items to control Flow tab. Highlight Script Task 1 and double click Script Task 1 icon -> Highlight "Script" on the left panel of Script Task Editor -> input "strSQL" for ReadWriteVariables -> click "Design Script..."
  7. Here are the code
    ' 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.Data.SqlClient
    Imports System.Data.Common
    Public Class ScriptMain
    ' The execution engine calls this method when the task executes.
    ' To access the object model, use the Dts object. Connections, variables, events,
    ' and logging features are available as static members of the Dts class.
    ' Before returning from this method, set the value of Dts.TaskResult to
    ' indicate success or failure.
    ' To open Code and Text Editor Help, press F1.
    ' To open Object Browser, press Ctrl+Alt+J.
    Public Sub Main()
    ' Add your code here
    Dim strConnection As String
    strConnection = "Data Source=server2;Initial Catalog=db2;Integrated Security=True"
    Dim connection As New SqlConnection(strConnection)
    Dim strQuery As String
    strQuery = "select max(TheId) MaxTheId from Table2"
    Dim command As New SqlCommand(strQuery, connection)
    Dim srReader As SqlDataReader
    srReader = command.ExecuteReader()
    Dim strTheId As String
    ' strTheId = "5"
    While (srReader.Read())
    strTheId = srReader.Item(0).ToString
    End While
    ' here create the right strSQL string
    Dts.Variables("strSQL").Value = "SELECT * FROM TABLE1 WHERE TheId > " & strTheId
    ' this is to test if the strSQL string is right
    Dts.TaskResult = Dts.Results.Success
    End Sub
    End Class

    Then save it and close this window

  8. click Data Flow tab and click the link on the center of this tab to add a data flow task
  9. On Data Flow tab, drag "OLE DB source" from toolbox's "Data Flow Source" section double click this OLE DB Source icon and under OLE DB Source editor,
    1. choose server1.db1 for OLE DB connection manager
    2. Data access mode: SQL command from variable
    3. Variable name: User::strSQL

    then click OK

  10. On Data Flow tab, drag "OLE DB Destination" from toolbox's "Data Flow Destinations" section double click this OLE DB Destination icon and under OLE DB Destination editor,
    1. choose server2.db2 for OLE DB connection manager
    2. Data access mode: Table or view - fast load
    3. Name of the table or the view: [dbo].[table2]

    then click Mappings on the left pane and make sure all the fields are mapped right then click OK

  11. Now you can click Build -> build ssisTest and click Debug -> Start with debugging
  12. You can verify the data which are inserted to db2's table2 from db1's table1

This way you have successfully used the global variable in Microsoft SSIS ( DTS) package.

Please enjoy,


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By
Software Developer (Senior)
United States United States
C# and .NET Core
Typescript, node.js, jQuery, jQuery Mobile
Web Api and MVC
Angular and AngularJS
Databases (SQL Server and Oracle) and No-SQL Databases
Agile and Waterfall

Comments and Discussions

GeneralMy vote of 1 Pin
Vimalsoft(Pty) Ltd22-Oct-09 3:03
professionalVimalsoft(Pty) Ltd22-Oct-09 3:03 
GeneralSource Files Not Found Pin
Javad Bayani6-Jul-07 23:18
Javad Bayani6-Jul-07 23:18 
GeneralUse ExecSQL task instead of script Pin
r.stropek4-Jun-06 21:23
r.stropek4-Jun-06 21:23 
QuestionHow to use SQL Command variable in OLEDB Destination? Pin
jchouy19-May-06 13:06
jchouy19-May-06 13:06 
AnswerRe: How to use SQL Command variable in OLEDB Destination? Pin
r.stropek4-Jun-06 21:32
r.stropek4-Jun-06 21:32 
Yes, there is. Do not use Oledb Destination but the "OLE DB Command" shape. There you can specify e. g. an update statement in the "SqlCommand" property. Here is an example:

update DimCurrency set CurrencyName=?

In the "Column Mappings" tab you can map input columns to your parameters. If you want to use variables you have to create columns out of your variables first. You can do that using the "Derived Column" shape.

If you do not want a solution in data flow but you want to do something in control flow use Exec SQL task with parameter mapping. It works, too.


Rainer Stropek
Visit my blog at

GeneralRe: How to use SQL Command variable in OLEDB Destination? Pin
jchouy8-Jun-06 9:18
jchouy8-Jun-06 9:18 
GeneralRe: How to use SQL Command variable in OLEDB Destination? Pin
r.stropek8-Jun-06 23:19
r.stropek8-Jun-06 23:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.