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 ] 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.