0

Background Created package in BIDS. Deployed to SSMS Package writes files to a CSV file in a network fileserver. The default name of the package's flat file destination is $path\workcsvout.csv Package derives filename from an expression

Issue When I configure and run from SSMS, it fails with Error DFT -Extract to File:Error:Cannot open the datafile "........\DerivedFilename.

Troubleshooting

Verified the file exists in directory - used flat file destination temp filename, before derived filename - still failed changed name to file it was trying to open - still failed

I am running job from my login in SSMS, via SSISDB - Projects - Package - .dtsx package - Execute

See pictures below and advise if more information is needed.error1 flatfiledestination Thanks

Hituptony
  • 2,740
  • 3
  • 22
  • 44

4 Answers4

3

  1. Ensure Visual Studio isn't open after attempting to either run the package directly from the Integration Services Catalog as I have found that VS can hang onto a connection to the files you are writing to and it can throw similar errors.

  2. Ensure the account configured for the package has sufficient permissions in all the areas it needs to write to.

  3. After VS is closed and permissions are all set in step 2, try executing the package directly inside the Integration Services Catalog in SSMS. If this works, move to step 4. If this doesn't work, troubleshoot the errors and ensure security is all setup properly and you are executing the package with the same account.

  4. If you are here, I will assume you want to schedule the package. Ensure that the owner is the same account used in step 2. Check the "Run As" account in Step in the job, if that account is not the same as step 2 then you either need to make it the same or give that account the same access as the account used in step 2.

I went through this troubleshooting process and it solved my issue. I also was building files on a general UNC file path like \servername\folder\folder without needing to do any local business with \servername\d$\folder\folder that other people recommend.

ichauvin
  • 230
  • 1
  • 5
  • 14
1

I would check to make sure that your SQL Server service account has full rights to the landing folder.

After experiencing the same issue as you, I finally checked the folder permissions that were created for our SQL Server service account. Come to find out that it was missing the "Full Control" and "Modify" folder permissions. Once I granted these to our service account, the issue went away.

Folder Permissions Dialog Box

Miguel Sanchez
  • 29
  • 4
0

Troubleshooting:

Can you try to create file on local and then move the file using File System Task.

Shoeb Siddique
  • 155
  • 2
  • 8
  • No, because of the nature of the files they need to be created on the FileServer – Hituptony Nov 19 '15 at 21:07
  • Can you expand on that statement? Also I suggest you check each of @ichauvin suggestions. – Nick.Mc Mar 08 '16 at 23:00
  • I want to check if there is any network related issues. According to the error message package is not able to locate the file. it might be access related issues or might be file is being used by some other resource. @ichauvin is right. – Shoeb Siddique Mar 18 '16 at 16:22
0

I was trying to pump the data which is in csv file. Closing the visual studio and closing the csv file which was opened in another machine resolved the problem

Dev
  • 1,451
  • 20
  • 30