March 4, 2011 at 5:39 pm
Greetings all,
So I have a table ETL that I am using SSIS to manage. In the data Flow I am using an OLEDB source to an AS400 (though that information is not too pertinent to the situation). Basically I have a script default that I use to extract only the information from the source that I want. Additionally I want to add a where clause that is dynamic based on the max timestamp of the table in the destination (a simple select max(timestamp) from table to fill in a variable is all I really need). So basically all I need is; select column1, column2, column3 from source table where timestamp > @variable
Obviously there is a way to do this since there is a "parameters" button in the OLE DB Source editor window when you select SQL Command from the source type, yet I am unable to figure out how to add this in!
Any help is greatly appreciated.
Link to my blog http://notyelf.com/
March 7, 2011 at 5:16 am
The first step you have to take is determine what the maximum timestamp is. You do this in a seperate Execute SQL Task and you store the result in a variable (if you don't know how to do this, let me know). Let's call this variable @MaxTimestamp.
Then you construct the SQL statement. Normally, you can do this directly in the OLE DB source, but I do not know how the OLE DB provider for AS400 takes parameters, so I'' use an alternative approach.
Create a SSIS string variable called @SQLStatement. Set the property EvaluateAsExpression to true and go to the expression editor. There you type the SQL statement you described earlier:
"select column1, column2, column3 from source table where timestamp > " + @MaxTimestamp
You can drag in the MaxTimestamp variable from the variables dropdown, so you don't have to type it yourself.
In the OLE DB Source, choose the SQLStatement variable as source for the SQL statement.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply