SSIS 2012 package not working from SQL Server Agent job
Posted on 2014-11-13
We have created an SSIS package which reads csv files from shared path and processes it to dump data into SQL Server 2012. The package was initially developed in 2008 and upgraded to 2012 using SQL Server Data Tools, as our requirement required the packages to run from SQL Server 2012.
We scheduled the package as a sql server agent job and try to execute the job using proxy account. The proxy account has access to shared folder from where the csv files are read and has been given permission in the DB as well. we encountered the below error -
Microsoft (R) SQL Server Execute Package Utility Version 11.0.2100.60 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 1:56:18 AM Error: 2014-11-13 01:56:20.52 Code: 0xC0202020 Source: Data Flow Task Flat File Source  Description: The buffer type is not valid. Make sure the Pipeline layout and all components pass validation. End Error Error: 2014-11-13 01:56:20.52 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: Flat File Source failed the pre-execute phase and returned error code 0xC0202020. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:56:18 AM Finished: 1:56:20 AM Elapsed: 2.23 seconds. The package execution failed. The step failed.,00:00:02,0,0,,,,0
When we tried to run the another package that was developed using SSDT also, it failed to execute. On setting the
package to run in 32 bit mode in the execution options tab of the job step, it works. We need to execute this in 64 bit mode only.
If anybody has encountered this issue, kindly let us know how to resolve this. Any help is highly appreciated.