Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: Using dynamic SQL in an OLE DB Source component

I reckon that one of the most commonly used components in SSIS is the OLE DB Source component. This is the one that you use to pick up data from an OLE DB compliant data source (e.g. a database) in order to use that data in your pipeline. This is done with a SQL statement.

One of the more common usage scenarios is to dynamically generate the SQL statement at runtime. For example you may have a WHERE clause in that SQL statement in which you want to use different values.

BOL probably states that the way to do this is use a parameterised SQL statement. Well...don't listen to BOL. Parameterised SQL statements are difficult to build and are subject to the vagaries of OLE DB Providers (see Kirk's post on the subject here: http://www.sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx).

The alternative is to build your SQL statement dynamically in a variable and use that in your OLE DB Source component. Here are the steps for the WHERE clause scenario mentioned above:

  1. Create a new variable called SourceSQL
  2. Open up the properties pane for SourceSQL variable (by pressing F4)
  3. Set EvaluateAsExpression=TRUE
  4. Set Expression="SELECT * FROM MyTable WHERE MyColumn = " + @[VariableContainingFilterValue]
  5. For your OLE DB Source component, open up the editor
  6. Set Data Access Mode="SQL Command from variable"
  7. Set VariableName = "SourceSQL"

So the only thing you have to do after that is find a way of changing @[VariableContainingFilterValue]. That's not within the scope of this post because there are many many ways that you could do this. I'll leave it to your imagination!

 

-Jamie

P.S. This technique works equally well on the SQLStatementSource property of an Execute SQL Task.

 

Published Friday, December 09, 2005 9:49 AM by jamie.thomson

Comments

 

Scott Barrett said:

Jamie,

I have found when using Variables in expressions, the one of the tricks is to set a default value for the variable before building the expression. Since the expression tries to evaluate immediately, not having a "starting" value for the variable can cause it to error on the initial evaluation.

Keep up the good work!
Scott Barrett
December 12, 2005 5:36 PM
 

Henrique said:

Hi Jamie,

I've read on the SQL Server Business Intelligence Development Studio Help that the DT_WSTR data type has a maximum lenght of 4000. As the variable "SourceSQL" type is String, it has only 4000 as max lenght. What can I do if my sql statement has more than 4000 characters? Does it work if I just use that data type? Do you have any other solution?

Thanks!
December 13, 2005 7:31 PM
 

jamie.thomson said:

Henrique,
I don't know. My best advice would be to "try it and find out".

If this IS a limitation then you should post it to Microsoft at: http://lab.msdn.microsoft.com/productfeedback/default.aspx

-Jamie

December 13, 2005 7:39 PM
 

Adrian Crawford said:

