Welcome To TechBrothersIT: 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)

Welcome To TechBrothersIT

Label

SSIS - Create Excel File Dynamically In SSIS

Scenario:

Solution :

No comments:

Post a Comment