SSIS - Create Excel File Dynamically In SSIS

Scenario: 

We want to create a SSIS Package to load data from Database but the number of columns can change any time. We do not want to open our SSIS Package and do remapping for our Excel destination. This type of situation can happen when we have Dynamic Pivot query results those needs to export to Excel and we are not sure about the number of columns.

Note: If Installing Excel Interop is not option, I will suggest to use posts at the end of article.

Solution :

As the number of columns will be changing anytime, we can write a stored procedure or View that will return us the desired output. We will save these results in Object Type variable in SSIS and then use Script Task to write to Excel destination. 

Step 1: 

Let's create view from our source table/tables. 

 --Test Table Definition and Some Sample Data

Create table dbo.Sale ( ID INT, RegionCD VARCHAR(100), CountryName VARCHAR(100), SalePersonName VARCHAR(100), Sale INT)
go
INSERT into dbo.Sale
Values(1,'Asia','Pakistan','Aamir',1000)
,(2,'Asia','India','Sukhjeet',2000)
,(1,'US','USA','Mike',3000)

--View Definition
Create view dbo.vw_Sale
AS
Select CountryName,Sale from dbo.Sale

As you have noticed that I choosed only two columns from source table those are CountryName and Sale.

Step 2:
Create SSIS Package and create variables as shown

Step 3: 
Bring Execute SQL Task to Control Flow pane and configure as shown. The goal is to Select all records from view and store into Object Type variable.

Step 4:
Bring Script task to Control Flow Task and connection Execute SQL Task to it. Choose the variables that we need to use in Script Task as shown below

Step 5:
Click on Edit Script and then we need to add reference to Microsoft.Office.Interop.Excel.dll

Step 6:
Use the below script in Script task. The Code I added in RED. You can copy that and paste in your Script Task.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.Reflection;
using System.Diagnostics;

namespace ST_825e524384ad45d6994d16bda6651279.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

        public void Main()
        {
            OleDbDataAdapter A = new OleDbDataAdapter();
            System.Data.DataTable dt = new System.Data.DataTable();
            A.Fill(dt, Dts.Variables["User::VarObjectDataSet"].Value);
           
            //Excel sheet
            Excel.Application oXL = new Excel.ApplicationClass();
            Excel.Workbooks oWBs = oXL.Workbooks;
            Excel.Workbook oWB = null;
            Excel.Worksheet oSheet;
         
            oWB = oWBs.Count > 0 ? oWB = oWBs[0] : oWBs.Add(System.Reflection.Missing.Value);
        

            /* Set some properties oXL.Visible = true;*/
            oXL.DisplayAlerts = false;
            // Get a new workbook. 
          oWB = oXL.Workbooks.Add(Missing.Value);
            // Get the active sheet 
            oSheet = (Excel.Worksheet)oWB.Worksheets.get_Item(1);
            oSheet.Name = Dts.Variables["User::SheetName"].Value.ToString();


            int rowCount = 1;
            foreach (DataRow dr in dt.Rows)
            {
                rowCount += 1;
                for (int i = 1; i < dt.Columns.Count + 1; i++)
                {
                    // Add the header time first only
                    if (rowCount == 2)
                    {
                        oSheet.Cells[1, i] = dt.Columns[i - 1].ColumnName;
                    }
                    oSheet.Cells[rowCount, i] = dr[i - 1].ToString();
                }
            }

                oWB.SaveAs(Dts.Variables["User::ExcelFilePath"].Value, Excel.XlFileFormat.xlWorkbookNormal,
                    Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                    Excel.XlSaveAsAccessMode.xlExclusive,
                    Missing.Value, Missing.Value, Missing.Value,
                    Missing.Value, Missing.Value);
                oWB.Close(false, Dts.Variables["User::ExcelFilePath"].Value, Missing.Value);
                oWBs.Close();
                oXL.Quit();


            // TODO: Add your code here
            Dts.TaskResult = (int)ScriptResults.Success;
        }
    }
}

Final Output: 
Let's execute our SSIS Package and see if the excel file is created.

As we can see that the Excel destination file is created with two columns.


Let's change the definition of our view and run the SSIS Package again without making any change to Package.

Alter view dbo.vw_Sale
AS
Select RegionCD,CountryName,SalePersonName,Sale from dbo.Sale

As we can see that the Excel destination file has all the columns which are included in View definition.

Excel Source and Destinations (Script Task- Dynamic)

1. How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
2. How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
3. How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
4. How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
5. How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
6. How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
7. How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task 
8. How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
9. How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
10. How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
11. How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
12. How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
13. Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
14. How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
15. How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task 
16. How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
17. How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
18. How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
19. How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
20. How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
21. How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
22. How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
23. How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
24. How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
25. How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
26. How to Export All tables of a database to Excel Files with Date-time in SSIS Package