Melanie
Melanie

Reputation: 3111

Writing a flat file in SSIS succeeds if run from VisualStudio, fails if run from the Integration Services Catalog

I have an SSIS package that does a Bulk Insert, then executes a SQL Task, and then finally writes some database data to a flat file on our network. The package runs fine if I run it from Visual Studio 2012. However, if I deploy the package to the Integration Services Catalog on a 2012 SQL Server and run it from there, the Bulk Insert and SQL Task run fine, but when the package tries to output to the flat file, I get these error messages:

Cannot open the datafile "\\nyfil006\Projects\Accounting\CostRecovery\Cafe de Novo\HospitalityCharges.csv". HospitalityCharges Flat File failed the pre-execute phase and returned error code 0xC020200E.

I'm able to output the System::UserName to an errorlog, and it's what I think it should be: an account that has full permissions to the folder in the flat file destination (and its parent folders). I've tried creating a blank version of HospitalityCharges.csv, and DelayValidation is set to True for the Data Flow Task that outputs the flat file. My system admin has granted Network Service permissions to the folder as per this link and this link, but that doesn't help. I've also made the connection string an expression as described here. We've also created a mapped drive and used that for the Destination Connection String instead of a UNC path. No joy. Does anyone know why this is happening?

Another note: if I change the flat file destination to point to the C: drive, the package runs fine, whether I run it from Visual Studio or from the Integration Services Catalog.

Upvotes: 0

Views: 1575

Answers (0)

Related Questions