Export to Excel Dynamic Number of Columns

We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.

I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.

Appreciate any input


September 11th, 2015 4:49pm

Hi Jason,

Did you try to use a template (Excel file with the header prepared)?

If the ad-hoc kills the metadata of the template then SSIS still is not the best choice for you and I suggest you resort to developing such an application using Visual Studio for Office kind of a project, or use specialized controls/libraries as Aspose.

Free Windows Admin Tool Kit Click here and download it now
September 11th, 2015 5:19pm

Have you seen the commercial COZYROC Data Flow Task Plus ? It is an extension of the standard Data Flow Task with support for dynamic columns at runtime. You can accomplish the exact scenario you are describing. No programming skills are required. I would also recommend you review the demo videos.

September 11th, 2015 10:23pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics