I am working on Integration services and came across a weird bug.
My data flow consist of:
-> DESTINATION
SOURCE -> SCRIPT_COMPONENT -> MULTICAST /
\-> DESTINATION
This error always occurs but differs the columns that is being addressed:
Error: 0xC0202009 at *STAFF_MIGRATION*, *STAFF_BASE* [627]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Error: 0xC020901C at *STAFF_MIGRATION*, *STAFF_BASE* [627]: There was an error with input column "*LAST_UPDATED_BY*" (651) on input "OLE DB Destination Input" (640). The column status returned was: "The status value given to the provider was not valid.".
The column LAST_UPDATED_BY differs to sometimes LAST_UPDATE_DATE
The weird behaviour is that this doesnt occur if I add Data Viewers along the path to the destination.
Any ideas?
EDIT: I am running on VS/BIDS 2008 SPI and SQL Server 2008 (RTM) x64 SP1.
EDIT: I am now running on SQL Server SP3 and still the behavior occurs.
I think my theory could be right. When I place a data viewer, it means it needs to build the data set for me to view prior to insertion. So the values for the columns must be built. I found a solution by making the destination a fast load table where bulk insertion is intended. That would mean it would have time to build the data and would not encounter the invalid value for columns.. It was just a wild guess but it worked