SSIS Handling a Flat File Missing a Text Qualifier

1
votes

I'm currently designing as SSIS package to import some CSV files and needs to account for various error types. One of the errors is an incorrect or missing text qualifier.

I.E: "col1","col2","col3/,"col4"

The package is currently throwing the error "[ProductMaster CSV [66]] Error: The column delimiter for column "Column 2" was not found.". Which is what I would expect to see in this situation.

Apparently getting the file initially sent in the correct format isn't an option at the moment.

I've tried changing the file to have no text qualifier, but this then falls over if there is a comma in a field so is not a viable solution.

Is there any way of handling this?

2
csvssisflat-file
In your example, how do you know for sure that col3 has the missing text qualifier. The answer is you don't. This sort of issue will only ever cause issues down the line. – BIDeveloper
do a script task to fix the file and then import it, or and Execute Process Task and use an existing cleanup utility. Is this a one time thing? Excel is actually pretty good at fixing some of this you could open in Excel and save it or convert back to CSV or something. This is one of the reasons I typically ask for Pipe (|) delimited instead less delimiter issues :) – Matt
It's going to be a daily file we're receiving. I'd hope this test was an extreme case though; I'd be surprised if the system creating the file would actually get a text delimiter wrong. May have a look at some 3rd party options just in case though. The pipe suggestion is a good one! – b_en

2 Answers

0
votes

I use a third party tool to read csv files and it handles this type of situation. If you must do something on your own I would import the entire line to one column and then parse it with either a stored procedure or a script component.

There are plenty of solutions out there, some free and some with a minimal cost.

I have never found a way to handle this with SSIS connection managers 'out of the box'.

0
votes

To solve this issue, look at your file format. Use text editor like Notepad++ and if your file is CR, make sure you don't use (") instead choose in the text qualifier and choose CR in the header row. This should work 100%