Ssis Read Excel File and Copy Data to Object Variable

Scenario:

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

Note: If Installing Excel Interop is not option, I will advise 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. Nosotros will save these results in Object Blazon variable in SSIS and then use Script Task to write to Excel destination.

Step 1:

Let's create view from our source tabular array/tables.

--Test Table Definition and Some Sample Data

Create table dbo.Auction ( ID INT, RegionCD VARCHAR(100), CountryName VARCHAR(100), SalePersonName VARCHAR(100), Sale INT)
go
INSERT into dbo.Sale
Values(one,'Asia','Islamic republic of pakistan','Aamir',chiliad)
,(2,'Asia','India','Sukhjeet',2000)
,(1,'U.s.','USA','Mike',3000)

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

As y'all have noticed that I choosed just 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 Menses pane and configure equally shown. The goal is to Select all records from view and store into Object Blazon variable.

Step 4:
Bring Script task to Control Flow Task and connectedness Execute SQL Task to information technology. Choose the variables that we need to utilize in Script Chore as shown below

Pace 5:
Click on Edit Script and then nosotros need to add reference to Microsoft.Role.Interop.Excel.dll

Step 6:
Apply the below script in Script task. The Lawmaking I added in Carmine. You can re-create 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 Organisation;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
using System.Information.OleDb;
using System.Reflection;
using Organization.Diagnostics;

namespace ST_825e524384ad45d6994d16bda6651279.csproj
{
[Organization.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Clarification = "")]
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
};

Share this post