How To Set and Use Variables in SSIS Execute SQL Task

I couldn’t find any step-by-step resources on this so I thought I’d blog it.  This is done in SQL Server 2008 R2. In this scenario I will use a SQL Statement to populate an SSIS variable, and then use that variable in another SQL Statement.

Create your variables

If the variables window isn’t visible already, from the menu select View –> Other Windows –> Variables.

SNAGHTML1fb1c4da

This will bring up your variables window on the left hand side of your screen.  You should be able to tab between it and the Toolbox.  You may have to pin it to your screen first.

image

 

I have already created some variables in the screenshot above.  To create a variable, first decide on the scope of the variable, whether it is the entire package, a container or a specific task.  I normally scope all of my variables to the package, since you can’t see the variables in the window unless you have selected the scoped object.  I find it easier to manage variables having them all in one place.  I have not had any compelling reason not to do it this way, although I’m sure there are cases where you may want them scoped differently. 

Before creating the variable you must select the item for it’s scope. In this case select the canvas of the Control Flow which will permit you to scope the variable to the entire package.  Then create the variable by clicking on the Add Variable button at the top of the Variables window.

image

 

Give your variable a Name, a Data Type and a Value.  In this case we are working with EndDate, and although you would want to set it as Date, there are some compatibility issues when using the Date data type for a variable and then pulling into a SQL parameter.  I have found it is best to set the date as a string and then manipulate it from there.  Set your EndDate attributes as shown, with the Scope being the name of your package.

image

Populate an SSIS variable using a SQL statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow.  Double click on it and set the following properties:

On the General tab:

  1. Set the ResultSet to Single row
  2. Select your Connection to the database where you will run your SQL Statement
  3. Enter your SQL Statement

image

On the Result Set tab:

  1. In the Result Name key in the name of the field being return in your SQL Statement.  In this case I named the field EndDate
  2. In the Variable Name select the user variable you created.

image

Use the Variable in a SQL Statement

From the Toolbox, drag an Execute SQL Task onto your Control Flow. Double click on it and set the following properties:

On the General tab:

  1. Select your Connection to the database where you will run your SQL Statement
  2. Enter your SQL Statement, placing a ? where each variable is to be used.

image

In this case I need to add two Parameters to the Parameter Mapping tab, since I have used two question marks in my SQL Statement.  Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement.  This is the only way to identify which parameter is being used for which question mark.  Parameters are made much easier in 2012.

Here’s the tricky bit which isn’t at all self explanatory.

On the Parameter Mapping tab: 

  1. Select the user variable in the Variable Name drop down which corresponded to the first variable (?) being used in your SQL Statement.
  2. Set the Data Type to NVARCHAR, since we are using a string variable. (This is the spot where the data type compatibility didn’t mesh, which is why we opted for string).  
  3. Set the ParameterName to 0.  This indicates the order in which the parameter is appearing in the SQL statement.  You can enter them in the pane in any order you want, but the ParameterName must contain numbers starting from 0 to however many parameters you have minus one.  So if you have two parameters you must have one ParameterName = 0 and one ParameterName = 1.  You can reverse the order, but they must both exist or the task will fail.

image

