Hoping someone can point me in the right direction.
I have an SSIS package that reads in a number of flat files and dumps the data into database tables. This is running as a SQL Job (SQL 2008 R2 server) under a credential which has db_owner access to the db it connects to. The credential has the necessary access to the file path location. However, the job doesn't run under the credential.
If I open the package in visual studio under my login and run it, it runs fine, if I open it as the credential the package runs fine but no error is dropped into the tables. The package step itself appears with a green progress indicating that there were no errors.
The flat file connection string is set by an expression, a variable contains the location of the file path.
When I run the package in the job, the job fails with the error "The file name property is not valid. The file name is a device or conatins invalid characters".
This doesn't make sense if the package runs under my account. This would appear to be a permissions issue but I've given the credential sa access on the db server and given it full access to the file location.
Just as a side note, the package was initially a 2005 package but was upgraded to 2008 as part of a project. In the test environment everything with the package running as the newly created credential ran fine