SSIS Package Fails when run as Job

0
votes

Within SSIS I am able to run each package without a problem. However when I run it via SQL job, I am encountering these errors:

Message:

Code: 0xC0047022 Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "ALL KN and UNK BR" (120) failed with error code 0xC0047020 while processing input "Union All Input 2" (144). 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.

Code: 0xC02020C4 Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.

Code: 0xC0047038 Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on SRC OLE DB 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.

Code: 0xC0209029 Description: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "OLE_DEST F_BR_Summary.Inputs[OLE DB Destination Input]" failed because error code 0xC020907B occurred, and the error row disposition on "OLE_DEST Summary.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.

Code: 0xC0047022 Source: DFT Populate Summary SSIS.Pipeline Description: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "Summary" (821) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (834). 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: 2016-07-19 20:40:18.97
Code: 0x80004005 Source: DFT Populate F_BR_Detail SSIS.Pipeline Description: Unspecified error End Error Error: 2016-07-19 20:40:19.41

Code: 0xC02020C4 Source: DFT Populate Detail SRC OLE DB [608] Description: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020. End Error Error: 2016-07-19 20:40:19.67 Code: 0xC0047038 Source: DFT Populate F_BR_Detail SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on SRC OLE DB returned error code 0xC02020C4. The component returned a failure code when ... The package execution fa... The step failed.

1
sql-serverssis
SSIS errors are very verbose, and the key is to look through them and find the error that matters. Unfortunately this error doesn't have anything useful in in it. There might be error messages in other parts that are useful. Things like 'login failed'. The usual reason that packages fail as a job is that the job is running as a different user and that user has different file rights and database connection rights. Jobs also run on the SQL Server rather than on a local machine. Perhaps your first step is to identify the user that that this is running as, as reported by the SQL Agent log. – Nick.McDermaid

1 Answers

0
votes

This error is caused by not having enough memory available. Solution is reduce the SQL Server engine max memory by running the sp_configure stored procedure.

The following example sets the max server memory option to 4 GB:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

For more information: https://msdn.microsoft.com/en-us/library/ms178067.aspx