SSIS: Variable from SQL to Data Flow Task

Question

Pretty new to BI and SQL in general, but a few months ago I didn't even know what a model is and now here I am...trying to build a package that runs daily.

Currently running this is Excel via PowerQuery but because the data is so much, I have to manually change the query every month. Decided to move it into SSIS.

Required outcome: Pull the last date in my Database and use it as a variable in the model (as I have millions of rows, I only want to load lines with dates greater than what I have in my table already).

Here is my Execute SQL Task:

I set up a variable for the SQL query

and trying to use it in my OLE DB query like this

Execute SQL Task: results, are fine - returns date as "dd/mm/yyyy hh24:mi:ss"

SELECT MAX (CONVACCT_CREATE_DATE) AS Expr1 FROM GOMSDailySales

Variable for OLE DB SQL Query:

"SELECT fin_booking_code, FIN_DEPT_CODE, FIN_ACCT_NO, FIN_PROD_CODE, FIN_PROG_CODE, FIN_OPEN_CODE, DEBIT_AMT, CREDIT_AMT, CURRENCY_CODE, PART_NO, FIN_DOC_NO, CREATE_DATE
FROM cuown.converted_accounts
WHERE (CREATE_DATE > TO_DATE(@[User::GetMaxDate],'yyyy/mm/dd hh24:mi:ss'))
AND (FIN_ACCT_NO LIKE '1%')"

Currently getting missing expression error, if I add " ' " to my @[User::GetMaxDate], I get a year must be between 0 and xxxx error.

What am I doing wrong / is there a cleaner way to get this done?

Farthermost

Answer 1

In the OLEDB source use the following, change the data access mode to SQL command, and use the following command:

SELECT fin_booking_code, FIN_DEPT_CODE, FIN_ACCT_NO, FIN_PROD_CODE, FIN_PROG_CODE, FIN_OPEN_CODE, DEBIT_AMT, CREDIT_AMT, CURRENCY_CODE, PART_NO, FIN_DOC_NO, CREATE_DATE
FROM cuown.converted_accounts
WHERE (CREATE_DATE > TO_DATE(?,'yyyy/mm/dd hh24:mi:ss'))
AND (FIN_ACCT_NO LIKE '1%')

And click on the parameters button and map @[User::GetMaxDate] to the first parameter.

For more information, check the following answer: Parameterized OLEDB source query

Alternative method

If parameters are not supported in the OLE DB provider you are using, create a variable of type string and evaluate this variable as the following expression:

"SELECT fin_booking_code, FIN_DEPT_CODE, FIN_ACCT_NO, FIN_PROD_CODE, FIN_PROG_CODE, FIN_OPEN_CODE, DEBIT_AMT, CREDIT_AMT, CURRENCY_CODE, PART_NO, FIN_DOC_NO, CREATE_DATE
FROM cuown.converted_accounts
WHERE CREATE_DATE > TO_DATE('" + (DT_WSTR, 50)@[User::GetMaxDate] +
"' ,'yyyy/mm/dd hh24:mi:ss') AND FIN_ACCT_NO LIKE '1%'"

Then from the OLE DB source, change the data access mode the SQL Command from variable and select the string variable you created.

Mages

Need to use Alternative as parameters are not supported... But I run into "ORA-00933 SQL Command Not Properly Ended." errors when I try it like this. – Farthermost

At first glance it seems to work (completes with error, but it works like intended - if I run it again, it doesn't load anything). I will check my SSAS Model on Monday and come back if there are any issues. Thanks man! – Farthermost

Answer 2

Your trying to use the SSIS variable like a variable in the query. When constructing a SQL query in a string variable you simply need to concatenate the strings together. The expression for your query string variable should look like this.

"SELECT fin_booking_code, FIN_DEPT_CODE, FIN_ACCT_NO, FIN_PROD_CODE, FIN_PROG_CODE, FIN_OPEN_CODE, DEBIT_AMT, CREDIT_AMT, CURRENCY_CODE, PART_NO, FIN_DOC_NO, CREATE_DATE
FROM cuown.converted_accounts
WHERE CREATE_DATE > " + @[User::GetMaxDate] +
"AND (FIN_ACCT_NO LIKE '1%')"

Problem

Firstly, thank you for the post edit. Secondly, I get the following error: ORA-00933 SQL Command Not Properly Ended. Could this be because there are no " ' " wrapped around my variable? I usually get this in Toad when I forget them... – Farthermost