July 30, 2013 at 8:36 pm
Ok - I'm stumped. I have an SSIS package that reads our CDR data and i need to do an incremental load nightly to only add new records. I can successfully run the package and insert directly in the table with out the look up transform, but as soon as i try to see if any of the records are in the destination table already the lookup transform fails with the following error:
[Error Rows [169]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E14.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E14 Description: "The Bulk Insert operation of SQL Server Destination has timed out. Please consider increasing the value of Timeout property on the SQL Server Destination in the dataflow.".
I have sys admin rights on the server. I am running in admin mode with 32 bit enabled. Bulk Insert is allowed on the server and the package is ran locally on the server using the bulk insert operation.
SSIS Data Flow Task:
flat file source ->derive some columns->conditional split to find all non null values for insert ->lookup transform to see if the record exists->insert new records into OLE DB Destination Table
I'm stumped on how to get the lookup transform to work. Is there another option to only insert new records via ssis efficiently?
Thanks in advance for your help!
SOLUTION: Move the lookup to a 2nd data flow. There was nothing wrong with the permissions. I had the lookup transform in the same data flow task as the original connection, so i assume you cannot connect twice causing the error. Once i surrounded the first part of the dataflow task in a sequence and set up a staging table for the new data set coming in. Then set up a second data flow task with the lookup comparing the staging table to the reference table. All worked perfect in full cache mode. 🙂
Katie
July 31, 2013 at 4:55 am
The only way is to have a staging tables type system where you load all records and do comparison work on which require inserting.
From the error it looks like the lookup transform is unable to see the data in your look up, happen check the security credentials, I know you have sysadmin but it appears that could be the problem.
🙂
July 31, 2013 at 5:59 am
Thanks, I will try that. I got a lot of responses to try the kerberos configuration tool for SQL as well.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply