0

I am trying to create multiple worksheets in excel using ssis. I am using flat file destination for creating the excel file by changing the file extension to xls. I can successfully create multiple excel files but what i am looking to do is to create multiple excel files with multiple worksheets. Can anyone suggest a simple workaround for that. Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
Minhal
  • 85
  • 2
  • 17

2 Answers2

1

You have to use Excel connection Manager / Excel Destination to achieve that, also you may need to utilize from Execute SQL Task to create Excel Worksheets if you cannot do that manually:

Hadi
  • 36,233
  • 13
  • 65
  • 124
0

It is not possible to create multiple worksheets using the Flat File Destination. This can only be done using the Excel Destination, using the technique shown in the linked question above.

Tab Alleman
  • 31,483
  • 7
  • 36
  • 52
  • But is it a way that we can get data from 2 different flat file destination and then merge them into a single file? while using the flat file with a .xls extension. – Minhal Aug 02 '19 at 14:20
  • It can't be done as part of the dataflow, but you can run a script task after all the files are created and programmatically merge the files. It won't be simple. Here is a Q/A that can get you started: https://stackoverflow.com/questions/27285615/how-do-i-merge-multiple-excel-files-to-a-single-excel-file – Tab Alleman Aug 02 '19 at 14:23
  • As of now I am getting all the files created by using 2 different flat file destination and I am getting all the files in the same folder as excel files but the problem is that they are separate files. I am looking to merge both the files in a way that both of these would be worksheets in a single excel file. – Minhal Aug 02 '19 at 14:27
  • I understand that, and my comment above addresses that. – Tab Alleman Aug 02 '19 at 15:13
  • The link you provided is showing the way of merging the excel worksheets however I am using flat files so I dont think this is the way to do it. – Minhal Aug 02 '19 at 15:45
  • It's a necessary step. You have to convert your flat files to Excel files, which you are doing by changing the extension. Then you follow the link to merge the excel files into one file with multiple worksheets. – Tab Alleman Aug 02 '19 at 19:06
  • I am looking to get all these worksheets in a loop where there will be multiple excel files created with multiple worksheets init. Thanks – Minhal Aug 05 '19 at 19:36