question

siera_gld avatar image
siera_gld asked

SSIS - PASSING VARIABLES IN AS ORACLE DATE

I am trying to use a date populated in an ssis package inside of a query to pull from an oracle table. My script will be a variable called in the ole db source so in this variable - called MyOraclePull I have this in the where clause that is throwing the error... Where sls.SLS__DT between ' " + (DT_WSTR, 10) @[User::BegDt] + " ' and ' " + (DT_WSTR, 10) @[User::EndDt] + " ' I believe that the dates are causing syntactical errors which prevent successful progress when the expression is evaluated. I also think the WSTR needs to be cast in oracle with a TO_DATE...... YYYY-MM-DD I am currently getting a truncation error message when trynigto evaluate in the expression builder Please assist!!!
ssisoracledatesvariables
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Oleg avatar image
Oleg answered
How does your variable look when you inspect it? In case if (DT\_WSTR, 10) @[User::BegDt] part produces the expected format (YYYY-MM-DD), the problem could be that you need an Oracle syntax, not T-SQL. If not then the problem could be caused by the extra space that you have after the word between, you have an extra space between single and double quote, it must be removed, so between ' " should be between '" The same applies to all single quote/double quote combinations in your snippet, they all have an extra space between them. If you need T-SQL flavour please try this: "Where sls.SLS__DT between '" + (DT_WSTR, 10) @[User::BegDt] + "' and '" + (DT_WSTR, 10) @[User::EndDt] + "'" If you need Oracle flavour please try this: "Where sls.SLS__DT between to_date('" + (DT_WSTR, 10) @[User::BegDt] + "', 'YYYY-MM-DD') and to_date('" + (DT_WSTR, 10) @[User::EndDt] + "', 'YYYY-MM-DD')" Oleg
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

thank you - another thing to add - I was pulling my dates as date time - i changed that to convert as string and that helped resolve
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.