SSIS Design Pattern – Dynamic SQL

Introduction

I sometimes miss the ActiveX task in DTS. It was cool because “if you could see it, you could change it.” I used the ActiveX Task to generate dynamic SQL.

When To Use Dynamic SQL In SSIS

There are lots of uses for dynamic SQL in SSIS. If you find yourself using the same SQL statements over and over again with minor modifications – a different table name here, a different criteria value there, etc. – the statement is a likely candidate for dynamic SQL.

The flexibility of dynamic SQL coupled with the flexibility of SSIS allow for some very creative solutions. You can generate SQL based on environmental conditions – whether or not a directory exists; or using a Loop Container, set numeric criteria based on the value of the iterator; or in a ForEach Loop Container you can acces and include filenames in dynamically-generated SQL. The possibilties are limited only by our experience and knowledge of SSIS.

Variable On Variable Technology!

One of the best methods for generating dynamic SQL in SSIS is to use variables and utilize the EvaluateAsExpression property to update the value of the variable containing the dynamic SQL. This SSIS Design Pattern demonstrates how to generate dynamic SQL using the EvaluateAsExpression property of variables. 

To start, open Business Intelligence Developer Studio (or Visual Studio – they’re the same). Create a new Integration Services project. Right-click any whitespace on the Control Flow canvas and click variables:

When the Variables dialog displays, click the Add Variable button on the Variables menu bar:

Name the variable sSQL and set the Data Type to String. Set the value to “Select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress FROM Person.Contact WHERE ContactID = 0”. sSQL is our template variable – we will use this initial value (that returns no rows) as the basis of our dynamic SQL.

Note: In this example I only configure and display the dynamic SQL – I do not assign it to an ExecuteSQL (or some other) Task. But if I did, the SQL would have to parse or the task would throw a validation error. There are two ways around this error:

  1. Use a valid statement in the template SQL.
  2. Set the DelayValidation property of the ExecuteSQL Task to True.

Create another variable of Int32 Data Type named iContactID. iContactID will contain the dynamic portion of outr SQL statement. Set iContactID’s Value to 11:

Click on sSQL and press the F4 key to view the properties for sSQL. Set EvaluateAsExpression to True.

Click Expression, then click the ellipsis. In the Expression Builder, enter “Select ContactID, NameStyle, Title, FirstName, MiddleName, LastName, Suffix, EmailAddress FROM Person.Contact WHERE ContactID = ” + (DT_STR, 4, 1252)@[User::iContactID].

We are building a string variable, but iContactID is an Int32 data type. To concatenate it to a string, we need to cast the integer to a string.

In SSIS Expression Language, casting is accomplished using Type Casts. Drag (or type) the cast function just in front of the variable, i.e. (DT_STR, 4, 1252) @[User::iContactID]. All together it will look like the following:

Click the Evaluate Expression button to test. Your SQL statement should display in the two-line label above the button. Then click Ok to exit the Expression Builder.

A note about building the expression: I am sometimes tempted to use the REPLACE string function in Expression Builder to build dynamic SQL for variables. This usually turns out to be a bad idea because the value of the variable itself is updated when it’s evaluated as an expression. This means if I have a variable named sSQL with an initial value like “SELECT <ReplaceMe> FROM Person.Contact” and I use an expression like REPLACE(@sSQL, “<ReplaceMe>”, “ContactID”) it will work just fine on the first pass, but will fail to update the variable value on subsequent passes because the Search Phrase is no longer contained in the value of @sSQL – it was updated when the variable was evaluated as an expression.

Add a Script Task to the Control Flow and name it “Popup sSQL”. On the Script screen add sSQL to the ReadOnlyVariables list:

Click the Design Script button and add a MsgBox to popup the value of sSQL:

Close the Script Task. Right-click the Script Task and click Execute Task:

The value of sSQL should popup in a message box – with the value for iContactID displayed in the WHERE clause:

Taking It To The Street 

Although I do not demonstrate it in this post, you would use something similar to this example in a Loop Container. If you use iContactID as the iterator of the loop, you could start at some value and advance to some other value, executing dynamic SQL inside the loop on each pass. 

Conclusion 

I hope this helps you design dynamic SQL in SSIS. If you still need the advanced functionality of a script you can use the Script Task.

Download the code (Registration required)!

