How to export a large number of columns into non existing excel file.
Developing an SSIS package to export to an existing Excel file is easy when the Excel file exists with column headings on the excel sheet. If you have an export that will be scheduled on a daily basis of 50+ columns to a new excel file, then you have some careful typing/copy-pasting to do to create an excel template file. Instead.
What I often do is to generate an SSIS package with the Sql Server Management Studio export wizard. The interesting thing about this wizard is that it can generate excel files on the fly.After saving the package thus generated by SSMS, you can open the SSIS package in Visual Studio to inspect what it is doing.
The package has a dataflow task, but it is preceded by a SQL task. The trick lies in the SQL statement that generates the columns for the Excel sheet. When we edit the Preparation SQL Task 1, then we see a SQL statement that creates a table called ‘Sheet’.
CREATE TABLE `Query` (
I can use the package generated by SSMS to adapt to my needs, for instance to generate a series of excel filenames in a For Each Loop Container or in a For Loop container, store theEexcel filename in a variable and put the Preparation SQL task and the Dataflow task in the container. I will still need the Excel file generated by the SSMS package to further design my Dataflow task. But once the package is deployed it will use the Excel variable name in the Excel connection.
This solution does not take into consideration the problem of Numeric values getting converted to text in the excel sheet.