3

Can any one help me out how can i merge excel files having same columns which are saved in one folder to one destination excel file using SQL Server Integration services(SSIS) .

Diego
  • 33,213
  • 18
  • 87
  • 131
Vikas Kunte
  • 593
  • 4
  • 13
  • 34

1 Answers1

1

add an Excel source for each of your files an a "union all" task to join them, so if you have 20 rows on your first excel and 30 on the second, you will end up with 50 rows:

enter image description here

to set the source and destination paths, just double click each of the tasks and set the connection manager

EDIT:

example of how to read an excel file here

Diego
  • 33,213
  • 18
  • 87
  • 131
  • Thanks Diego. But i am new to SSIS. Where can i get step by step procedure to achieve above task – Vikas Kunte May 21 '12 at 13:25
  • that is the step by step procedure :) I edited my answer with an example of reading an excel file, just do it twice – Diego May 21 '12 at 13:39
  • What if there are 100 excel files? Creating 100 excel sources is a lengthy procedure. Is there a better way to append data of all 100 excel files to 1 excel file using SSIS? – Vikas Kunte May 21 '12 at 13:41
  • no, then you need to use a foreach loop container class to loop the files, get the file path on a variable and your excel source will read from this variable – Diego May 21 '12 at 13:42
  • Thanks. I know very basics of SSIS, but can u brief me the procedure used to do this task? U can mail me if u have any docs? – Vikas Kunte May 21 '12 at 13:48
  • http://stackoverflow.com/questions/7411741/how-to-loop-through-excel-files-and-load-them-into-a-database-using-ssis-package – Diego May 21 '12 at 13:49
  • Thanks a lot, It looks good.I will try and thanks for the help. – Vikas Kunte May 21 '12 at 13:52
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/11524/discussion-between-vikas-kunte-and-diego) – Vikas Kunte May 21 '12 at 14:02