:{> Andy

 PS – The PASS Summit 2015 Call for Speakers closes at 9:00 PM PDT Sunday, 15 Mar 2015 (04:00 GMT 16 Mar 2015). There’s still time, but hurry!

Learn more: 
Linchpin People Blog: SSIS 
Stairway to Biml 
Stairway to Integration Services 

SSIS2014DesignPatterns200

Andy Leonard

andyleonard.blog

Christian, husband, dad, grandpa, Data Philosopher, Data Engineer, Azure Data Factory, SSIS guy, and farmer. I was cloud before cloud was cool. :{>

21 thoughts on “SSIS Design Pattern – Dynamic SQL

  1. I am using a DataReader Source to extract data from a Postgres table. I am trying to use the Expression Builder to build the dynamic SQL. The issue is that in Postgres, columns and tables should be in double quotes (") in the query:
    Select "columnA", "columnB" from "owner"."tablename" where "columnC" = 0. How can I build the dynamic query with these extra quotes in Expression Builder?

  2. Hi Cenko2,
      You need to use literals to construct this type of query. The \" literal will print (and not evaluate) quotation makrs. To use your example, "Select \"columnA\", \"columnB\" from \"owner\".\"tablename\" where \"columnC\" = 0" would produce the desired results.
      The best way I’ve found to do this is to write the query as I’d like it to appear, then copy it and paste it into notepad. I then replace all the quotation marks (") with slash and quotation marks (\"). If you then copy this and paste it into the expression builder, you merely need to nest it inside opening and closing quotation marks.
    Hope this helps,
    Andy

  3. Hi – great article – I am always confused about how to get single quotes inside Expression Builder when building my SQL stmts – any help is greatly appreciated –
    "SELECT lcd_id FROM USCourtDistricts WHERE mf_abbrev = ‘"  + UPPER(@[User::district_abbrev]) + "’"
    I need this to eval to –
    SELECT lcd_id FROM USCourtDistricts WHERE mf_abbrev = ‘azx’

  4. Hi Andy,
    Thanks for this useful article.  Would you know of a way of reading in SQL statements from a table and executing these within an SSIS package.  
    I would like to place a number of business validation rules within a table, and read and execute these rules to validate rows of data we will be loading into a Staging and Data Warehouse.
    Any suggestions.
    Thanks

  5. Hi Shelley,
      Thanks for your kind words.
      Yes, you can store SQL statements in a table and execute them using SSIS. One approach is to use an Execute SQL Task to retrieve the statements in the order you wish them executed, and then shred through that list using a ForEach Loop Container. I wrote more about that at http://www.sqlservercentral.com/articles/SSIS/64014/.
    :{> Andy

  6. Thanks for your script.
    i had issues with a In Clause in a execute Sql task
    row In (‘text1′,’text2′,’text3’) (in parameter or not)
    so i tried to create all the sql in a expression and it’s working.

  7. Excellent question Jim!
      If the length of the SQL Statement exceeds the limit for characters in the SQLStatement property of the Execute SQL Task, I write the statement to a file. Then, in the Execute SQL Task, you can set up a File Connection source aimed at the file location.
    :{> Andy

  8. I need to subtract today’s date by 1. I’m not sure how to cast the variable so that I can use operators on it.  So far only
    (DT_WSTR, 40) getdate() works but I need to substract by one. I’m using the expression builder as a subject line in my Send Mail Task box in SSIS.
    Thanks

  9. New to, and frustrated by, SSIS and new framework within my organization (e.g. no links)
    This was exactly the information I needed to solve a problem of aquiring data from a very large table on another instance database associated many (~E3) to 1 to a selected population (E3 out of E6) in my instance.
    Applied within ‘foreach’ driven by object created from population.
    I will continue to follow your articles in the hope of making this tool work for me.
    If only I could figure out how to use it without the GUI …

  10. I have a package that uses this technique in 2 layers.  The first is a creation of a simple date value such as 20110613.  The next layer uses that value in a dynamic sql statement to copy the contents of a table to a date named version of the table.  The variables always shows the correct values when I look at them.  I then use the variable in an Execute SQL task to create the new table.  I have 5 tables I do this with.  Invariably 1 of the five Exec SQL tasks will fail and the error is that the table already exists but the date variable in the SQL Statement is the date from the last time the package ran.  Typically it is a different task that fails from week to week.  Why would this happen?  Is there some cache that needs to be cleared before running these statements?  I am still running these in BIDS.  Thanks.

  11. This is a great example how to build the sql.  How would I invoke a SQL function to return a value from SQL into a package variable?

  12. Hi SSIS Newb,
      If you set the Execute SQL Task SQLSourceType property to Variable, you can select the SSIS variable from the SourceVariable property.
    Hope this helps,
    Andy

  13. Hi andyleonard,
    In my case I have a very huge query, when I click on EVALUATE EXPRESSION ssis throws an error message that expression is too big and maximum allowed length is 4000 characters. Is there any alternate solution.
    Thanks!!

  14. Hi Saurabh,
      Yep, you can use a Script Task to generate the query and save it to a file. Then you can configure an Execute SQL Task’s SQLSourceType property to File. After that, you configure the File Connection property to use a <New Connection>, and aim the File Connection Manager at the file created by the Script Task. It will take a little .Net coding, but I’ve driven T-SQL as large as 4M that way.
    Hope this helps,
    Andy

  15. Hi AndyLeonard,
    I have a SSIS Package that retrieves some tables from a SQL Server database and transfer it to a DB2/400 database.
    However, there are two tables that I will need to handle a bit differently:
    There will be certain fields, which I should use in case it is an “INSERT”, but if it’s an “UPDATE”, few fields of that table should not be updated.
    There’s a table indicating to the SSIS Package which fields should not be updated.
    Unfortunately I was not able to figure out a way how to do that. Do you have any advice for it?
    I’m looking forward for your thoughts.
    Thanks in advance, Rico

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.