|
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!
(comments are locked)
|
|
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.
(comments are locked)
|
I'd listen to your colleague on this one.