Data Flow Event Handler: File Being Used By Another Process, Can't Move

I have an onError event handler attached to my Data Flow task so that just in case I get a CSV file that is mis-formatted, I can move it into a Bad File folder.

The onError event fires, but it won't move the file because it says it is being used by another process.  I do not get this same type of error later in the Control Flow of the package when I attempt to move the file after successful processing.

Any ideas on how to release the file from the process first and then move?  I'm pretty sure it's being held by some DTS process but I have to believe it can be released and then moved.
davidcahanAsked:
Who is Participating?
 
nmcdermaidCommented:
It appears to make sense. The data flow has to be using it to throw the error, and its still effectively using it during the error event, so its locked.


Perhaps you should do one of the following:

1. Ignore it and move on the the next one. Assuming a successful file is moved to a 'processesd' folder then you know that everything thats left is a problem file and can be moved

Or...

2. Explicitly log the file path to a failure table. Then at the end of the process, go through this table and move them all.


0
 
rocky_lotus_newbieCommented:
please check if you are able to find the details of the process locking the file after the OnError event fires from  -
Computer Managent -> Shared Folders -> Open files.

Thanks,
0
 
davidcahanAuthor Commented:
nothing
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dbaSQLCommented:
If you're absolutely certain the file is not otherwise in use, possibly you should check the MaxConcurrentExecutables and EngineThreads:

http://stackoverflow.com/questions/4759892/file-in-use-error-in-ssis
http://blogs.msdn.com/b/sqlperf/archive/2007/05/11/implement-parallel-execution-in-ssis.aspx
0
 
davidcahanAuthor Commented:
It's definitely not in use by any other "entity" except the SSIS package.  I've actually read that this behavior (not being able to move a file source during a dataflow onError event) is a "bug".  
0
 
davidcahanAuthor Commented:
that's actually a really good idea.  I never considered that.  my root folder can be both filles to process and failed files, while my archive can be successfully processed files.

thanks.
0
 
davidcahanAuthor Commented:
I gave points to @dbaSQL also because I wasn't sure the links given actually solved my problem but I also didn't have enough time to fully test.  ultimately though @nmcdermaid's solution was the most feasible at the time.
0
 
dbaSQLCommented:
Thank you for the points, davidcahan.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.