Cannot open the datafile - inconsistent

Question

Cannot open the datafile - inconsistent

Chris 7190

SSC Rookie

Points: 36
More actions
March 3, 2014 at 8:58 pm

#396360

Hi all,
We have an SSIS package that produces around 100 flat files in lieu of a proper integration broker.
This package runs weekly. About once a month, the package fails with the "cannot open the datafile....." for one of the files, this file differs each time.
Permissions are NOT an issue, as the package reads metadata and generates all other 99 files with no problem, and all use the same user for DB connection.
The package is set to overwrite the existing files and also make an archive .xls in a seperate dir. The problem is resolved when the file in question is deleted and the package re-run manually. How ever I am interested to know why it happens in the first place.
Any ideas on what could cause this would be very helpful.
Cheers.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sushil Dwivedi SSC Eights! Points: 973 More actions · Answer 1

I think overwrite or deleting existing file from source folder could be an issue. After loading data into staging table, you can move those file into Archive folder in place of overwrite existing file and moving to archive folder on same time.

Chris 7190 SSC Rookie Points: 36 More actions · Answer 2

Yes, thank you for the response, I could setup a file task that moves .csv to another folder or deleting them totally before running the job, I am however interested in why the SQL agent fails at reading the metadata from the file causing job failure in a random fashion. This points to a core SSIS issue that may need to be addressed by MS.

Lowell SSC Guru Points: 323518 More actions · Answer 3

is it possible tha that the file being read is still being written by some other process , and you are accidentally grabbing it mid-stream? i know i had an FTP issue like that once before; file was still being written when i grabbed it.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

aaron.reese SSChampion Points: 13498 More actions · Answer 4

I'm with Lowell on this one. Something has gotten hold of the file you are trying to write to. You could try writing to a unique filename (add a timestamp using a variable...) and then a file system task to rename it. the rename would fail, but at least you would have output the data. If this still failed then I would suggest network dropouts or disk space might be an issue.