Vendor Changed the Flat File Format

Question

Vendor Changed the Flat File Format

Vertigo44

SSCarpal Tunnel

Points: 4055
More actions
March 23, 2015 at 12:55 pm

#398224

My Source connection was set to look for 5 columns with the last column delimiter expecting a CR/LF. However, our vendor continues to add additional columns to the flat file so the CR/LF is not encountered until a few columns later.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Vertigo44 (3/23/2015)
My Source connection was set to look for 5 columns with the last column delimiter expecting a CR/LF. However, our vendor continues to add additional columns to the flat file so the CR/LF is not encountered until a few columns later.

Is this just a rant?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 2

Sorry Phil. No.

I found a possible solution to dynamic flat file columns using...

http://ssisdfs.codeplex.com/

and a script task component. I am going to try to implement it and see if it saves my butt.

Thanks!

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

Welcome to the real world!

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 4

Vertigo44 (3/23/2015)
Sorry Phil. No.
I found a possible solution to dynamic flat file columns using...
http://ssisdfs.codeplex.com/
and a script task component. I am going to try to implement it and see if it saves my butt.
Thanks!

Do you have the knowledge to maintain that code? You might want to consider not using it, then. Even the author says he's having problems with performance.

You haven't explained WHAT you want to do with the extra columns. Import them to a staging table? Ignore them? What???

--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)

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 5

Oh yes that might have been helpful!

So we have 5 columns that we will always want from that file. That is fixed. However as you know if the flat file connection manager the last column looks for a CR/LF as the row delimiter. But when the vendor adds extra columns the CR/LF get bumped to the right and my connection manager gets screwy so I'd like to figure out a way to just ignore any additional columns in that file if possible. I don't care about them.

I was thinking I could import the entire FF into a table then run a select against it pulling out only the columns that i am interested in and pulling it that way?

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

Assuming that you're using SSIS and your main problem is that your fifth/last column is including unwanted columns, I would suggest that you use a derived column to use only the portion needed with substring.

Something like this:

SUBSTRING(MyLastColumn, 1, FINDSTRING(MyLastColumn + ",", ",", 1))

Be sure to assign enough length to your last column in your Source Connection.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

Why are you even accepting the file? Notify the vendor that the file format is set and that alterations are not tolerated. Its clear, if they are adding data that isn't being used they are doing nothing but breaking the process.

Take a hard line, files not following the spec are rejected.

CEWII

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 8

I strongly agree with Elliott's take on pork-chopping the vendor for making unagreed upon changes but there's a way around this. If the files have no PII or proprietary information, could you attach one each of before and after columns were added and identify the 5 columns that you want to consistently import?

--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)

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 9

Elliott Whitlow (3/23/2015)
Why are you even accepting the file? Notify the vendor that the file format is set and that alterations are not tolerated. Its clear, if they are adding data that isn't being used they are doing nothing but breaking the process.
Take a hard line, files not following the spec are rejected.
CEWII

I like your idea, but you can't always do that. The 3rd party I'm now working with would say that if we don't like their format then we can simply stop using their data. That is not an option for us. Resistance is futile; we have to adapt.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 10

Yes, I just stepped into this environment not too long ago but apparently this has been happening with the vendor for over 2 years now. Its been brought up but nothing ever happens on their end. So at this point I think I should just come on with a pre-check / validation process that runs prior to the actual job as some sort of warning.

Sqlraider SSCertifiable Points: 5441 More actions · Answer 11

What is in the contract with the vendor? Are you the customer? If your company is paying alot of $$ and then paying you to code work arounds they are not getting any ROI. Showing your company how to save $$ is always a good thing in my book.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 12

Vertigo44 (3/24/2015)
Yes, I just stepped into this environment not too long ago but apparently this has been happening with the vendor for over 2 years now. Its been brought up but nothing ever happens on their end. So at this point I think I should just come on with a pre-check / validation process that runs prior to the actual job as some sort of warning.

Like I said, if the file has no proprietary or PII in it, can you attach it both before and after extra columns were added so I can show you a way to overcome this problem?

--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)

Vertigo44 SSCarpal Tunnel Points: 4055 More actions · Answer 13

Hi Jeff,

See attached. I only included the file headers in the file as I think that's all your really need to see what is taking place. If the FF connection mgr is configured to use the before file and then you modify the file to the after file (adding two additional columns to the end you will see the last column will have two commas in it ",,"). Thanks for your help. Let me know if this is what you needed. I'm not a regular here.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Vertigo44 (3/24/2015)
Hi Jeff,
See attached. I only included the file headers in the file as I think that's all your really need to see what is taking place. If the FF connection mgr is configured to use the before file and then you modify the file to the after file (adding two additional columns to the end you will see the last column will have two commas in it ",,"). Thanks for your help. Let me know if this is what you needed. I'm not a regular here.

So, which of those columns are the 5 columns that you want to keep?

And, yeah, I can do this with just the headers but it means that the solution won't be as tested as I like to post. I may just make a smaller example and then you can modify it for your needs. I still need to know which 5 columns you want to keep, though.

--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)