Cannot Open Datafile Ssis Package via Ssms
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...
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. Thanks
4 Answers
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.
Ensure the account configured for the package has sufficient permissions in all the areas it needs to write to.
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.
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.
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.
Troubleshooting:
Can you try to create file on local and then move the file using File System Task.
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
Elena Rostova holds a Master's degree in Public Health Journalism. She covers groundbreaking medical research, holistic wellness trends, mental health awareness, and nutritional science.
