Flat File Import, Ignore Missing Columns?

Question

The text files I'm importing always contain a fixed set of columns for example total number of full set of columns is 60, or a subset of those columns (some csv contain 40 columns, some contain 30 or 20 or any other number.) .  I would like to import these csv based on the column header inside the each csv, if it is a subset of full column set then the missing columns can be ignored with null value.

At the moment in SQL 2012, if I import a subset of columns in the csv file, the data doesn't import...I assume because the actual file doesn't include every column defined in the flat file source object? 

Is it possible to accomplish this without dynamically selecting the columns,  or using script component?

Thanks for help.

---

Sea Cloud

Answer 1

Try below link

http://dwbi1.wordpress.com/2011/03/05/ssis-importing-a-file-with-dynamic-columns/

http://www.citagus.com/citagus/blog/importing-from-flat-file-with-dynamic-columns/

http://agilebi.com/jwelch/2007/05/08/handling-flat-files-with-varying-numbers-of-columns/

http://www.timmitchell.net/post/2013/01/14/ragged-flat-file-processing-in-ssis/

Thanks

Saravana Kumar C

Answer 2

If the columns coming are dynamic, then you might have to first get it into staging table with a single column reading entire row contents onto that single column and parse out the column information using string parsing logic as below

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

This will help you to understand what columns are present based on which you can do the insertion to your table.

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs