Hi Experts, Need a solution for ever changing Flat file from SAP Reports. The change will be in the columns i.e the columns may reorder e.g. Name which appears in 1st column one input file may change to 5th or 10th position in the next file. Since i'm new to SSIS finding it difficult to address this and i have already committed to my client that SSIS will handle this kind of change since we'll be mapping the column names to the destination not the column index. Please help me.. Thanks in Advance. --Sandheep

Hi Sandheep, SSIS will not handle non-static metadata w/o changes into it. Your only approach is to generate the package each run programmatically using code, then run it. Question is how do you know what column needs to go to what target.Arthur My Blog

Hi Arthur, Thanks for the early reply, as for rewriting the package is concerned i'm not sure this is a common phenomenon right, there has to be a way to do this. The column names will not change but only the column index. Can i just dump the data in some temp table on insert trigger copy the data to the relevant destination table. Sandheep S Kulkarni

How would you dump ever changing column positions? It is a catch 21 situation, you never know in advance what movement occurred. You may write a parsing script. E.g. such a script would read the column names, then re-arrange the data file to conform to the "pre-defined" structure, and then load, yes, but pre-processing of some kind will be always part of the design. Arthur My Blog

As Arthur stated, once a flat file connection is configured any metadata changes are not dynamically refreshed. The only way of handling dynamic column positions would be via a script component where you would have to manually parse the columns and add it to the output buffer.http://btsbee.wordpress.com/

Right, the input file is a huge file using a script will consume time and performance will take a hit here.Sandheep S Kulkarni