SSIS Flat File Connection Manager not identifying CR/LF on certain records

Question

SSIS Flat File Connection Manager not identifying CR/LF on certain records

RenzoSQL

Ten Centuries

Points: 1252
More actions
December 9, 2010 at 12:42 pm

#232354

Hello,
Has anyone come across a problem where the Flat File Connection Manager is not recognizing CR/LF on certain records? I am importing a CSV file and on some of the records the CR/LF is not being identifying and running into the next record and messing up the import routine.
Here is an example of the records that I am trying to import:
10.10.10.45,JohnDOE,12/06/2010,01:47:38,RAS,FCCSCDC2,4,10.10.10.45,6,2,7,1,5,5,61,5,64,1,65,1,31,74.23.152.155,66,24.83.152.155,25,311 1 10.10.10.46 12/02/2010 20:43:35 82,44,1441,8,10.10.10.87,12,1400,50,153,51,1,55,1291625258,45,2,40,1,4108,10.10.10.45,4147,311,4148,MSRASV5.20,4160,0xAC5735AB93751042BD3CE73AAFB48CB8,4159,MSRAS-0-XXXXXX-LAPTOP,4120,0x0043414C4C465553494F4E,4294967206,4,4154,Use Windows authentication for all users,4136,4,4142,0
10.10.10.45,johnDOE,12/06/2010,04:21:08,RAS,FCCSCDC2,44,1442,4,10.10.10.45,6,2,7,1,5,6,61,5,64,1,65,1,31,506.169.64.181,66,208.169.66.181,4108,10.10.10.45,4147,311,4148,MSRASV5.20,4160,MSRASV5.10,4159,MSRAS-0-OBEY,4155,1,4154,Use Windows authentication for all users,4129,JohnDOE,4127,4,25,311 1 10.10.10.46 12/02/2010 20:43:35 83,4130,0x63616C6C667573696F6E2E6C6F63616C2F53542D53757065727669736F72732D547261696E696E672F55736572732F456C6C69732C204B6174686572696E65,4149,VPN Policy,4136,1,4142,0
10.10.10.45,JohnDOE,12/06/2010,04:21:08,RAS,FCCSCDC2,25,311 1 10.10.10.46 12/02/2010 20:43:35 83,4130,0x63616C6C667573696F6E2E6C6F63616C2F53542D53757065727669736F72732D547261696E696E672F55736572732F456C6C69732C204B6174686572696E65,6,2,7,1,4149,VPN Policy,4120,0x0043414C4C465553494F4E,4127,4,4129,JohnDOE,4154,Use Windows authentication for all users,4155,1,4136,2,4142,0
10.10.10.45,JohnDOE,12/06/2010,04:21:08,RAS,FCCSCDC2,4,10.10.10.45,6,2,7,1,5,6,61,5,64,1,65,1,31,226.169.66.181,66,206.159.66.181,25,311 1 10.10.10.46 12/02/2010 20:43:35 83,44,1442,8,10.10.10.81,12,1400,50,155,51,1,55,1291634468,45,2,40,1,4108,10.10.10.45,4147,311,4148,MSRASV5.20,4160,MSRASV5.10,4159,MSRAS-0-OBEY,4120,0x0043414C4C465553494F4E,4294967206,4,4154,Use Windows authentication for all users,4136,4,4142,0
The second record is the one that starts the problem and it happens randomly throughout the file as I import data.
Any help is much appreciated.
Thanks!
R.

Viewing 12 posts - 1 through 11 (of 11 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

I'm having a hard time to understand your example. Are those meant to be 4 lines, or 12 lines?

If 4, that won't work. CR/LF is a new line for the CSV format, which essentially means that you have 12 lines, and the lines don't have the same number of columns.

If 12, explain your problem a bit better, because I don't get it.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 2

I saved the first two rows to a text document. Opened with excel and noticed the number of columns in each row are different.

This might present a problem...

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 3

Renzo, you need to backtrack this to the source.

First, open up the raw document in something that can display CR/LF characters, like Notepad++. See if it's missing a LF or a CR. If they just don't exist, the source for the data is improperly generating the data, and that needs to be corrected.

You can't fix this after the fact, you have to go to the source and get it to output properly.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 4

Those are 4 records and at the end of each record there is a CR. I opened the file with word to display the carriage return and each record has one at the end of it, however, the records are always of different size meaning that they all have different number of columns and the end of each record should be identified by the CR.

When created the Flat File Connection Manager and display the columns under Preview, I can see that some of the records have a column that displays the two little square boxes that are supposed to be a CR, but for some reason is not getting identified as one and it runs into the next record, messing up the entire import.

Wish I could post a screenshot so you can better understand the problem.

Thanks all for your help.

R.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 5

If each record has different columns, how do you identify which columns for each record go where, presence of data?

AND

if this is the case, you will need to probably import each record into 1 COLUMN and then use a script task to parse through each record. I have actually did this for a feed where I am receiving different transactions in one file.

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 6

There are only 6 columns that I care about on each record, and every record has it regardless of the size, however at the end of each record if the carriage return is not identified, it runs on to the next record and it happens at random times and not on every record which is the weird part.

Chris Hurlbut SSCarpal Tunnel Points: 4162 More actions · Answer 7

Could there be Line Feeds or Carriage returns within some of the fields?

RenzoSQL Ten Centuries Points: 1252 More actions · Answer 8

I dont see them because when i load the file on word i can see that there is only one carriage return and no line feeds per record.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

RenzoSQL (12/15/2010)
I dont see them because when i load the file on word i can see that there is only one carriage return and no line feeds per record.

You definately need something else than word to look at the file 🙂

Try notepad++ or ultraedit, these allow you to see every character, even in hexadecimal if you want to.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff N. Cantwell SSC Journeyman Points: 87 More actions · Answer 10