Search code examples
csvssisflat-filessms-2012

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 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

Solution

    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.