SSIS Package

• tripri

  SSCommitted

  Points: 1584

  More actions

  March 22, 2011 at 1:15 pm

  #389110

  i need to design a ssis package which pulls data from excel sheet to a database. That is not a big problem for me but in that i got one issue.

  that excel sheet will get updated weekly so i need to automate that package weekly so here some times when excel sheet gets updated some new columns may be added to that.

  So now i need to design the package in such a way that if any new columns are added to excel sheet all that columns should be added to the DB also and the main thing is it should be an automated process. Can some one help me in this please.

• Koen Verbeeck

  SSC Guru

  Points: 259015

  More actions

  March 23, 2011 at 3:48 am

  #1302342

  You can't do that with out of-the-box SSIS.

  The dataflow in SSIS is strongly typed about metadata, so you just can't dynamically add columns to your dataflow.

  Alternatives are:

  * use a 3rd party component such as CozyRoc

  * write a dynamic SQL query to the Excel file using the Execute SQL Task (you can set the source type to Excel)

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• Gosta Munktell

  SSCertifiable

  Points: 5973

  More actions

  March 24, 2011 at 2:50 am

  #1302831

  I can do that for you. I use VBA, ADO and T-SQL.

  I will not put any code here because it is more than a "small work"

  to create and test the code and I need more information.

  I you like send me a private E-mail.

  Best luck

  Gosta M