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
  • 34,802
  • 21
  • 91
  • 134
Vikas Kunte
  • 683
  • 4
  • 15
  • 35

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
  • 34,802
  • 21
  • 91
  • 134
  • 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
  • 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