I couldn't get the parameterised SQL statement option to work with a DB2 OLE DB compliant data source. So I used this option, and although it works, it's unbelievably arduous to set up with a statement of any length....I had a select statement with about 1000 chars, and after taking out all carriage returns(wouldn't take it with them) so that I could paste it into the variable expression box I finally had it working.

Adrian
January 4, 2006 9:38 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 2:02 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 2:03 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 3:41 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 3:44 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 6:52 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 5, 2006 9:58 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 7, 2006 7:45 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 7, 2006 8:01 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 9, 2006 1:43 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
January 23, 2006 6:44 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
February 17, 2006 11:46 PM
 

ER Hunter said:

Jamie, first, thanks for having this out here, its a great resource. I'm an old school DTS developer trying to match the old to the new and this is a great help.

Ok, so you touch on something here I need more info about. Lets say I have a monthly fact table I need to create on the fly each month based on a package var for month id, example: fact_myfact_m72. I have the DDL for this table creation and the variable but I'm not sure how I could A) get the table created dynamically and B) get the ole db connector to accept this table (which will be the same layout month to month but a different name) sight unseen.

In earlier DTS, I would have two ways to do this, but typically I would use activex to seed a template string with the global variable for the month, then send that string of template code to a sql step, and then assign the datapumps destination table property to this new table name.

Doing this in SSIS will be a huge hurdle cleared for me, any thoughts?
February 21, 2006 4:24 PM
 

Jamie Thomson - Life, the universe and SSIS! said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices....
February 21, 2006 10:18 PM
 

John said:

This seems to work when only one variable needs to passed if I have to pass two variables, it is not getting updated. I tried using variable as "Exec storedproc " + @[Variable1] + ", " + @[Variable2]

Do you have a work around for this?
April 7, 2006 3:37 PM
 

jamie.thomson said:

John,
I can't repro the problem. Using 2 vars works OK for me.


-Jamie
April 7, 2006 3:48 PM
 

jamie.thomson said:

ER Hunter,
I have just noticed your post above. I never got notified that it was there (we used to have a problem with our auto notifications).

On the off-chance that you are still reading...In your situation you can use dynamic SQL in exactly the same way to set the SQLStatementSource of an Execute SQL Task.

-Jamie
April 7, 2006 3:50 PM
 

Raghu said:

Hi, I am Raghu Raman and I am new to this blog. I am stuck here in a situation where I have a package that has a task of copying data from server A.DBA to serverB.dbB. When I execute the package in design mode, it works great. When I create an Agent pointing to the package, It fails with the err

Date 5/1/2006 9:18:51 AM
Log Job History (Daily Routine)

Step ID 1
Server BHFINANCE02
Job Name Daily Routine
Step Name PackageExec
Duration 00:00:20
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0

Message
Executed as user: CORP\SQLservice. The package execution failed.  The step failed.

I totally could not understand this.

I am not sure if someone already asked this question. Please help

Thanks
Raghu Raman
May 1, 2006 5:45 PM
 

SSIS Junkie said:

I thought it would be worth publishing a list of guidelines that I see as SSIS development best practices.

November 8, 2006 9:52 PM
 

Mafti said:

Hey there, i try to get it working but i have some problem.

when i use a script-component  to show the variable in a msgbox it looks okm however when i use the variable in the ole-db-source component it "crashes". I don't know how to debug this...

January 24, 2007 10:16 AM
 

SmokedIronMade said:

I am maybe missunderstanding how the OLE DB Command works, but I have been trying to use it to run a stored procedure to update records in a temporary table, so once they get moved to production table they have the right data, but this seems don't have effect at all.

Is this possible to do?

-SmokedIronMade

April 4, 2007 3:37 PM
 

jamie.thomson said:

SmokedIronMade,

It sounds as thouhg you want to be using an Execute SQL Task to do this.

-Jamie

April 4, 2007 4:08 PM
 

SmokedIronMade said:

Jamie,

Thanks it worked for me.

-SmokedIronMade

April 4, 2007 4:39 PM
 

yacir said:

Hi, i m new to SSIS. I have a question. I really appreciate if any one helps me.

Actually i need to modify OLEDBCommand's SQLStatement property inside a scriptComponent . How can i reference other Components inside ScriptComponent to modify their properties at runtime.

April 24, 2007 11:35 PM
 

jamie.thomson said:

Hi yacir,

Simple answer. You can't.

-Jamie

April 25, 2007 12:19 AM
 

yacir said:

oh REALLY!!...

anywayz thanks Jamie for the answer .

May 1, 2007 11:19 PM
 

jamie.thomson said:

Yacir,

Its very possible there are other ways to achieve what you are trying to achieve. Try posting your problem on the SSIS forum.

-Jamie

May 1, 2007 11:36 PM
 

roy ashbrook said:

I’m wanting to compile a basic best practices list for SSIS. I don’t really need to get into

May 5, 2007 2:42 AM
 

Dotnet Fellow said:

Hi Jamie,

Are there some guidelines anywhere on when to use and when not to use dynamic ad-hoc embedded sql selects within an Ole Db Source component in Ssis, vice when to execute a Sql Server database engine stored procedure for selecting data back into the Ssis package?

Thanks.  Sincerely, -Dotnet Fellow

June 28, 2007 5:08 PM
 

jamie.thomson said:

dotnet fellow,

I guess the answer is - "whatever works for you". There are no guidelines per se.

If you are considering using sprocs then definately read this: http://blogs.conchango.com/jamiethomson/archive/2006/12/20/SSIS_3A00_-Using-stored-procedures-inside-an-OLE-DB-Source-component.aspx

-Jamie

June 28, 2007 5:39 PM
 

d-cpt said:

Dear Jamie,

I tried to use your method to get data from an Oracle DB to populate in a SQL Server DB 2005  but get this error:

[OLE DB Source [247]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E14. An OLE DB record is available.  Source: "OraOLEDB"  Hresult: 0x80040E14  Description: "ORA-00936: missing expression".

Query is: "SELECT DISTINCT StuID FROM Students WHERE SEM =" + @[vSem]

The connection uses Oracle Provider for OLE DB

vSem is the variable.

I also tried MS OLE DB Provider for Oracle but get the same error.

Thank you for your helps,

d-cpt

July 24, 2007 4:23 PM
 

d-cpt said:

when I checked the variable value, it shows

SELECT DISTINCT StuID FROM Students WHERE SEM = SELECT DISTINCT StuID FROM Students WHERE SEM ='123'

vSem has default value as '123'

I don't know why it replaces the default value with a whole query.

May you give me some hints.

Regards,

d-cpt

July 24, 2007 4:57 PM
 

jamie.thomson said:

My best guess is that the variable is referencing itself. It can't do that.

I've asked Microsoft to put checks into the designer to stop us from being able to build self-referencing variable expressions. They said they'll think about it.

-Jamie

July 24, 2007 5:04 PM
 

d-cpt said:

Thank you, Jamie.

d-cpt

July 24, 2007 6:57 PM
 

SSIS-abc said:

I wanted to ask that how should i go ahead if i want to put a filter for month_oa_date entry in my table?

I have tried the first option given by Jamie of dynamic SQL....but i guess its not working for me...please help me out....

October 18, 2007 12:24 PM
 

sonal said:

it is simply great :)

October 25, 2007 2:18 PM
 

SSIS-abc said:

Please tell me what can I do for it?

October 29, 2007 7:51 AM
 

Mårten Gustafsson said:

I want to create a query containing a DateTime variable, but then the expression type fails. It works for string variables. What kind of functions (for type conversion) can you call in an Expression?

January 29, 2008 4:26 PM
 

jamie.thomson said:

Hi

An error message would be useful.

-Jamie

January 29, 2008 4:43 PM
 

Sanjay said:

Is there any equivalent of the TSQL CHAR(n) function, or {CR}{LF}, that could be used in a transform expression? I may be missing the point, however, I have reached a dead end.

Thanks

February 19, 2008 11:05 AM
 

jamie.thomson said:

Sanjay,

Check the 'escape characters' section here: http://msdn2.microsoft.com/en-us/library/ms141001.aspx

-Jamie

February 19, 2008 3:02 PM
 

RJ said:

Hello guys, i want to copy data from different tables so, the my source oledb query would be select * from tabl1, select * from table2. Both the tables1, table2 have different columns. And my destination is flat file. I tried constructing a variable for the sql string and dynamically creating fiat files. But it doesnt work. How can i do this ?

Any help appreciated.

Thanks

March 17, 2008 8:21 PM
 

jamie.thomson said:

RJ,

Join them or Union them together using SQL's INNER JOIN or UNION ALL statements.

-Jamie

March 17, 2008 8:32 PM
 

Bibhuti Thakuria said:

It was quite useful lesson for me using which I could solve many problems.

Thanks a lot.

Bibhuti Thakuria,Boston,USA

April 2, 2008 9:07 PM
 

Robin Wesley said:

I am new to SSIS. My scenario demands me to do an incremental load. So i have followed your article and have created a one variable 'vDate' (Dataflow Task, Datetime, 3/3/2008) and another variable SourceSQL (Dataflow Task, String). I have set this SourceSQL evaluation to True and have added this;

"select * from stagingperfdata where startdatetime = "+ (DT_WSTR,10) @[vDate].

I want to call this within a OLE DB source, so i select the data access method to SQL command from variable. My variable name is visible and i am able to select it and then save and debug. I also have a derived column transformation to increment the variable vDate by one day.

The flow is successful but there is no records written to the target.

I tried the same query (available in the variable's value column) in SSMS and it is retrieving 12 records.

I went back to the Ole Db source and wanted to preview the data, but i get "Query timeout expired (Microsoft SQL Native Client)" error.

Am i missing something.

April 10, 2008 3:26 PM
 

jamie.thomson said:

Robin,

Does the expression for 'SourceSQL' evaluate to the SQL statment that you want?

Use SQL Profiler to check that the correct SQL is getting executed.

-Jamie

April 10, 2008 3:32 PM
 

Robin Wesley said:

The expression for my variable 'SourceSQL' does NOT evaluate to my need. The initial value  (3/3/2008) i passed to my other variable (vDate) is visible. I have pasted below that query.

select * from stagingdumpdata where startdatetime = 3/3/2008

It should have evaluated to

select * from stagingdumpfdata where startdatetime = '3/3/2008'

Am i missing something here.

Thanks. Robin

April 11, 2008 7:15 AM
 

Robin Wesley said:

Played around with the Expression editor and got my package to work when my query was like this.

"select * from stagingperfdata where startdatetime =" + " ' " +  (DT_wSTR,10) @[vDate]  + " ' "

Thanks Jamie.

Any thoughts on assigning the incremental date (which i get from the derived colum transformation) to the variable. Do we have something like "SETMAXVARIABLE" function of Informatica.

Thanks again

April 11, 2008 8:08 AM
 

jamie.thomson said:

Hi Robin,

You can use the script component to write to a variable from within the pipeline. is this what you want to do though given that the value isn't scoped to the data flow, there is a value for each ROW.

Not quite sure what you're trying to achieve here.

-Jamie

April 14, 2008 1:53 PM
 

Justin B said:

Anybody else think that it's ridiculous to go through so much effort for a dynamic query?  I mean, I had to set my query variable to a dummy query with the dynamic values set statically just to get the metadata to map, then use a VB.NET script task to create the query based upon dynamic variables.

I mean, it's just obsurd to me that clicking the "parameters" button doesn't parse my more complicated query.  Took me 4 hours to do a package that should've taken 15 minutes.  Just had to vent.   -Justin

April 24, 2008 6:06 PM
 

Isondart said:

I wanted to adjust a dynamic process, which imports data from excel into SQL, by only retrieving Non-Null values. Your example gave me what I was looking for. I had to make one adjustment to get the result I wanted.

I ended up placing brackets just inside the quotes:

Ie: "SELECT * FROM ["  +@[User::anExcelSpreadsheet] +"]  WHERE [a specific column name] IS NOT NULL

and I get the non-null values imported into SQL.

thanks Jamie!

-- Isondart

July 8, 2008 7:35 PM
 

FR said:

I'm kind of new to SSIS - this info was a tremendous help!  

thanks!

November 12, 2008 5:47 PM
 

Gaurav said:

Hi ,

I am totally new to SSIS jus 2 weeks old.

I have build a task to get records from table where the table name is like XXX_Email.

and XXX is some prefix  which is there is some other table so i jus added one more column in my prefix table  to get table name records as prefix+_Email.

so i need to pass these table names to my query variable @[User::TableName]

can anyone help how to pass this value to my tablename variable .

November 25, 2008 9:26 AM
 

Syntax | keyongtech said:

January 18, 2009 5:07 PM
 

kumsenthil said:

Hi,

I have created one variable called Effectivedate then i set Evaluate Expression as False and assigned default value '20/03/2009' type as string

then i have created another variable Preparesql and i have set evaluate expression as True then assigned the following query to that expression

SELECT POSITION.SECURITY_ALIAS,POSITION.DEP_ACQ_DATE,POSITION1.ORIG_FACE FROM (SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS ,CONVERT(VARCHAR(8),LLP.department_acquisition_date,112)

AS DEP_ACQ_DATE FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP

WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias

AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='DD') AND

CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) =("+ @[User::Effectivedate] + ") ) POSITION LEFT JOIN ( SELECT LLP.SECURITY_ALIAS AS SECURITY_ALIAS, LLP.ORIG_FACE FROM HOLDING.DBO.POSITION P, HOLDING.DBO.POSITION_DETAIL PD, HOLDING.DBO.LOT_LEVEL_POSITION LLP WHERE P.POSITION_ID = PD.POSITION_ID AND P.POSITION_ID = LLP.POSITION_ID AND PD.security_alias = LLP.security_alias AND P.SRC_INTFC_INST =(SELECT INSTANCE FROM PACE_MASTER.DBO.INTERFACES WHERE SHORT_DESC='BLACKROCK')AND CONVERT(VARCHAR(8),P.EFFECTIVE_DATE,112) =("+ @[User::Effectivedate] + "))POSITION1 ON POSITION1.SECURITY_ALIAS = POSITION.SECURITY_ALIAS

then i created the OLEBE command and used sql command from variable option.

Data flow task is executing successfully but it is not pulling any record.

can someone guide what could be the problem?

Regards

Senthil

July 8, 2009 5:41 AM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems