Post reply

Parameterizing extraction from an ODBC data source (NetSuite's Oracle dbms)

  • February 16, 2016 at 7:41 pm

    #308638

    Caveat 1) I've very modest experience with SSIS. I can build a fairly simple package.

    Caveat 2) I thought I was attempting to build a fairly simple package.

    Goal: Customer Package extracts everything from Customer table where DATE_LAST_MODIFIED is within X days back. X days is the ONLY parameter/variable.

    I cannot pass the parameter. I can create the SQL Command

    (SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - 5), I can create both a "package parameter"(Lookback) and variable (LookbackCount). Nowhere can I find any info on how to use the parameter in place of the 5!!! :angry::angry::exclamation::exclamation::exclamation::exclamation::exclamation:

    I have tried, unsuccessfully, the following variations.

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - ?Lookback)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - @?Lookback)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - ?@Lookback)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - @Lookback)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - ?LookbackCount)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - @?LookbackCount)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - ?@LookbackCount)[/I]

    SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - @LookbackCount)[/I]

    It strikes me as unlikely that an extraction QUERY cannot be given a simple parameter, so what am I missing?

  • February 17, 2016 at 4:53 am

    #1859219

    I don't believe ODBC Source in SSIS allows parameter mapping. You can dynamically create the SQL command using the expression editor on the [ODBC Source].[SqlCommand] property of the Data Flow Task (i.e. you need to go to the Control Flow tab and select the DF Task).

    (Also note you can't edit that property directly in the Properties window, you select the Expressions property and use the dialog that appears.)

    The expression should look something like:

    "SELECT * FROM CUSTOMERS WHERE DATE_LAST_MODIFIED > (SYSDATE - " + @[User::Lookback] + ")"

    You might need to define look back as a String data type.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply