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
Let me know what you think about this article.