47 thoughts on “How To Set and Use Variables in SSIS Execute SQL Task”

  3. Hi,
    Thanks for the information given above.Its useful.
    Can you please let me know when to use single row set and full row set in Execute SQL task ?

    1. Hi Karan, Single row set is when you only ever expect one row returned. Full row is when you expect multiple rows. Cheers.

  9. Is there a similar approach that would create a variable that holds a list of values? I need to hit one database to get a list of product codes (0-10 returned), and use that list as a filter in another query on another database (via a different connection manager), the resulting query uses an IN operator as the filter – eg. i want my code to show …
    prdct_cd in (@User::prod_ids)
    … and at runtime be able to generate …
    prdct_cd in (‘ABC123′,’ABH334′,’NBN667’).

    Any suggestions?

    1. You could try something like reading the list into an SSIS object variable, and then looping through that object to append each value to a string which you could use in your IN operator in the SQL statement.
      Cheers,
      Martina

      1. Hi Matrina,

        I am looking for similar kind.. Could you please provide me sample on how to create a variable that holds a list of values.

        Thanks,
        Vamsi

        1. HiVamsi,

          Instead of a string variable you need to create a variable of type Object to hold the multiple values. Then you need to set the [Execute SQL Task] to Result Set to “Full result set” instead of “Single Row”. On the Result Set tab, be sure to set your Result Name = 0.

          Cheers,
          Martina

          1. Have you actually tried this?
            I’ve done what you said and used this query in the sql task but it is still not working.

            SELECT ‘0’,’1′

  10. I am trying to set a variable value in Execute Sql Task but it is giving error like this

    [Execute SQL Task] Error: An error occurred while assigning a value to variable “RowCount”: “Exception from HRESULT: 0xC0015005”.

    I have Defined One Variable named as @RowCount as Int32

    In General Tab I have done following settings
    Result set : Single Row
    Connection Type : OLEDB
    SourceType : Direct Input

    SQL Statement

    UPDATE [dbo].[ONE] SET [COLUMN 2] = ‘aaa’;
    Go
    select @@ROWCOUNT as rtn
    Go

    and in
    Result set Tab
    Result Name rtn and Variable name User::RowCount

    But Still Getting an Error Please Help

    Thanks in Advance!!!

    1. Hi Azim,

      I would guess (I don’t know for sure) that the Execute SQL Task is unable to manage two statements to set the variable. It may be looking for the variable in the first statement (the update statement). Try just using a single select statement rather than including the update statement. You could run one Execute SQL statement to populate the variable “SELECT count([COLUMN 2]) as rtn FROM [ONE]”, and then run a second Execute SQL Statement to update the table.

      Cheers,
      Martina

  13. Is it possible to pass the variable from your SQL Task to a File System Task?

    How do you verify what is in your variable after the execution of the SQL Task? In other words how do you display what is in User::EndDate?

    I am trying to pass a variable (User::EndDate) to a File System Task which renames a file (example – C:Folder + “Filename” + @[User::EndDate].csv.

    Is this possible?

    Thanks,
    Sqlraider

  14. Thanks Martina… Your post and respose to others were both helpfull. keep posting more.

  16. Thanks.

    How can I use the variable multiple times. I have set a variable managerID = 10 and used in the following statement with both manager and training_officer = managerID

    insert into employee (Name, Manager, training_officer) values (‘Peter’, ?, ?)

    1. Even if you are using the same Variable multiple times, you still need to enter them separately on the Parameter Mapping tab, once for each time they appear in the SQL Statement. This is the only way to identify which parameter is being used for which question mark.

  17. This is really helpful except I do not see Variables or Log Events in the Other Windows submenu. I am running 2008. Is there an option that would turn that on?

    1. Hi Dennis, When you select the View menu, have you first selected the Control Flow or created a package to which you would like to add the variables?

  18. Many thanks Dear Martina
    for that useful and helpful document.
    I appriciate.
    best regards.

  24. Thank you so much for providing this information clearly and thoroughly. It’s still relevant and useful after the original post 2 years ago. Have a good one!

  25. guys…..could u please out and post how to call the stored procedures parameters in execute sql task
    send me the query…….for that

    1. Hi Dinesh, It works in exactly the same was as other sql statements. Instead of the parameter for your stored procedure, use the question mark. An example of executing a stored procedure with one parameter, the SQL statement would look like this: EXEC spTest ?

  27. Hi,

    I am trying to insert variables into my table. It seems like I did exactly what you recommended but it gives me error message. Could you take a look?

    I have 4 variables plus getdate(). all the variables are int 32. I filled the parameters as you recommended and fill the result set as you recommended, filled the general session as recommended.

    here are my SQL statement:

    insert into LOC_Summary_Table
    (Report_dt,LOC_A,LOC_B,LOC_C,ASSESS)

    select getdate(),?,?,?,?

    The error message is compile error.

    could you help?

    Many many thanks

    Jing

    1. Are the variables scoped to the package? What datatype did you use on the Parameter Mapping tab? The SQL statement looks fine.

  28. Thanks, Martina, for an excellent explanation. I am using SQL 2012. In what way is the process easier? Could you please give me the steps to 1) create a variable (Tables) and populate it with a list of table names, 2) create a second variable (CurrentTable) that takes a value from the Tables variable and 3) use the value in CurrentTable to name an output (text) file? I will use a Foreach Loop container to query each of the tables in Tables.

    Regards,
    Irene

    1. Hi Irene,

      1) Create an Object variable to hold the list of table names.
      Use an Execute SQL task to populate the variable: on the General tab set the Result Set to “Full Result Set”.
      On the Result tab map the Result Name “0” to the variable you created above.
      2) Create a string variable
      Add a Foreach Loop. On the Collection tab, set the Enumberator to “Foreach ADO Enumerator”. Set the ADO object source variable to your Object variable created in step 1.
      On the Variable Mappings tab, map the string variable to Index 0.
      3) In the properties of the Connection Manager for your output (text) file, select Expressions and set the Property “Name” to the string variable you created in step 2.

      Hope that helps give you some direction.
      Martina

  29. Hi Martina,

    My case is similar but I had two execute sql tasks. I had a variable “output”(data type object) which gets populated by EST-1. The data is a table. I need to pass this table as a input parameter to a Stored procedure , which is called in the EST-2.

    I am confused in setting the data type of variable in the parameter mapping tab of EST-2. Can you tell me what data type should we use for variable in parameter mapping with datatype Object.

  33. My search found this article when I was searching for “initial”, but there really isn’t any content about initialization.

    For example, if one leaves “Value” field un-populated, does that mean NULL, or empty string / zero, depending on type?

    If I want to set the initial value to NULL, do I have to create an expression and use one of the NULL() expressions? Or can I put something into the “value” field to set NULL? I’m guessing that entering the letters n-u-l-l for a string would set the string “null”, not the indicator NULL.

    Of course one can do experiments, but I think this is something a reference article should at least mention (don’t know if this one is ever updated).

Comments are closed.