SSIS Pipe delimited file not failing when the row has more number pipes than the column number?

0
votes

My Source File is (|) Pipe Delimited text file(.txt). I am trying load the file into SQL Server 2012 using SSIS(SQL Server Data Tools 2012). I have three columns. Below is the example for how data in file looks like.

enter image description here

I am hoping my package should fail as this is pipe(|) delimited instead my package is a success and the last row in the third column with multiple Pipes into last column.

My Question is Why is't the package failing? I believe it has corrupt data because it has more number of columns if we go by delimiter? If I want to fail the package what are my options,If number of delimiters are more than the number columns?

enter image description here

2
sqlsql-serverssisssis-2012
The package is working as intended. It just assumes everything past the first pipe is part of the data for that column. You can wrap your data double quotes to prevent this from happening. – TTeeple
Data Source is from outside vendor. the standard file format is Pipe(|) Delimited. Is there a work around to fail the package? – RavikG

2 Answers

2
votes

You can tell what is happening if you look at the advanced page of the flat file connection manager. For all but the last field the delimiter is '|', for the last field it is CRLF.

So by design all data after the last defined pipe and the end of the line (CRLF) is imported into your last field.

What I would do is add another column to the connection manager and your staging table. Map the new 'TestColumn' in the destination. When the import is complete you want to ensure that this column is null in every row. If not then throw an error.

You could use a script task but this way you will not need to code in c# and you will not have to process the file twice. If you are comfortable coding a script task and / or you can not use a staging table with extra column then that will be the only other route I could think of.

A suggestion for checking for null would be to use an execute sql task with single row result set to integer. If the value is > 0 then fail the package.

The query would be Select Count(*) NotNullCount From Table Where TestColumn Is Not Null.

0
votes

You can write a script task that reads the file, counts the pipes, and raises an error if the number of pipes is not what you want.