Solved

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

Posted on 2011-02-19
15
699 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:davidcahan
  • 8
  • 7
15 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 34934174
You will have to add a task to "clean up" the data prior to doing the data transfer task.
0
 

Author Comment

by:davidcahan
ID: 34934254
Could I not use an Event Handler?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34934431
The Data Flow task in SSIS will fail as the data does not have the correct format.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:davidcahan
ID: 34934473
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34935230
>>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
 

Author Comment

by:davidcahan
ID: 34935287
if I were going to clean the data first, any suggestions on how to handle the sample "bad data" I outlined above?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 34935573
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
 

Author Comment

by:davidcahan
ID: 34937408
hmmmm...ok.  thanks
0
 

Author Comment

by:davidcahan
ID: 34937484
can you tell me the basic ssis controls I will need to do that?
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 500 total points
ID: 34939144
You can use the Script Task to do this.
0
 

Author Comment

by:davidcahan
ID: 34939254
thanks for all your help!
0
 

Author Comment

by:davidcahan
ID: 34939276
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34939338
>>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
 

Author Comment

by:davidcahan
ID: 34944321
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34949060
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Insert statement is inserting duplicate records 15 61
PERFORMANCE OF SQL QUERY 13 73
Stored procedure 4 32
How do I subtract date and time within a same column in SQL 4 38
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
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.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 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