SSIS: Can't Get Source CSV File To Move when Failure in Data Flow Task

Inside my data flow task I have a Source File and a Destination OLE DB.  Outside this, in the control flow, I have my data flow task, on Failure (red line), configured to move the files to a "failure" directory where I archive.  

When I run the job, and for some reason a row in the CSV has an extra comma the entire SQL Job fails (I'm running SSIS via a sql job), no "failed" files move to the failed directory and I'm basically dead in the water.

I've tried configuring inside the data flow task but there is no option for file operations.  I've attached a screen shot of the Control Flow and Data Flow tasks.  


ControlFlow.jpg
DataFlow.jpg
davidcahanAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Anthony PerkinsConnect With a Mentor Commented:
You will have to add a task to "clean up" the data prior to doing the data transfer task.
0
 
davidcahanAuthor Commented:
Could I not use an Event Handler?
0
 
Anthony PerkinsCommented:
The Data Flow task in SSIS will fail as the data does not have the correct format.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
davidcahanAuthor Commented:
so the onError event handler for the data flow task won't allow me to trap that and then move the file over to my failed files directory.

sorry, I'm not intentionally being dense.  Firstly, I'm just confirming what I believe to know about event handlers and also, trying to figure out every possible way I might have to clean up the data could be difficult.  currently, it's choking on this

"1","2","foo"
                   "2","1","foo"
"3","2","foo"

as you can see there is a line of data that is either tabbed way over or something similar.  I wouldn't even know how to clean that.
0
 
Anthony PerkinsCommented:
>>so the onError event handler for the data flow task won't allow me to trap that and then move the file over to my failed files directory.<<
It should, but I honestly do not know for sure.  Hopefully someone will step up to the plate.

0
 
davidcahanAuthor Commented:
if I were going to clean the data first, any suggestions on how to handle the sample "bad data" I outlined above?
0
 
Anthony PerkinsConnect With a Mentor Commented:
Open the file, read all the lines and write them out to a new file, when you get to one that starts with a tab, replace it with an empty string.
0
 
davidcahanAuthor Commented:
hmmmm...ok.  thanks
0
 
davidcahanAuthor Commented:
can you tell me the basic ssis controls I will need to do that?
0
 
Anthony PerkinsConnect With a Mentor Commented:
You can use the Script Task to do this.
0
 
davidcahanAuthor Commented:
thanks for all your help!
0
 
davidcahanAuthor Commented:
by the way, the onError event does work theoretically.  but in practice, a process holds on to the file and the file move errors out.  I either need to fix the file as you suggest or figure out how to kill the process holding the file so that it can be moved.
0
 
Anthony PerkinsCommented:
>>the onError event does work theoretically.  but in practice, a process holds on to the file and the file move errors out.<<
That is good to know.  Thank you.
0
 
davidcahanAuthor Commented:
do you think my script task can attempt to overwrite the original file?  otherwise, how do I reset the File Variable name in the ForEach loop container?
0
 
Anthony PerkinsCommented:
No.  You should not do that.  You need to write to a new file.  If you cannot change the File Variable, then when you are done, delete the original and rename the new one.
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.