how to import a file when the file added new column, unexpectedly?

Question

I have an etl process that imports a "flat file".  say, it has 2 columns and it is importing fine.

Is there a way to programmatically change the import so that it will know a third column is introduced and import that new column?

Thanks.

 

 

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Answer 1

Have a look at my blog for some ideas.

http://beyondrelational.com/blogs/sudeep/archive/2011/03/09/ssis-script-component-add-missing-column-or-ignore-extra-columns.aspx

---

My Blog    |      Ask Me     

Answer 2

Potentially yes... but I probably wouldn't try to do it.

You'd have to write a .NET script to read the structure of the flat file, check for any changes, then modify the SSIS package using the object model available through .NET. You'd probably also need to modify your destination to include the additional column also. You would then schedule this script in order to modify the package when a change occurs.

The Integration Services Technical Reference includes links to documentation for the SSIS object model classes - http://msdn.microsoft.com/en-us/library/bb522642.aspx

Unless your file structure changes a lot, it's a lot of effort given the small reward.

Alternatively you could read the flat file as a single column, then use a script component to split each line. This would be a lot easier, but you'll still have the issue of modifying the destination etc...

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 3

(it seems u have some constraint in modifying your package)

if you r trying to import the data into a table then 

1) on addition of a new column in the source flat file, the new column will be merged to the last column with a coma "," in between

2) create a new package with the destination table as source

use derived column transformation to get the column value before and after the coma in separate columns 

map these derived columns to the table columns

* this way u wil get the data in the newly added column into a new column in the table without modifying the last package

Answer 4

If you can use third-party solutions, check the commercial CozyRoc Data Flow Task Plus. It is an extension of the standard Data Flow Task, which allows dynamic columns at runtime. The Data Flow Task Plus will interrogate the source file for new columns and if found include in the data flow to import them. No programming skills are required.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 5

good idea, no_prakash and CozyROc.

 

how to add the new column to the table, automatically, at package run time?

 

---

-- IBM has jobs for USA employee to work oversea (India) and get their wages.

Answer 6

you can modify the table and add columns after the completion of once execution cycle.