StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSSIS - Is there a Data Flow Source component that will handle CSV files where the column order may change?
text
Body
copied!<p>We have written a number of SSIS packages that import data from CSV files using the Flat File Source. </p> <p>It now seems that after these packages are deployed into production, the providers of these files may deliver files where the column order of the files changes (Don't ask!). Currently if this happens, our packages will fail.</p> <p>For example, an additional column is inserted at the beginning of each row. In this case, the flat file source continues to use the existing column order, which obviously has a detrimental effect on the transformation!</p> <p>Eg. Using a trivial example, the original file has the following content :</p> <pre><code>OurReference,Client,Amount 235,MFI,20000.00 236,MS,30000.00 </code></pre> <p>The output from the flat file source is :</p> <pre><code>OurReference Client Amount 235 ClientA 20000.00 236 ClientB 30000.00 </code></pre> <p>Subsequently, the file delivered changes to :</p> <pre><code>OurReference,ClientReference,Client,Amount 235,A244,ClientA,20000.00 236,B222,ClientB,30000.00 </code></pre> <p>When the existing unchanged package is run against this file, the output from the flat file source is :</p> <pre><code>OurReference Client Amount 235 A244 ClientA,20000.00 236 B222 ClientB,30000.00 </code></pre> <p>Ideally, we would like to use a data source that will cope with this problem - ie which produces output based on the column names, instead of the column order. </p> <p>Any suggestions would be welcomed!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload