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!!!
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
9 People are following this question.
