Note: If Installing Excel Interop is not option, I will suggest to use posts at the end of article.
INSERT into dbo.Sale
Create view dbo.vw_Sale
Select CountryName,Sale from dbo.Sale
As you have noticed that I choosed only two columns from source table those are CountryName and Sale.
Create SSIS Package and create variables as shown
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.
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
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 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 Excel = Microsoft.Office.Interop.Excel;
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
#region VSTA generated code
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
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();
Excel.Application oXL = new Excel.ApplicationClass();
Excel.Workbooks oWBs = oXL.Workbooks;
Excel.Workbook oWB = null;
oWB = oWBs.Count > 0 ? oWB = oWBs : 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();
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
oWB.Close(false, Dts.Variables["User::ExcelFilePath"].Value, Missing.Value);
// TODO: Add your code here
Dts.TaskResult = (int)ScriptResults.Success;
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
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.
- How to Load Data from Excel Files when Number of Columns can decrease or order is changed in Excel Sheet
- How to Load Only Matching Column Data to SQL Server Table from Multiple Excel Files (Single Sheet per file) Dynamically in SSIS Package
- How to Load Excel File Names with Sheet Names ,Row Count,Last Modified Date, File Size in SQL Server Table
- How to Load Multiple Excel Files with Multiple Sheets to Single SQL Server Table by using SSIS Package
- How to Load Matching Sheets from Excel to Table and Log Not Matching Sheets Information in SQL Server Table
- How to create Table for each sheet in Excel Files and load data to it dynamically in SSIS Package
- How to Create Table per Excel File and Load all Sheets Data Dynamically in SSIS Package by using Script Task
- How to create CSV file per Excel File and Load All Sheets from Excel File to it in SSIS Package
- How to Create CSV File for Each Excel Sheet from Excel Files in SSIS Package
- How to Load Excel File Name and Sheet Name with Data to SQL Server in SSIS Package
- How to Import data from Multiple Excel Sheets with a pattern of sheet names from Multiple Excel File in SSIS Package
- How to import Data from Excel Files for specific Sheet Name to SQL Server Table in SSIS Package
- Load Data To Tables according to Excel Sheet Names from Excel Files dynamically in SSIS Package
- How to Load Excel Files with Single/ Multiple Sheets to SQL Server Tables according to Excel File Name Dynamically
- How to Read Excel Sheet Data after Skipping Rows in SSIS Package by using Script Task
- How to read data from Excel Sheet and Load to Multiple Tables by using Script Task in SSIS Package
- How to create Excel File Dynamically from SQL server Table/View by using Script Task in SSIS Package
- How to create Excel File Dynamically for Stored Procedure Results in SSIS Package by using Script Task
- How to Export SQL Server Tables from Database to Excel File Dynamically in SSIS Package by using Script Task
- How to Convert CSV/Text Files to Excel Files in SSIS Package by using Script Task
- How to Load All CSV Files to Excel Sheets ( Sheet Per CSV) in single Excel File in SSIS Package
- How to Load All CSV Files to Single Excel Sheet with File Names in an Excel File Dynamically in SSIS Package
- How to Create Sample Excel file with Sheet from each table with Top 1000 Rows per sheet in SSIS Package
- How to Export Data to Multiple Excel Sheets from Single SQL Server Table in SSIS Package
- How to split large table data into multiple Excel Sheets on Single Excel File by using SSIS Package
- How to Export All tables of a database to Excel Files with Date-time in SSIS Package