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?

Hadi · Accepted Answer · 2020-01-19 08:35:30Z

3

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.

edited Jan 19, 2020 at 8:35

answered Jan 18, 2020 at 22:31

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

donDT Over a year ago

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.

donDT Over a year ago

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!

Chris Albert · Accepted Answer · 2020-01-18 22:26:48Z

1

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%')"

answered Jan 18, 2020 at 22:26

Chris Albert

2,5158 gold badges29 silver badges34 bronze badges

1 Comment

donDT Over a year ago

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...

Collectives™ on Stack Overflow

SSIS: Variable from SQL to Data Flow Task

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related