0

I have a scenario where my source can be on different versions of our database as a result the in source file I could have different number of columns while my destination have defined number of columns. now what we are trying to do is:

load data from source to flat files. move them to central server and then load that data into central database. but if any column is missing in flat file i need to add derived column.

what is the best way to do this?? how can i dynamically add derived columns?

Radhi
  • 6,289
  • 15
  • 47
  • 68
  • If you set up your package to load data from a file with seven columns and it finds a file with six columns your package with fail. You may wish to investigate BIML which allows you to autogenerate a package per file. – Nick.Mc Apr 13 '16 at 06:05
  • As @Nick.McDermaid says, why add derived columns dynamically when you can create the entire SSIS package dynamically? Refer - [Here](http://www.sqlservercentral.com/articles/Integration+Services+(SSIS)/130470/) – freakyhat Apr 13 '16 at 10:47

1 Answers1

0

You can either do this with BiMLScript as other have suggested in comments, or you can write a script task that reads the file, analyzes the contents, and imports it. Yet another option would be to bulk import the file as is to a staging table (that would have to be dropped and re-created everytime) and write a stored procedure that analyzes the DDL and contents, and imports data to the destination table.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52