Passing a variable from ExecuteSQL task to Oracle OLE DB Source

• Debi Olk

  SSC Enthusiast

  Points: 129

  More actions

  August 7, 2007 at 2:14 pm

  #119953

  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

  More actions

  June 5, 2019 at 4:23 pm

  #3650497

  Hello,

   

  Is the error solved?