Hello, I am having a problem with an SSIS package that I am running with a scheduled task with SQL Agent. If I run the package manually with SQL Server Information Services it works, but when I automate the task with SQL agent it fails. I am using SQL Server 2008. I created a VM to replicate my server in production with the same SQL Server version and the task with SQL agent does not fail, but in the other server fails. The server in production is running other tasks with the agent that runs successfully, but this new task fails. Attached is a sample of the data the SSIS pkg is converting to fill out 4 tables: Employees, OU, EmployeeGroup, and Groups. I also attached the error that I am getting. I can figure out why the pkg runs successfully if I run it manually, but fails when is automated to run with the SQL agent. To clarify, Jordan, the flat file I am using is the same to run the job in the VM is the same I am using in the server in production. Also, the account I am using to run the package has access to the flat file, and I am using a Proxy account to run the package. This is the error I get: Message Executed as user: abc. Microsoft (R) SQL Server Execute Package Utility Version 10.50.6000.34 for 64-bit Copyright (C) Microsoft Corporation 2010. All rights reserved. Started: 11:48:05 AM Error: 2017-06-29 11:48:07.97 Code: 0xC020209C Source: DFT Employee Table Flat File Source  Description: The column data for column "UserName" overflowed the disk I/O buffer. End Error Error: 2017-06-29 11:48:07.97 Code: 0xC0202091 Source: DFT Employee Table Flat File Source  Description: An error occurred while skipping data rows. End Error Error: 2017-06-29 11:48:07.97 Code: 0xC0047038 Source: DFT Employee Table SSIS.Pipeline Description: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Flat File Source" (628) returned error code 0xC0202091. 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 DTExec: The package execution returned DTSER_FAILURE (1). Started: 11:48:05 AM Finished: 11:48:08 AM Elapsed: 2.028 seconds. The package execution failed. The step failed. Any suggestions on this issue? Thanks.
I've had this issue, it looks like the server you are running the package on has run out of RAM in my experience. Increase the RAM and you should be good. Check how much RAM SQL Server is allocated though, because SSIS doesn't use the RAM allocated to SQL Server and if SQL Server has taken all the RAM there isn't anything left for SSIS or for that matter the OS.