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?

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.