Post reply

SSIS Oracle 10G Extracting data to SServer 2005

  • December 11, 2008 at 5:09 pm

    #127427

    I'm designing an incremental extraction package (SSIS) from Oracle 10g to SS2k5. I'm passing a parameter that stores the LatestExtractionDate to the OLE DB Source Editor. But when I try to use the parameter, it says: "the provider might not help to parse parameter".

    Sample statement:

    Select * from tableOracle where createDate > ?

    This is the way to do it in Oracle. I have to replace the expression after ">" sign with a "?" but it's not letting me:

    where a.dw_load_date > to_Date(to_char(sysdate-1,'yyyymmdd') || '010000','yyyymmddhh24miss')

    Anyone knows how to use a SQL Server datetime data type on a Oracle query?

  • December 12, 2008 at 3:16 am

    #911518

    You could use a variable evaluated as an expression to build the SQL command that selects the data. That way you eliminate the need for parameters and you can debug easier because you'll be able to see the actual statement.

    HTH

    Kindest Regards,

    Frank Bazan

  • December 12, 2008 at 12:09 pm

    #911862

    Thank Frank.

    I got the Expression working. Now I need "select max(LastSuccessfullExtractionDate) from tableLogs" capture this in a variable and use it in my Expression.

    My expression looks like:

    select * FROM dwods.cus a where a.dw_load_date > " + "'" +(DT_WSTR,4)YEAR(DATEADD("dd", -1, GETDATE()))

    + RIGHT("0" + (DT_WSTR,2)MONTH(DATEADD("dd", -1, GETDATE())), 2)

    + RIGHT("0" + (DT_WSTR,2)DAY(DATEADD("dd", -1, GETDATE())), 2) + "'"

    Basically I need to replace GETDATE() with the max(LastSuccessfullExtractionDate) in the Expression.

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

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