SSIS Package - User Variables

A SSIS (SQL Server Integration Services) package is very useful if you want to export data from your database. Recently I wanted to pass some input parameters to my SSIS package and had to struggle to figure out how this can be done. This really isn't very well documented and you can't find detailed instructions on the Internet so I thought I'd blog about it. Get a load of how complicated it turned out to be!

The first step is to add a variable to your SSIS package. In the Business Intelligence Development Studio (Visual Studio 2005), select SSIS > Variables


The first button on the left is for Add Variable. Enter the variable name, scope, data type, and value. The variables will be added to the DTSX file:

   1: <DTS:Variable>
   2:     <DTS:Property DTS:Name="Expression"></DTS:Property>
   3:     <DTS:Property DTS:Name="EvaluateAsExpression">0</DTS:Property>
   4:     <DTS:Property DTS:Name="Namespace">User</DTS:Property>
   5:     <DTS:Property DTS:Name="ReadOnly">0</DTS:Property>
   6:     <DTS:Property DTS:Name="RaiseChangedEvent">0</DTS:Property>
   7:     <DTS:VariableValue DTS:DataType="7">1/1/1950</DTS:VariableValue>
   8:     <DTS:Property DTS:Name="ObjectName">varBirthDate</DTS:Property>
   9:     <DTS:Property DTS:Name="DTSID">{6CE1ED2D-3720-4FDB-9590-FEDC2D78C797}</DTS:Property>
  10:     <DTS:Property DTS:Name="Description"></DTS:Property><DTS:Property DTS:Name="CreationName"></DTS:Property>
  11: </DTS:Variable>

The next step is to map a parameter in your SQL statement to the variable.

  1. Select the Data Flow tab.
  2. Right click on the Source Query
  3. Select Edit
  4. Replace a hard coded value in the SQL statement with a question mark to create a parameter
  5. Click the Parameters... button
  6. Select the variable from the drop down list
  7. Enter the parameter name in the format @Name for a stored procedure parameter or the field name



The third step is to add this variable to the package configuration so you can easily change the value in an XML configuration file.

  1. Select SSIS > Package Configurations
  2. Click the Edit button and then the Next button
  3. In the Select Properties to Export dialog box, check the Value property of the variable (which will not appear unless you complete the previous steps to create it first).


The variables will be added to the .dtsConfig file:

   1: <Configuration ConfiguredType="Property" Path="\Package.Variables[User::varBirthDate].Properties[Value]" ValueType="DateTime">
   2:         <ConfiguredValue>1/1/1950</ConfiguredValue>
   3: </Configuration>

The SSIS package can be run by an ASP.NET page or a web service by importing the Microsoft.SqlServer.Dts.Runtime namespace. The input parameter can easily by changed in the package configuration file by loading it as an XML document and editing the appropriate node.

You can use SSIS packages to do very complicated data transformations and exports. Basically it automates everything the SQL Server Import and Export Wizard does. So you can export your database to Access or Excel without repeating that whole process of going through the wizard steps. I've found this particularly useful to set up my own scheduled back up of a remote SQL Server database used by a hosted web application.


  • FYI to those that are trying to do this with DTEXECUI Set Values option, the Property Path value needs to be the whole string:
    For the User Variable = ServerAndPort =>


    Hope it helps someone...

  • can we edit these variables values in xml file and will it refelect to our ssis package?

  • Yes, you can edit the values in the package configuration .dtsConfig file and they will be used when you run the SSIS package.

  • its giving me error like " Parameter information can not be derived from sql statement . Set parameter information before preparing sql command " please let me know why this is happening

  • How would you go about it if you are querying an oracle database? The variables don't seem to like to work with Oracle sql.

  • Is there a way to have a dynamic variable ... for exemple having the current date everytime you just run the package without editing the config file ?

  • Our variable is called 'currentDT' and always is the current date. Not sure why which is what I was looking for info about.

  • Is there any possible way to map a variable from your package configuration to your SSIS package?? I know you did it here for an OLE Source, but I don't have one of them. I have a very simple package that just truncates a work table in SQL, then opens a flat file and fills the SQL table.

    Very simple, yet I can't find anything anywhere on how to set the filename in the package config so it's not hard-coded in my package. Yes, I know how to set the variable in the package config, but I can't find a way to access it in my package!


  • Geez - found the problem! Let this be a valuable lesson for everyone out there!

    The problem turned out to be nothing more than the wrong collation for the fields in the config file on SQL Server.

    Try to find that solution in the literature!

  • How would you go about it if you are querying an oracle database? The variables don't seem to like to work with Oracle sql.

  • This is exactly what I needed, Thank You!

    I've spent days trying to figure this out. I used a variable for a path in an FTP task. If anyone could benefit from what I did I'm happy to share.

  • How to use package variables in script component....?

    like say i have a package variable named "srctb" of type string that contains the name of table..and i want to use it in script component edit

    INSERT INTO Person.Address2(AddressID, City) " & _
    "VALUES(@addressid, @city)"

    instead of "Person.Address2" i want to pass package variable "srctb"...

    how should i proceed....
    pls help me

    thanx in advance...

  • @Nausif
    Not sure but maybe u can create a variable which is set to Evalaute as expression as TRUE. THen goto Expression for that variable and write ur whole query
    "INSERT INTO" +[@User:srctb] + "(AddressID, City) \" & _

    \"VALUES(@addressid, @city)"
    something like this.
    ANd then use this variable in ur Tasks.

  • Can I use variables to change parameters of SSIS package dynamically inorder to execute different web services?

  • Thanks for posting *with pictures*. Am using to make my life easier!

  • I have an SSIS package that reads in values by OLE DB and has the Purchase Order number passed in already. I've added a package variable in the calling to the SSIS package to pass in the username from the web application. My question is: how do I retrieve that value to update that column, which I've called ExportUser, in the SSIS package?

  • Hi renuka
    u r missing the parameter key like@ in the Set Query Parameter.

  • Hello my loved one! I wish to say that this post is awesome, nice written and include almost all significant infos.
    I want to peer extra posts something like this.

  • How can i pass two variable? Ex where BirthDate between ? and ?

  • It’s a beautiful country, more people need to recognise that.

  • Hmm is anyone else experiencing problems with that the images on this blog loading?
    I’m trying to get out if it can be a problem on my end or if it’s that the blog.
    Any feed-back would be vastly appreciated.

  • Right now it seems like Movable Type is the
    top blogging platform out there right now. (from what I've read) Is that what you are using on your blog?

Comments have been disabled for this content.