how to map the columns of a flat file when new columns added to the file using script task with out reset columns button in flat file connection manager in ssis
Hi all, am new to the ssis above is my question, how to refresh metadata of a flat file using script task when a new column added to the flat file the column which i added newly the data is appending with the previous(last) column data and tha column is not recognized in the column window when am trying to map Plzzzzz help me thanks in advance
November 7th, 2012 6:49am
Hi, You can't refresh the meta- data using a Script task. You need to open the package in BIDS, refresh all the data flow components. And an important thing to note is, one you build the package Your file structure can not change at run time. Every time the structure of Source or Destination changes, Manual Refresh is required.Rajkumar
November 7th, 2012 7:30am
Hi, You can't refresh the meta- data using a Script task. You need to open the package in BIDS, refresh all the data flow components. And an important thing to note is, one you build the package Your file structure can not change at run time. Every time the structure of Source or Destination changes, Manual Refresh is required. Rajkumar Not 100% true for flat files. If columns are added, these are just appended to the last column (which may result in a truncation error). So basically you can still run the package and the data gets imported to the table. After the import, you can issue an UPDATE statement that splits the "last column" into the real columns.MCSA SQL Server 2012 - Please mark posts as answered where appropriate.
November 7th, 2012 7:57am
Hi Koen, Basically my requirement is, even though meta data of the source file (especially .CSV,.Txt) is changed the package should not fail and it should give the expected out put. so for that what kind of measures or precautions need to be taken in order to not to change the deployed packages in Production environment. Kishor.
November 8th, 2012 1:04am
As I said before, when you add a new column to the flat file, that new column is appended to the value of the previous last column. So you need to do some extra parsing to get it out. Be sure to test everything rigorously. However, if the destination table changes its layout, it can still crash the SSIS package. SSIS doesn't handle changing metadata well. As in not at all. If it doesn't work out, you might need to look into 3rd party components such as CozyRoc or into scripting the whole thing out.MCSA SQL Server 2012 - Please mark posts as answered where appropriate.
November 8th, 2012 1:53am
You don't use a script task for this. You use the "Reset Columns" button in the flat file connection manager "Columns" tab. Something tells me you aren't asking the right question, can you give more details about what you are trying to do?
November 8th, 2012 2:01am