October 12, 2009 at 11:21 am
I'm using a Foreach Loop Container to run the following steps within my SSIS package:
1) Get flat file from source folder
2) Reformat the files
3) Export pipe-delimited output file to destination folder
4) Move original source file to an Archive folder
5) If another file exists, repeat steps 1-4.
Everything runs correctly on the first file until step 4 where I receive the error:
"[File System Task] Error: An error occurred with the following error message: "The process cannot access the file because it is being used by another process.". "
Do I need to add a step between 3 and 4 to somehow "release" the source file so I can move it to the archive folder? I've done some research, but found nothing to address this specific scenario.
Any thoughts/suggestions would be much appreciated.
October 12, 2009 at 12:11 pm
Are you using a precedence constraint between the data flow and the filesystem task? This should insure that the data is extracted from the file before the archive process moves the file.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 12, 2009 at 12:39 pm
Tim Mitchell (10/12/2009)
Are you using a precedence constraint between the data flow and the filesystem task? This should insure that the data is extracted from the file before the archive process moves the file.hth,
Tim
Thanks for the reply Tim.
Yes, I am using a precedence constraint. I actually have two file system tasks off the data flow. The first is using a variable named RowCount to avoid creating an unnecessary output file (so the precedence constraint uses the expression @RowCount==0; no output file is created). The second moves the file to archive if RowCount > 0 (this is where the package is failing).
Let me know if you need additional info.
October 12, 2009 at 12:58 pm
Can you attach the package, or a screenshot of your control flow?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 12, 2009 at 1:28 pm
I've attached a screen shot of the Control Flow.
October 12, 2009 at 2:53 pm
It looks like you've got the data flow objects set up correctly, but the behavior you describe sounds like both of the file system tasks are trying to run at the same time. Is it possible that one of the precedence constraint expressions is incorrect?
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 12, 2009 at 3:22 pm
For testing purposes, since I know my source file had valid records, I removed the File System Task that would delete the output file if RowCount = 0. I also changed the archive file system task to a "Constraint" only evaluation operation rather than "Expression and Constraint" just to eliminate the possibility of having an incorrect expression.
I still received the same error message, so it appears the issue was not with the precedence constraint expressions.
October 12, 2009 at 7:30 pm
Interesting... I haven't encountered this behavior before. There's no requirement to explicitly release the file after processing in the data flow, so it's not clear to me why you're experiencing this problem.
If you want to share your DSTX file, I'll be glad to take a further look.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 13, 2009 at 8:48 am
I'm getting an error message when trying to attach the .zip file containing my .dtsx file. Can I e-mail you the .dtsx file instead?
October 13, 2009 at 10:45 am
Sure - it's tdmitch [at] gmail [dot] com. The forum software won't allow you to upload a DTSX file, but I think it let's you load a zip file.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 13, 2009 at 11:02 am
Yeah, I tried to attach a .zip file and it said "the application encountered an unexpected error."
I did just send you an e-mail though with the .dtsx file attached. Let me know if you need anything else. And thanks again for your help.
October 13, 2009 at 1:10 pm
Steve, I did receive your e-mail. I'll dig into it later today and see what I can find.
Thanks,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
October 19, 2009 at 9:06 am
Tim,
Do you have any further thoughts and/or suggestions after looking at the .dtsx package I sent? Even if I can't get a resolution to why I'm getting the error, maybe there's another direction I could go to archive the file.
Thanks for your time.
October 20, 2009 at 4:19 am
I've had something similar where I needed to allow time for an action to complete - once it was an external batch file run and another time a stored procedure. I added an execute sql to do a WAITFOR statement to give it all time to catch up with itself! In your case it would fit in before the file move.
October 20, 2009 at 7:58 am
Steve,
I found a couple of different issues. First, you were using two different variables (User::Source and User::fileName) to store essentially the same data. You should be able to change your For Each Loop to use the file's fully qualified name and consolidate this to a single variable.
Second, it looks like you've got an Archive variable set up but it's not being used. The file connection named Archive is using the base directory rather than the archive directory, which is probably why you're getting file contention; the package is trying to move the archive file to the same location it already exists. Also, you've got an expression to set that filename; since your File Connection is set to use "Existing Folder", you need not specify the filename, just the folder name.
hth,
Tim
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply