SSIS - cannot move flat file on error - package won't release the file

Question

question

user-1032 (yahoo) asked Jan 27, '10

SSIS - cannot move flat file on error - package won't release the file

I am trying to put a bad input file into another folder when I have load errors. The file in question is a comma delimited CSV that occasionally has commas in the wrong place, and then the file will not load because the data doesn't match the layout.

I tried to move the file to another folder (all processing is within a for-each-file loop), but the flat file connection manager apparently won't let go of the file for the "move file" process to work. I have the "move file" (File System Task) process in the event handler for when this "Flat File Source" object errors out.

I get the following error when the "move file" process attempts to move the file that is being processed: "The process cannot access the file because it is being used by another process."

ssis

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-01-31T02:31:45Z

Bob Leithiser answered Jan 31, '10

You could log the error in a SQL database table using a Data Flow Task or invoke a stored procedure using an Execute SQL Task and then use another Foreach Loop to enumerate the database rows with errors and then do the move file. That way the move file is outside of the process where it is being read.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-03-10T11:29:51Z

Jason Crider answered Mar 10, '10

Two things come to mind: Have you tried to have the connection to the flat file stored in a variable and set it through an expression?

Have you tried to look at places where you could set "Delay Validation" to true to see if that helps?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-03-10T12:52:52Z

Pavel Pawlowski answered Mar 10, '10

Depends where you tried to move the file. If you tried to move the fie in the OnError handler inside the data flow, then it is used by the connection so you cannot move the file. (The file is used inside the Data Flow Task, so you cannot move it)

You need to create a failure constaint from the Data Flow Task to a File System task, which will move the files. (instead of the File System task you can use also the Script Task where you can handle the file in a way you want.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question