x

Why Does't SSIS Flat File Data Check If Columns Names or Order Have Changed? What is best way to check?

I guess I've always assumed that the SSIS Flat File data source checks that either the column names or the order of the columns has changed whenever it imports a new file -- or at the very least when I change the File Name -- otherwise, why does it have a checkbox "Column names in the first row"? (please don't tell me it's just to know to skip that first row!). But now I've found out the hard way that this is NOT true. My data provider (an outside vendor) can change the order and name of any and all fields, and as long as the data types match, I will have NO indication that this happened! How in the world do you guys make sure this doesn't happen with an automated job? A colleague suggested I build a separate package that just imports the first row of the file (the headers) and compare that to a string variable. Good idea, but is this the best way? Thanks!

more ▼

asked Mar 22, 2010 at 06:11 AM in Default

ScottEdwards2000 gravatar image

ScottEdwards2000
56 2 2 3

I'd listen to your colleague on this one.
Mar 22, 2010 at 10:13 AM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

I can't tell you what the best way to handle that is, as SSIS is not my strong suit. But I can tell you that the 'Column names in the first row' option is used at configuration time to configure the mappings between the data file and the database. It is expected that the format of a flat data file is defined and static (i.e. columns do not change).

The way I would do it is to create a pre-processor, which checks the format of the file before it goes anywhere near SSIS. If the file is correct, it would allow the package to continue, if the file was incorrect, it would move it to a holding error and email somebody / raise an alert. That would probably be about 20 lines of C# code. As to whether that is the 'best way' to do that, I'm not sure. Just my 2 cents.

more ▼

answered Mar 22, 2010 at 06:23 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x937
x66

asked: Mar 22, 2010 at 06:11 AM

Seen: 3269 times

Last Updated: Mar 22, 2010 at 06:11 AM