I am new to SSIS. I want to be able to write a dynamic SQL statement for a Data Flow task. It would go something like this. I want to schedule an SSIS package to run everynight and extract data from an ODBC source and collect the previous day's sales info based on a date parameter, so the date parameter of the query would have to get a value based on the previous day's date
EX: SELECT * FROM Sales WHERE Invoice_Date = <previous day's date>
So far I am looking at a Script Component to do this and populate the Data Source, but I just wanted to check and make sure there isn't a better or more efficient way.
The previous day could be a simple getdate()-1 in your query however assuming you need something more complex, use the following type of things.
1) Create a variable, i.e. Qtr, String
2) Create a execute sql task or a script task to set the variable value. i.e. for SQL task something like, select cast(c.Current_Qrtr_yyyyqq as nchar(6)) as Qtr from dbo.Constants c with(nolock) and use the result set to pass the value to the variable Result set = Qtr, Variable name = User::Qtr
3) Edit the DataFlow task property, Add an expression for the SQL Command, something like your SQL between double quotes with the variable where you need it, i.e. "SELECT cast(Sum( Case when DECODE ( SIGN( NVL(B.RSD_DT,B.MSD_DT) - (Sysdate-1)), -1, 0,
Decode(B.RSD_YYYYQQ_NUM,0,B.MSD_YYYYQQ_Num, B.RSD_YYYYQQ_Num) ) = " + @.[User::Qtr] +"
then B.NET_AMT else 0 end) as varchar(50)) Current_Qtr_Net_Amt from dataware.mytable B"
Remember that you have to generate all the column mappings before implementing the expression. Also, when you build the package, the original query in the data flow get stripped out of the hard coded values that your expressions replaces, so if you want to change your metadata, you have to manually put back the values in the dataflow source item.
Hope it helps,
Philippe
-Jamie
No comments:
Post a Comment