SSIS

SSIS package fails with out of memory errors

You might get below out of memory errors when you run SSIS packages 

Errors

A buffer failed while allocating n bytes. The system reports n percent memory load. There are n bytes of physical memory with n bytes free.

There are 2147352576 bytes of virtual memory with 44814336 bytes free. The paging file has 687069143034 bytes with 35348451328 bytes free.

A buffer failed while allocating 63160 bytes. The attempt to add a row to the Data Flow task buffer failed with error code 0x8007000E. 

SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component “Read Results” (975) 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. 

The system reports 2 percent memory load. There are 206147428352 bytes of physical memory with 24692375552 bytes free. There are 8796092891136 bytes of virtual memory with 8713333325824 bytes free. The paging file has 205966053376 bytes with 15835136 bytes free.

Executed as user: Domain\Password . …9.00.4035.00 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  6:06:21 AM  Error:     Code: 0xC0047012     Source: Load Daily Run Piece DTS.Pipeline     Description: A buffer failed while allocating 6356480 bytes.  End Error  Error: 2009-12-29 06:19:41.56     Code: 0xC0047011     Source: Load Daily Run Piece DTS.Pipeline     Description: The system reports 82 percent memory load. There are 4294656000 bytes of physical memory with 756637696 bytes free. There are 2147352576 bytes of virtual memory with 399495168 bytes free. The paging file has 8418062336 bytes with 5051555840 bytes free.

The system reports 24 percent memory load. There are 68716240896 bytes of physical memory with 51754737664 bytes free. There are 2147352576 bytes of virtual memory with 146911232 bytes free. The paging file has 137430540288 bytes with 120562003968 bytes free.

Below are options you can use to solve the above errors

1. Check if the Page file for the system is configured properly, you might get this error if there is no page file or if page file is very small

2. Change the lookup cache mode from Full to Partial or no cache mode.

3. Migrate to 64-Bit SSIS if you are in 32-BIT. Remember you have to migrate all the drivers and providers used in connections. If you are in 64 BIT system and getting above errors while executing packages from BIDS then check if you have set package to run in 64bit run time ( Run64bitruntime = True in  Integration services project properties page).

4.  Divide the package in to multiple child packages (Execute package task –> ExecuteOutOfProcess =True)

5. Tweak DefaultBufferMaxRows and DefaultBufferMaxSize size.

Advertisement
by

SSIS package fails when executed as job using proxy account

Issue

The SQL server Integration package which transfers the data from data source like excel to SQL Server database fails when executed from SQL Agent job using proxy account

ERROR

Date                      4/25/2013 4:16:34 PM

Log                         Job History (SSISTest)

Step ID                 1

Server                   Myserver\SQL2008STD

Job Name                            SSISTest

Step Name                         SSISJob1

Duration                              00:00:01

Sql Severity                        0

Sql Message ID                 0

Operator Emailed                           

Operator Net sent                          

Operator Paged                               

Retries Attempted                          0

Message

Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4276.0 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  4:16:34 PM  Info: 2013-04-25 16:16:34.81     Code: 0x4004300A     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-25 16:16:35.04     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager 1”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Access Database Engine”  Hresult: 0x80004005  Description: “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.  End Error  Error: 2013-04-25 16:16:35.05     Code: 0xC020801C     Source: Data Flow Task to move data from MS Excel to SQL server database Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager 1” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC0047017     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-25 16:16:35.07     Code: 0xC004700C     Source: Data Flow Task to move data from MS Excel to SQL server database SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-25 16:16:35.09     Code: 0xC0024107     Source: Data Flow Task to move data from MS Excel to SQL server database      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  4:16:34 PM  Finished: 4:16:35 PM  Elapsed:  0.764 seconds.  Process Exit Code 1.  The step failed.

Cause:

Missing permission on PROFILE directory of SQL server Agent service account for Proxy account .

BufferTEMPstorage path and BlobTempstoragepath are defaulted to TEMP and TMP environment variables for SQL Server agent start up account by default.

When you use proxy account to execute a package then proxy account should have access to temp and TMP folder of SQL Server agents start up account profile.

TEMP=C:\Users\StartupaccountofSQLAgent\AppData\Local\Temp

TMP=C:\Users\ StartupaccountofSQLAgent\AppData\Local\Temp

If your start up account of SQL Server agent is Local service or Network service then proxy account should have permission for TMP and TEMP folder located under C:\Windows\ServiceProfiles for Local service and Network service

If the proxy account doesn’t have access to this location SSIS job would produce the below error:

Date,Source,Severity,Step ID,Server,Job Name,Step Name,Notifications,Message,Duration,Sql Severity,Sql Message ID,Operator Emailed,Operator Net sent,Operator Paged,Retries Attempted

04/08/2013 11:26:19,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:26:19,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.   

Started:  11:26:19  Info: 2013-04-08 11:26:19.94     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:26:19.98     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:26:19.98     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:26:19  Finished: 11:26:19  Elapsed:  0.405 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,0,UKLONDT642483,ExcelTC,(Job outcome),,The job failed.  The Job was invoked by User MyDomain\MyUser1.  The last step to run was step 1 (ExcelTC\Package).,00:00:01,0,0,,,,0

04/08/2013 11:10:11,ExcelTC,Error,1,UKLONDT642483,ExcelTC,ExcelTC\Package,,Executed as user: MyDomain\MyUser1. Microsoft (R) SQL Server Execute Package Utility  Version 10.0.5500.0 for 32-bit  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.    Started:  11:10:12  Info: 2013-04-08 11:10:12.65     Code: 0x4004300A     Source: Data Flow Task SSIS.Pipeline     Description: Validation phase is beginning.  End Info  Error: 2013-04-08 11:10:12.78     Code: 0xC0202009     Source: Package Connection manager “Excel Connection Manager”     Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.  An OLE DB record is available.  Source: “Microsoft Office Access Database Engine”  Hresult: 0x80004005  Description: “Unspecified error”.  End Error  Error: 2013-04-08 11:10:12.78     Code: 0xC020801C     Source: Data Flow Task Excel Source [1]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager “Excel Connection Manager” failed with error code 0xC0202009.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0047017     Source: Data Flow Task SSIS.Pipeline     Description: component “Excel Source” (1) failed validation and returned error code 0xC020801C.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC004700C     Source: Data Flow Task SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2013-04-08 11:10:12.79     Code: 0xC0024107     Source: Data Flow Task      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  11:10:12  Finished: 11:10:12  Elapsed:  0.593 seconds.  Process Exit Code 1.  The step failed.,00:00:01,0,0,,,,0