0

I have to create an SSIS package which will simply copy the data from SQL table to an Excel file. The catch is I need a merged column for two columns in the first row and rest of the columns in the second row. Is this possible without keeping a template?

Screenshot:

enter image description here

Please advice! Thanks

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aakriti
  • 11
  • 1
  • 2
    Create a template file first, with the headers you need, and then export the data from SSIS headerless. You may want to copy the file, using a System File Task, first and then export to that one if you want to preserve the template. – Thom A May 18 '20 at 12:28
  • Alternatively, you might be better off using Reporting Services, as then you are in control of the presentation at design time. – Thom A May 18 '20 at 12:33
  • I understand that SSRS is better for such presentations, but then I will need to run this SSRS report using SSIS, because that is the requirement that this report will be called every sunday. Is this possible to run an ssrs report using ssis?? – Aakriti May 18 '20 at 13:08
  • Yes, running a (scheduled) SSRS report is just T-SQL, and SSIS has Execute Transact-SQL Control nodes. – Thom A May 18 '20 at 13:09
  • Can you please explain more. – Aakriti May 18 '20 at 13:12
  • @Aakriti, that would be a new question. This isn't a discussion forum. Research the functionality. Give it a try. If you run into problems, come on back and ask that question. – Eric Brandt May 18 '20 at 13:14
  • The Execute Transact-SQL Task is quite a fundamental part of SSIS, @Aakriti . If you aren't familiar with it, I suggest familiarising yourself with SSIS first. – Thom A May 18 '20 at 13:16

1 Answers1

0

Even though excel templates as destination will be the best solution, if you cannot go for excel templates you can think of below options:

  • Save the open xml template as a variable.
  • Get the result into recordset destination. Go for looping and Fill the rows inside Open xml template with recordset content, using script task, using C#. Save the xml as .xlsx file.
  • Get the result into recordset destination. Have script task and Fill all the rows inside Open xml template with recordset content, using C#. Save the xml as .xlsx file.

Refer to this SO post

Venkataraman R
  • 12,181
  • 2
  • 31
  • 58