0

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.

Community
  • 1
  • 1
Melanie
  • 3,021
  • 6
  • 38
  • 56
  • note at the begging of the string there is no drive letter or "\\MachineNameOrIp\" which would make it a relative path so I would guess that the relative path would not be correct especially when considering the context of SSIS will be c:\program files\microsoft....\..\..\. If The drive letter just didn't get copied or something my other thought would be is the location on a different server? Do you know if kerberos delegation is configured appropriately constraints could be an issue in that case? Are the SQL Service account and SSIS Service accounts local accounts or domain accounts? – Matt Sep 20 '16 at 23:54
  • Terribly sorry. The error message reads "\\nyfil006...." nyfil006 is the server name. My typo. I'll check with the DBA and sys admins on the other issues you raise. Thanks! – Melanie Sep 21 '16 at 14:19
  • This won't help, but I saw something similar with SQL 2008R2. I gave up trying to fix it and just hardcoded the folder path instead of using a variable. This worked for me because the destination location was consistent and never changed. – Jason B. Sep 21 '16 at 17:10
  • Also possible that the account running the job does not have access to that network path – Alan Waage Sep 22 '16 at 22:59
  • @AlanWaage, I'm assured by the sys admins and the DBA that all accounts being used have access to the path. Also note, this is not a scheduled job, so SQL Agent is not involved. – Melanie Sep 23 '16 at 13:36

0 Answers0