Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-02-20
10
Medium Priority
?
364 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:davidcahan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 4

Expert Comment

by:rocky_lotus_newbie
ID: 34939973
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
 

Author Comment

by:davidcahan
ID: 34960363
nothing
0
 
LVL 17

Assisted Solution

by:dbaSQL
dbaSQL earned 400 total points
ID: 34985020
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:davidcahan
ID: 34985058
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
 
LVL 30

Accepted Solution

by:
nmcdermaid earned 1600 total points
ID: 34990082
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
 

Author Comment

by:davidcahan
ID: 34992813
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
 

Author Closing Comment

by:davidcahan
ID: 34992819
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
 
LVL 17

Expert Comment

by:dbaSQL
ID: 34993419
Thank you for the points, davidcahan.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question