Flat File Connection Mgr / EOL

Question

Flat File Connection Mgr / EOL

BSavoie

SSCertifiable

Points: 6309
More actions
March 8, 2012 at 3:46 pm

#391760

Ran into a problem trying to import a flat file. We are getting these flat files from different vendors. They all use the same format, but It tuns out that one vendor is using CRLF for end of line, but another uses only LF. This is causing a problem for the SSIS Flat File Connection Mgr. Any way around this?
Thanks
.

Viewing 14 posts - 1 through 13 (of 13 total)

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 1

Unless anybody has a better idea, I'm going to write a script task to pre-process the input file and strip out the CR/LF's before the import. I'm not sure why these sorts of files would have CR/LF's in the first place, but I already know it would be a big mess asking the vendor to fix the files. I might as well just deal with it.

.

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 2

BSavoie (3/8/2012)
I'm not sure why these sorts of files would have CR/LF's in the first place

Actually, it's a form of "grouping" that can help you ensure that you don't have partial rows in the file. Yep, I agree... fixed field lengths help out on that little problem alot but it's a real PITA when (and it'll happen to you someday... I can almost guarantee it) you get to the end of a file and find there aren't enough "fields" left for the last row.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Can't you put the row delimiter to just LF and filter out possible CR's in a derived column?

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

Yadava Shettigar SSC-Addicted Points: 400 More actions · Answer 4

In my last project I also faced same issue..!

In expression designer of flat file connection manager there is a option for row delimiter and header delimiter which will pick up from variable dynamically if u set..But unfortunately its not working... 🙁

So we found work arround by creating two data flow tasks one is for LF and another for CRLF...based on expression of variable data will flow into appropriate dataflow.

BSavoie SSCertifiable Points: 6309 More actions · Answer 5

Yadava. I like your solution a lot, it's just that the existing data flow that I would have to clone is pretty complicated. I'd hate to turn this into a two headed monster. That's what you are suggesting correct?

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 6

Seems like the best idea I've heard. I'm just not grasping how a derived column gets me around the problem. Can you give me a little bit more detail?

Thanks!

.

Yadava Shettigar SSC-Addicted Points: 400 More actions · Answer 7

yes you are right. ..if data flow is complicated and another data flow before main data flow and take original flat file and convert to one format ...

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

BSavoie (3/9/2012)
Seems like the best idea I've heard. I'm just not grasping how a derived column gets me around the problem. Can you give me a little bit more detail?
Thanks!

Use the following expression to remove CRs from the data:

REPLACE(myColumn,CHAR(13),"")

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

BSavoie SSCertifiable Points: 6309 More actions · Answer 9

Ahhh! I see. Great idea. I'll give that a try. Thanks!

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 10

Wait a minute. My problem is happening very early on in the pipeline, in the flat file connection mgr. Any fix I would implement would have to happen prior to the connection mgr reading the record. That's my whole problem right is the connection mgr must know the file layout right off the bat, and I essentially have two different layouts. The derived column would not happen until after the connection mgr read the row from the file. By then it's too late. Or am I missing something?

.

Yadava Shettigar SSC-Addicted Points: 400 More actions · Answer 11

let us know whether above replace function will help ....because connection manager will accept either cr lf or lf only its mandatory to define beginning itself ...

BSavoie SSCertifiable Points: 6309 More actions · Answer 12

I don't see how I can make it work.

My script task to pre-process the file works pretty well but it doesn't seem real professional. At least it's a backup plan if I can't come up with something better.

Appreciate the help.

.

BSavoie SSCertifiable Points: 6309 More actions · Answer 13

This whole SSIS / Upsert is tricky business. I might be able to help the next bloak. I need to write a clear article on SSIS Upserts and spare the poor souls who like me thinks a trusty OLD OLE DB Command wired up to a Lookup will make all their dreams come true. I'll try and tackle this "fluff piece" this weekend.

.