August 10, 2011 at 10:49 am
I'm trying to read a file that is currently being written to. I can view the data in the preview pane and I can copy it from a command prompt so it's not opened exclusively by the writer. When I run the SSIS 2008 package, I get the error message "[Source for FlatFileConnection [1]] Warning: The process cannot access the file because it is being used by another process." I have set the Data Flow task IsolationLevel property to "ReadUncommitted" and TransactionOption to "NotSupported" but it still doesn't work. (The package properties are the same by default.) The File System Task gives me the same error.
I would rather not use a command shell to copy. I would prefer reading directly from the source. Any suggestions for accessing this file? ...or something I may have missed?
Thanks,
Brian.
August 12, 2011 at 9:22 am
Where to begin...
Isolation level only relates to databases, file sources don't count.
The out of the box transforms will not let you do it because they want to exclusively lock the files. I know of no way to change that behavior.
You could use a script source in the data-flow and read the data yourself into the pipeline. Then you could open the file in a non-exclusive manor.
However, my bigger question is why are you reading a file that is not complete and how do you expect to handle the case where data is being written at the same time it is being read?
CEWII
August 12, 2011 at 9:36 am
I would agree with Elliot. You are potentially asking for trouble here. How can you know what you're read, or more importantly, if anything's changed?
I'd make a copy, if only to be sure I had a set amount of data to import and a way to audit back what was imported in case I have an issue.
The file system sometimes allows you to read a file, even if it's open by someone else. There are tricks around that, which is how backup software can use an Open File Manager to back up a file. However the point in time for which your backup is done doesn't mean that's an intact file. For example, a SQL Server MDF might not be consistent within itself if you restored an MDF backup from an open file manager.
The same could occur with a Word file. It's possible that formatting isn't correct or even a tail file formatting might not exist.
August 12, 2011 at 9:39 am
I'm reading a CSV output file from a DB2 database. That database never closes the file until the end of the week and that's just before it deletes it. It's not our system so there's nothing I can change there.
I've recently started a new position and I'm trying to improve things. This job failed last week and the data was permanently lost so it required some rework to minimize the risk of this happening again. Since the source system does not keep the data, there's always a risk.
Rows that are not complete are not processed.
I have the process working with the copy then read. This is probably the best since the copy happens very quickly it will reduce the risk of read/write at the same time. I also time-stamped the files so I have an audit trail.
Thanks,
Brian.
August 12, 2011 at 9:43 am
Can i ask a larger question then.. Why aren't you reading the DB2 data directly? Why this rube-goldberg contraption?
CEWII
August 12, 2011 at 9:43 am
If you have it working, I'd go with the copy and then read. Especially if it's out of your control to change the process.
Alternatively, could you just read the data from DB2?
August 12, 2011 at 10:02 am
It's an inventory system and my understanding is that they are not storing anything but current stock level. The output stream is a log of the changes and was added after the fact. I could be wrong though, my information is second hand. It was developed by consultants that are no longer there.
I don't know DB2 so I wouldn't even know how to look at the schema. There are quite a few SQL Server problems here that I can solve much more quickly, so I'm not planning on worrying too much about this one.
August 12, 2011 at 10:08 am
It sounds like you are on the ball with this one. I'd do the copy, and not add the complexity or possible issues from the open file read.
August 12, 2011 at 11:12 am
I have to agree with Steve..
CEWII
August 12, 2011 at 12:16 pm
Thanks for the feedback. I didn't like the extra step of copying the file first but it does give some auditibility and is safer. In a few months, I may be able to revisit this and check if I can read directly from the database. I have a linked server set up already that I can read from if I can find a table. A cursory look didn't show any likely candidates.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply