Passing a variable from ExecuteSQL task to Oracle OLE DB Source

  • Debi Olk

    SSC Journeyman

    Points: 99

    I have created a Package ExtractDistributionRecords.

    In the Control Flow, I have an Execute SQL task that does a simple query to get the date that an extract last occurred.  This date is stored in a little SQL Server table.  I have to convert the result to the output that is required by Oracle.

    SELECT  REPLACE(CONVERT(CHAR(11),LAST_EXTRACT_DATE,106),' ','-') AS LastExtractDate FROM LastExtractDate

    In this task, I have mapped the resultset of LastExtractDate to a variable called vExtractDateIn.

    I have a Data Flow task that makes the OLE DB connection to Oracle with the following SQL Command:

    Select OUTLET_ID, CALLER_ID, PERIOD_ID, PRODUCT_KEY, FACINGS, OUT_OF_STOCK, ADD_DATE, CHANGE_DATE, PROCESSED_FLAG,  FACINGS_ON_ENTRY, OUT_OF_STOCK_ON_ENTRY

    FROM   FDR.STOREPRO_DISTRIBUTION sd

    WHERE  sd.outlet_id in (select o.outlet_id from fdr.outlet o where  o.outlettype_id in (33,34))

    AND ( sd.facings > 0 or (sd.facings = 0 and sd.facings_on_entry > 0)) AND (sd.ADD_DATE <= (select SYSDATE from dual) and  sd.ADD_DATE > :vExtractDateIn)

    I then perform the Input Column task into the SQL Server table where I store the data.  The above scenario works when I have a real date in place of the variable.

    Now when I use the variable and run the package I get the following error:

    [IncrementalExtract_DistributionTransactions [1]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E10. An OLE DB record is available.  Source: "Microsoft OLE DB Provider for Oracle"  Hresult: 0x80040E10  Description: "ORA-01008: not all variables bound ".

    I have tried to search on the ORA error message but that doesn't help me in declaring the variable within the OLE DB task and retain my columns.

    What am I doing wrong? 

    Thanks in advance.

  • caro0901

    Newbie

    Points: 1

    Hello,

     

    Is the error solved?

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

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