CODEWITHSUNDEEP
sql-serverssisssis-2012
S.Jose
S.Jose

Reputation: 246

SSIS Package failing, not redirecting error rows

We have an SSIS package to import data from CSV flat file to a table on SQL Server. The file consists of several hundred lines of data that are delimited by a comma(,) with text data qualified by double quotes ("), but not all the text columns are qualified by double quotes. The problem arises when a string that is not qualified by quotes (") contains comma (,). This cause the package to fail with following error

[Source Data [130]] Error: The column delimiter for column "ColumnX" was not found.

I tried to redirect the bad rows to another flat file destination, but the package is not redirecting the bad rows, but its failing

Upvotes: 0

Views: 910

Answers (1)

Tab Alleman
Tab Alleman

Reputation: 31775

If you can't get whoever generates the file to fix it, your only programmatic solution is to write a script task that fixes the file before it goes to the dataflow.

The script would need to analyze each row to see if it has the right number of commas, and to add quotation marks around the fields. If it finds one with too many commas, it would have to apply some logic (which only you can determine) to decide which comma isn't a column delimiter and make sure that comma is either deleted or enclosed in the quoted field value.

Upvotes: 1

Related Questions