I just changed the package to point to the source file on a new server. When I run the package in BIDS it works and when I run the package in SSMS it works. But when I try to run it from the SQL Service Agent Job it fails with the following error
Executed as user: MY_DOMAIN\SQLSvcAcct. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2000.8 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 9:51:26 AM Error: 2016-09-13 09:51:27.57 Code: 0xC020200E Source: Data Flow Task Flat File Source [2] Description: Cannot open the datafile "\...............\myFile20160913.txt". End Error Error: 2016-09-13 09:51:27.57 Code: 0xC004701A Source: Data Flow Task SSIS.Pipeline Description: Flat File Source failed the pre-execute phase and returned error code 0xC020200E. End Error
I made sure that SQLSvcAcct has permission to the folder on the new server. I am not sure what else to try. I have had this issue before when moving these packages from a test server to production and this fixed the issue.