2

PACKAGE DESCRIPTION: I use a Source from One database do a lookup to get a surrogate geography key then another to check if the customer exists, if not insert the row if so update the row...

PROBLEM: I am unable to insert approximately 700,000 rows.

PROBLEM DESCRIPTION: I have looked at this for a long, long time now using data viewer, outputting to flat files and cannot find the cause of my issues.

From the below errors, research and checking through SSIS I have drawn a blank.

Error: 0xC0209029 at DimCustomer, Dw_DimCustomer [2]: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "Dw_DimCustomer.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "Dw_DimCustomer.Inputs[OLE DB Destination Input]" 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 DimCustomer, SSIS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Dw_DimCustomer" (2) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (15). 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: 0xC02020C4 at DimCustomer, SQL_Customer [154]: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Error: 0xC0047038 at DimCustomer, SSIS.Pipeline: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on SQL_Customer returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

SSIS Package

WORK DONE :

I've checked all formats match and that no truncation will occur. Checked all lookups are working correctly. Dropped my destination database (I'm in a test environment don't worry) and recreated it. I've checked that all the correct columns are showing the correct data in the correct format between tasks. I've checked the only error is coming from the final OLE DB destination output (which it is).

I am unsure where to go, as ever the answer is StackOverflow.

Any pointers or ideas or help would be welcomed with open arms.

Hadi
  • 36,233
  • 13
  • 65
  • 124
Will
  • 228
  • 1
  • 2
  • 15
  • Did you delete and recreate the source? – manderson Feb 20 '17 at 15:30
  • No, i have already run a SSIS package using the source which suggests it is fine. – Will Feb 20 '17 at 15:47
  • "The attempt to add a row to the Data Flow task buffer failed" could mean you've run out of RAM. Take a look in Task Manager and see if the RAM usage is climbing. The pattern you're using is very slow BTW. I always do a direct UPDATE/INSERT from a staging table. – Nick.Mc Feb 21 '17 at 06:10

3 Answers3

0

From the image you attached i think that you are using SSIS 2012 or higher, After searching there are many issue that cause this problem.

You can test the following things:

First

if your server operationg system is 64-bit try running your ssis package in 64bit runtime. you can follow this link for more details.

Second

Try replacing OLEDB Destination with a SQL server Destination and set the TIME OUT to higher value (for ex : 500)

Thrird

On you OLEDB Destination try to uncheck Check Constraints checkbox (The error may be caused by a constraints)

References

First workaround

Second Workaround

Additional Info

I think that this is the main Error message that you can search for it (if there is no error message received from your package) if my answer didn't solved it:

The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020

Hope it helps

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
  • First - All operating systems matched up. Second - The database isn't held locally and so OLEDB has to be used. Third - Check Constraints worked like a charm. SOLUTION: On you OLEDB Destination try to uncheck Check Constraints checkbox (The error may be caused by a constraints) Many Thanks!! – Will Feb 21 '17 at 12:06
  • @Will the error message you provided is ambigious. That why i provided many way to solve it :) . Happy to solve ur problem – Hadi Feb 21 '17 at 15:37
  • Un-checking constraints worked, but then I got data in my table that didn't adhere to the foreign key constraint... which is not ideal. I changed the destination to an OLE DB Command and wrote the required insert. Worked fine then. – goneos Oct 18 '19 at 02:18
0

Go to DimCustomer Properties:

enter image description here

Make BufferMaxRows to 100. This might increase the execution time for the package as it will transfer only 100 row at a time using a less RAM at that instance.

If this doesnt work:

You can create go for For-loop. Adding this same inside a For-loop appdeing 100 rows everytime it runs. You can found the loop execution by dividing the total count by 100. This will definitely help you!

Kinchit Dalwani
  • 398
  • 4
  • 19
0

This error occured when data is damaged before the data reaches the data flow destination. Data flow find unexpectly , null or blank or empty values you should to recreate your source data basese with healthy backup .

Neda
  • 1