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

20 comments:

  1. I just feel one should easily try to take up more of some information like this for SSIS and solve some very complex IT problems.This post is heavily appreciated.

    SSIS Postgresql Write

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This is such a great generic solution that it can be reused in so many ways. Very useful and well explained.

    ReplyDelete
  4. Took me time to read all the comments, but I really enjoyed the article. It proved to be Very helpful to me and I am sure to all the commenters here! It’s always nice when you can not only be informed, but also entertained! bill tracking sheets

    ReplyDelete
  5. A lot of thanks for all your labor on this web page. Ellie really loves doing internet research and it’s really obvious why. My partner and i notice all relating to the powerful means you present efficient information via the web blog and therefore improve contribution from others on the subject and our simple princess is really understanding a great deal. Take advantage of the remaining portion of the year. You have been carrying out a great job. help with excel spreadsheets

    ReplyDelete
  6. Excellent website! I adore how it is easy on my eyes it is. I am questioning how I might be notified whenever a new post has been made. Looking for more new updates. Have a great day! convert pdf to png

    ReplyDelete
  7. Nice to be visiting your blog once more, it has been months for me. Well this article that ive been waited for therefore long. i want this article to finish my assignment within the faculty, and it has same topic together with your article. Thanks, nice share. 먹튀사이트

    ReplyDelete
  8. Merely a smiling visitant here to share the love (:, btw outstanding style. 바카라

    ReplyDelete
  9. I have bookmarked your blog, the articles are way better than other similar blogs.. thanks for a great blog! facebook ads

    ReplyDelete
  10. I haven’t any word to appreciate this post.....Really i am impressed from this post....the person who create this post it was a great human..thanks for shared this with us. 릴게임

    ReplyDelete
  11. I think this is one of the most significant information for me. And i’m glad reading your article. But should remark on some general things, The web site style is perfect, the articles is really great : D. Good job, cheers 먹튀검증사이트

    ReplyDelete
  12. An fascinating discussion is value comment. I think that it is best to write extra on this matter, it won’t be a taboo topic however generally people are not enough to talk on such topics. To the next. Cheers 먹튀검증

    ReplyDelete
  13. I know this is one of the most meaningful information for me. And I'm animated reading your article. But should remark on some general things, the website style is perfect; the articles are great. Thanks for the ton of tangible and attainable help. 토토커뮤니티

    ReplyDelete
  14. I appreciated your work very thanks 88카

    ReplyDelete
  15. Your content is nothing short of bright in many forms. I think this is friendly and eye-opening material. I have gotten so many ideas from your blog. Thank you so much. textbook answers

    ReplyDelete
  16. To start with, this article will not get into the very advanced automations available in Excel, but it will give a framework that hopefully can be used by others to improve on and make it more expansible. The framework will allow you to create an Excel object and control some of the basic functionalities such as getting worksheet information and extracting data from the worksheet given a range. word translator

    ReplyDelete
  17. Hello, I am one of the most impressed people in your article. 먹튀검증 What you wrote was very helpful to me. Thank you. Actually, I run a site similar to you. If you have time, could you visit my site? Please leave your comments after reading what I wrote. If you do so, I will actively reflect your opinion. I think it will be a great help to run my site. Have a good day.


    ReplyDelete
  18. Your explanation is organized very easy to understand!!! I understood at once. Could you please post about bitcoincasino ?? Please!!

    ReplyDelete
  19. Taking lypo spheric vitamin c has changed my life, particularly when it comes to fighting off cold and flu symptoms. I love how convenient the sachets are, and how the liposomal encapsulation increases absorption. It's fantastic to find a vitamin C supplement that makes full use of the ingredient rather than relying on the more conventional methods.

    ReplyDelete