July 1, 2009 at 7:39 am
Hi
I have a lookup which can be updated or insert into my table.suppose i have 10 records i can insert the records into table by using the look up and next day load if i get 11 records which 10 records are same which i have loaded previously and got some change to the column values so now i need to update the records for those 10 Records and one new record has to be inserted.Its like SCD1..
Now the problem is i can insert the row which i coming new but if i m going to update the record to my destination table nothing but lookup table its getting error as
Error: 0xC0202009 at If the data of the file is validated load the data into the Source table, OLE DB Destination [15476]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Native Client" 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 If the data of the file is validated load the data into the Source table, OLE DB Destination [15476]: There was an error with input column "Delete_Flag_Out" (17730) on input "OLE DB Destination Input" (15489). The column status returned was: "The value could not be converted because of a potential loss of data.".
Error: 0xC0209029 at If the data of the file is validated load the data into the Source table, OLE DB Destination [15476]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "input "OLE DB Destination Input" (15489)" failed because error code 0xC0209077 occurred, and the error row disposition on "input "OLE DB Destination Input" (15489)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
Error: 0xC0047022 at If the data of the file is validated load the data into the Source table, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Destination" (15476) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at If the data of the file is validated load the data into the Source table, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited.
Can any one help me in this issue???
Thanks in Advance
July 1, 2009 at 7:51 am
It seems from the error message that there is a problem with the data type for the Delete_Flag_Out column.
Check that you are using the correct data type in the data base and that if you are converting data types that you use compatible data types.
July 1, 2009 at 8:26 am
Thanks for the quick response and one more problem is how can we update the rows which are already been inserted.
As i worked in informatica before there we have UpdateStrategy Transformation where we can say the row is to update,insert,and delete by the functions DD_Update,DD_Insert,DD_Delete
Which Transormation can i use for the same functionality in the SSIS?
Thanks in Advance
July 1, 2009 at 8:43 am
There are no direct replacments for those functions in SSIS 2005, I also am an informatica developer so i understand the issues.
In SSIS i would either write an update stored procedure to do the updates, this is inefficent though but works well on smaller data sets.
Otherwise I would load all the data into a staging table and do the updates using a UPDATE with an INNER JOIN sql statement
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply