Incorrect Row Delimiters

Question

Incorrect Row Delimiters

Greg Roberts-134214

SSCommitted

Points: 1686
More actions
November 2, 2010 at 9:52 am

#225748

My source file is a pipe delimited text file.
When a row does not contain all of the columns (i.e. 5 instead of 6 columns) SSIS is appending the 1st column of the following row to the last column of the current row. And the remaining columns of the following row are inserted into the final column of the current row.
Sample Input File (The first row of data is missign a delimiter for the NickName column):
ID|FirstName|LastName|NickName|Action|ActionDate
1236|Fred|Flinstone|Update|11/1/2010
1234|Gregory|Roberts|Greg|Insert|11/1/2010
1235|Joe|Blow||Insert|11/1/2010
1237|Donald|Duck|Quacker|Insert|11/2/2010
1238|Scooby|Doo|Scoob|Updated|11-1-2010
The dataviewer output is attached. It shows the record for Fred Flinstone as
1236FredFlinstoneUpdate11/1/2010 1234Gregory|Roberts|Greg|Insert|11/1/2010
The remaining rows import correctly.
Also attached is the General tab of the Connection Manager for the Flat File.
Any hints as to where the import is going haywire?
Thanks in advance,
Greg
Greg Roberts

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

It really sounds like less of an import problem, and more of a "Garbage-In, Garbage-Out" issue. The delimited file is missing a delimiter, so the file is Garbage. You really need to fix the source, not try to fix it somewhere else. After all, how would you (programatically) know which delimiter is actually missing?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 2

I agree, fixing the source is the ultimate solution. Unfortunately the source is an outside 3rd party who has been quoted say "The specification is more of a guidline." And more recently admitted the published specification contains errors.

Thsi means I have to build more robust error handling into my import, and redirect bad rows to an error file. the problem is, SSIS is not recognizing a bad row.

Greg Roberts

WayneS SSC Guru Points: 95460 More actions · Answer 3

Okay, then how about this. Don't worry about the delimiters yet... first load the file into a staging table. Go through this, counting the number of delimiters. If it's less than what you need, move the record to a error file.

Now, use what's left in the table - it should now pass through.

In the event that you now need it in columns, use the DelimitedSplit8K function (Click here for the latest Delimited Split Function) to return a result set of columns for each delimited item.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 4

That's close to the path i am on. Challenge? 49 files. 15 wider than 80 columns. :ermm:

Blasted vendor! :angry:

Greg Roberts

fazilu SSC Veteran Points: 232 More actions · Answer 5

HI Greg,

you can try a work around by putting a conditonal split just after the flat file soruce and checking for condtion like

FINDSTRING([ActionDate ],"|",1) <= 0 (you can add other coloumn here) and move false condtion rows to error file and continue with true condtion

regards

Fazil

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 6

What i've done is load the file into a single column table. Then count the occurance of the | delimiter. If it is not equal to the number of columns, the row is moved to an error file. the remaining rows can then be parsed.

Royal PITA, but it works.

Greg Roberts