July 8, 2011 at 3:25 am
Hi All,
I have a package that transform data from OLEDB Source TableA to OLEDBDestination TableB.
The count of records in TableA is around 7.500.000 records.
When I run that package, the package run well, but after around 200.000 records have been inserted to TableB, the package got error, and TableA become Red. This is the summary of the error messages :
Unspecified error
SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 11" (16989) failed with error code 0xC0047020 while processing input "Union All Input 2" (17111). 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.
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020
I've tried to search that problem, and I read in forum that someone suggest to change the Data Access Mode for OLEDB Destination From "Table/View Fast Load" to "Table/View". I've tried that, but the performance become very slow, and I still got the same error after around 300.000 records have been inserted to TableB.
Does anybody know what reason that might cause that error?
Thank you.
July 8, 2011 at 5:46 am
Fast Load has nothing to do with your Union All, so keep that setting!
(unless you like slow packages)
Apparently there is a problem with union all input 2. Maybe check the rows of that stream to see if there are any anomalies.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 13, 2011 at 7:35 pm
Union All2 is okay.
The problem may be because of the response time from the database. Because I used virtual machine with very huge database.
After I change OLEDB Destination to SQL Server Destination, and set the timeout to 500, all the records can be inserted to the destination table.
July 14, 2011 at 12:27 am
Does anybody know why the error can be happened in OLEDB Destination ? and Can we set timeout in OLEDB Destination?
Because we can Only use SQL Server Destination if the Destination Table is in the same server with our package.
Thank you..
July 14, 2011 at 12:59 am
I still think the problem lies somewhere else. The OLE DB Destination with Fast Load option shouldn't have too many troubles inserting millions of rows. Maybe you can try a smaller batch size?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply