THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Andy Leonard

Andy Leonard is an author and engineer who enjoys building and automating data integration solutions. Andy is co-host of the Data Driven podcast. Andy is no longer updating this blog. His current blog is AndyLeonard.blog.

SSIS Design Pattern - Dynamic SQL

This blog has moved! You can find this content at the following new location:

http://andyleonard.blog/2007/07/22/ssis-design-pattern-dynamic-sql/

Published Sunday, July 22, 2007 2:55 PM by andyleonard

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

test said:

rather confusion, no start, no ending, the script task won't run its own

July 30, 2007 2:43 PM
 

andyleonard said:

Hi test,

  This works fine for others. I will be happy to work with you if you are experiencing an error / issue with the code. Please email me at andy@andyleonardconsulting.com.

:{> Andy

July 31, 2007 5:00 AM
 

cenko2 said:

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?

August 20, 2007 4:22 PM
 

andyleonard said:

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

August 21, 2007 3:18 PM
 

Ed Per said:

It works fine!!!

Super chido!!!!

December 6, 2007 6:09 PM
 

Oscar(Colombia) said:

It works so fine... Thanks in advance.

February 25, 2008 5:16 PM
 

JKG said:

going into powershell as an argument it turns into a single quote????

May 2, 2008 10:40 AM
 

John Flowers said:

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'

June 2, 2008 12:51 PM
 

Shelley said:

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

May 15, 2009 10:27 AM
 

andyleonard said:

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

May 15, 2009 11:43 AM
 

PatLim said:

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.

July 8, 2009 11:26 AM
 

JimL said:

What do you do if your SQL Statement is over 4000 characters?

May 18, 2010 11:08 AM
 

andyleonard said:

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

May 18, 2010 11:24 AM
 

Tash said:

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

July 1, 2010 6:59 AM
 

Anarkus said:

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

September 18, 2010 8:22 AM
 

DanielBowlin said:

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.

June 13, 2011 11:33 AM
 

SSIS newb said:

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?

February 11, 2013 11:42 AM
 

andyleonard said:

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

February 11, 2013 11:52 AM
 

Saurabh said:

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

August 15, 2013 5:42 PM
 

andyleonard said:

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

August 15, 2013 9:28 PM
 

Rico Gabrielli said:

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

December 5, 2013 2:18 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

News

My Latest Book:

Community Awards



Friend of Red Gate

Contact Me

Archives

Privacy Statement