arthrex
asked on
The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020
Hi Experts,
I have an SSIS job on SQL 2008R2 64bit.
The server has 8 GB RAM.
This SSIS job ran on an SQL 2005 32 bit with 3 GB RAM before.
NOw on the new server thos job sometimes runs properly and sometimes fails with below message.
Please help, what can I do? As the job ran on a machine with much less RAm, I can't believe that the server is out of RAM.
Is there any setting I need to change in the job?
Thanks a lot for your help!!
Executed as user: domain\admin. ...SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:53:00 PM Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:08.56 Code: 0xC02020C4 Source: 10_Sales_Cr_Memo_Invoice 05_Sales_Cr_Memo_Invoice [1] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2010-09-16 19:53:08.57 Code: 0xC0047038 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "05_Sales_Cr_Memo_Invoice" (1) 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. End Error Error: 2010-09-16 19:53:08.76 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All" (808) failed with error code 0xC0047020 while processing input "Union All Input 1" (809). 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. End Error Error: 2010-09-16 19:53:08.92 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 1" (7083) failed with error code 0xC0047020 while processing input "Union All Input 1" (7084). 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. End Error Error: 2010-09-16 19:53:08.95 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 3" (17524) failed with error code 0xC0047020 while processing input "Union All Input 2" (17742). 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. End Error Error: 2010-09-16 19:53:09.01 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 4" (27048) failed with error code 0xC0047020 while processing input "Union All Input 2" (27166). 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. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 7:53:00 PM Finished: 7:53:10 PM Elapsed: 10.577 seconds. The package execution failed. The step failed.
I have an SSIS job on SQL 2008R2 64bit.
The server has 8 GB RAM.
This SSIS job ran on an SQL 2005 32 bit with 3 GB RAM before.
NOw on the new server thos job sometimes runs properly and sometimes fails with below message.
Please help, what can I do? As the job ran on a machine with much less RAm, I can't believe that the server is out of RAM.
Is there any setting I need to change in the job?
Thanks a lot for your help!!
Executed as user: domain\admin. ...SQL Server Execute Package Utility Version 10.50.1600.1 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 7:53:00 PM Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:07.92 Code: 0x80004005 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: Unspecified error End Error Error: 2010-09-16 19:53:08.56 Code: 0xC02020C4 Source: 10_Sales_Cr_Memo_Invoice 05_Sales_Cr_Memo_Invoice [1] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2010-09-16 19:53:08.57 Code: 0xC0047038 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "05_Sales_Cr_Memo_Invoice"
How many rows come down the pipeline before it hits this error? Or does it fail immediately?
ASKER
I just tried it. about 198 000 rows already ran in the data target then it just failed.
ASKER
when I run the job in Visual Studio I get an additional error which wasn't shown in the Agent Log:
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
[SSIS.Pipeline] Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.
ASKER
for sure I run this job as Administrator!!
maybe problem is in 64 bit,
change your job step to run ssis package with 32 bit version of DTEXE, you can find 32bit DTEXEC from this directory:
"\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn"
change your job step to run ssis package with 32 bit version of DTEXE, you can find 32bit DTEXEC from this directory:
"\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
when I have a look in task manager the sqlsrv.exe has a memory usage of 6,9 GB
is this where I should have a look? or where else?
But how can this be ressource issue, that the same job ran without any error on a server with 4 Gb and only 3GB for sql?
is this where I should have a look? or where else?
But how can this be ressource issue, that the same job ran without any error on a server with 4 Gb and only 3GB for sql?
did you tried my suggestion ?
ASKER
when I have a look in the server properties of the sql server the max memory is 2147483647 MB.
should I limit this?
In addition to that I just saw in task manager that the memory usage goes up to 7,3 GB. Then the Job starts to buffer in the temp folder I've defined.
should I limit this?
In addition to that I just saw in task manager that the memory usage goes up to 7,3 GB. Then the Job starts to buffer in the temp folder I've defined.
ASKER
HI Reza Rad,
thanks for your help.
I couldn't find the mentioned path. But this one:
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec .exe
But I checked the check box in the SQL Server Agent:
Which sais under "Execution options":
"Use 32bit runtime". Might be the same, is it?
I just tried and the error came again.
I just saw: 96% used memory. Might this really be the problem?
thanks for your help.
I couldn't find the mentioned path. But this one:
C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec
But I checked the check box in the SQL Server Agent:
Which sais under "Execution options":
"Use 32bit runtime". Might be the same, is it?
I just tried and the error came again.
I just saw: 96% used memory. Might this really be the problem?
what tasks and transformation you have in your ssis package?
maybe you used resource consuming tasks like MULTICAST or like that
maybe you used resource consuming tasks like MULTICAST or like that
ASKER
mainly lookups and UNIONS.
I think the error is thrown with the union because it's mentioned in the error message:
The ProcessInput method on component "Union All" (808) failed with error code 0xC0047020 while processing input "Union All Input 1" (809). 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. End Error Error: 2010-09-16 19:53:08.92 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 1" (7083) failed with error code 0xC0047020 while processing input "Union All Input 1" (7084).
I think the error is thrown with the union because it's mentioned in the error message:
The ProcessInput method on component "Union All" (808) failed with error code 0xC0047020 while processing input "Union All Input 1" (809). 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. End Error Error: 2010-09-16 19:53:08.92 Code: 0xC0047022 Source: 10_Sales_Cr_Memo_Invoice SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Union All 1" (7083) failed with error code 0xC0047020 while processing input "Union All Input 1" (7084).
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.