Dynamically Create Excel Files depends on Export Data in SSIS

Scenario

     In this Article, we will explain dynamically creation of new Excel files in SSIS depends on Export data from SQL Server table.

Solution

    The solution here was to create new excel file with Department Name at the package ran and export Department names from the Database.

• Let’s create a table with following structure.

CREATE TABLE [dbo].[Emp_Data](

     [Eid] [int] NULL,

     [Ename] [varchar](50) NULL,

     [Sal] [float] NULL,

     [Dept] [varchar](10) NULL

)

GO

• Insert the values into the table of [dbo].[Emp_Data]

insert into Emp_Data values(1,'Ken J',15000,'IT')

insert into Emp_Data values(3,'Rob M',25000,'Coding')

insert into Emp_Data values(4,'Diane L',20000,'DMS')

insert into Emp_Data values(5,'Janice M',12000,'IT')

insert into Emp_Data values(6,'Kevin F',12500,'DMS')

insert into Emp_Data values(2,'Terri Lee',10000,'Coding')

• Create SSIS Package and Create variables as shown

• Take Execute SQL Task from Control Flow pane and configure as shown. The goal is to select the Department name from table and store into Object Type variable.

• Add Foreach loop Container to the package and select “Edit”. On the resulting dialog, click the “Collections” tab in the left-hand pane. On the right-hand pane, select the enumerator type of “Foreach ADO Enumerator”. The bottom portion in the right-hand pane will change. In that lower area, click the drop-down for the “ADO object source variable” and select the variable “User::Dataset”. And the “Enumerator Mode” changes the value in to “Rows in the first table”.

• Now click on the “Variable Mappings” tab of the Foreach Loop Editor and select “User::DeptName” and assign value “0” in Index.

• Let’s take Script task in Control Flow. Choose the variables that we need to use in Script task as shown below.

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

• Use the below script in Script task.The code is coloured in Red. You can copy that and paste in the 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()

{

 // Store the Filepath

 String Filepath = "";           

 Filepath = Dts.Variables["User::ExcelFilePath"].Value.ToString();

 System.Data.DataTable dt = new System.Data.DataTable();

 Dts.Variables["User::ExcelFilePath"].Value =                                     Dts.Variables["User::ExcelFilePath"].Value+""+

                  Dts.Variables["DeptName"].Value + ".xls"  ;

 //Excel sheet

 Excel.Application oXL = new Excel.ApplicationClass();

 Excel.Workbooks oWBs = oXL.Workbooks;

 Excel.Workbook oWB = null;

 /* Set some properties oXL.Visible = true;*/

 oXL.DisplayAlerts = false;

 // Get a new workbook.

 oWB = oXL.Workbooks.Add(Missing.Value);

 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();

 Dts.Variables["User::ExcelFilePath"].Value = Filepath;

 // TODO: Add your code here

 Dts.TaskResult = (int)ScriptResults.Success;

}// End of Main

}// End of Microsoft...

}// End of namespace

• Final Output:

Let’s execute our SSIS Package and see the before and after see the files in the folder.

Before Execution

After Execution

Let me know what you think about this article.