Wednesday 17 February 2010

Use a variable as a table name in an SSIS Execute SQL Task

I spent quite a while today trying to build an SSIS package that iterated through a dataset that contained a set of table names usign a for-each container, then run a piece of SQL against each table.

Setting up the for-each loop was relatively simple, but I couldn't get the Execute SQL task to run a simple query of the type:
Select count(*) from @table
Where @table is the name of the parameter generated from the for each loop.

It appears that this is not possible in SSIS.  Eventually I found a solution:  use another variable to store the SQL statement and set up the SQL task to use this variable as its input source.

Assuming that the input parameter is already set up and called [User::TableName],  then the steps to achive this are:

  1. Set up a new variable (say called "SQLStatement"), make this a string type.
  2. In the properties box of the paremeter, Set EvaluateAsExpression to True
  3. In the properties box of the paremeter, click on the elipses (...) of the "Expression" property to build an expression.  Type in the SQL you need and drag the variable you want to use from the "variables" tree.
  4. Escape you SQL in this expression wwithin quotation marks.  e.g. the count statement above would look like:
  5. "Select count(*) from " + @[User:TableName] 
  6.  You can use the "Evaluate expression" button to make sure that this evaluates as real SQL.  If you are using a "for-each" container then you may want to give your [User::TableName] variable a sensible default value in order to see the results of the evaluated expression in a format you could use for testing.
  7. Drag an "Execute SQL Task" object into your control flow and open its properties page
  8. Set the "SQL Source Type" property to "Variable" to tell it that the SQL will come from a variable, rather from a typed in SQL statement.
  9. Set the "SourceVariable" property to the name of the variable you created in step 1 (in this example it would be "SQLStatement".
That's it!  

    3 comments:

    1. Daniel dani_shields@hotmail.com23 March 2012 at 13:41

      Hola Iain!
      I have the same problem and since Ive just migrated from sql2000 to 2008 Im having a harsh time following your steps.

      I need to copy 2 or 3 tables. The names vary everytime so: 1st thing I run I do is store the names into variables (I guess the variables are global, I created them from the left panel, scope:package).
      And after this "SQL task" the process goes to a Data Flow where I want to copy/transform the tables from Access to SQL.

      My question is, How do I tell the ADONET (access) dialog to look the name of the tables from the variables.

      Any help will be much appreciated.

      Thanks.

      ReplyDelete
    2. Hi Daniel, I'm pretty sure I did this in SQL 2008. The "EvaluateAsExpression" property is important so make sure that is set, and make sute that your data flow taks works properly if you give it a hard coded SQL statement.

      Once you know it works properly with a hard coded bit of SQL, then you need to set up a loop to populate your variable, so you need to populate a table somewhere with the names of the tables you want, then loop through that (which I have not explained but is standard SSIS so you will find lots of examples).

      ReplyDelete
    3. I'm suffering with this too. I'm grateful for this explanation, but I don't understand why we can't simply map a parameter to a variable, use the parameter to represent the table name, and be done with it! Grrr....

      ReplyDelete