Aug 292012
 

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

  33 Responses to “How To Set and Use Variables in SSIS Execute SQL Task”

  1. Thanks, great stuff

  2. Thanks, your help!

  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 ?

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

  4. Good stuff. Thanks.

  5. Nice write up.It was exactly what I was looking for. Thank you, Martina!

  6. Thanks a lot for this blog. It is really helpful.

  7. Thanks!

  8. Thanks Data Queen! This told me what I needed to know.

  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?

    • 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

      • 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

        • 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

  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!!!

    • 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

  11. Thanks. Really helped me get this task done quickly

  12. Thank you, good example

  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.

  15. Thanks. Really helped me get this task done quickly

  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’, ?, ?)

    • 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?

    • 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.

  19. Very concise and to-the-point. Was exactly what I needed with no extra frills. Thanks.

  20. How to create variable with numeric datatype in excute sql task
    ?

  21. Simple but helpful, thanks.

  22. Thanks a lot this article is really helpful :)

Leave a Reply

Wordpress SEO Plugin by SEOPressor
%d bloggers like this: