Excel Report using SSIS without Physical Mapping

Introduction:

In this article, we will see how we can generate Excel Report in SSIS without doing Physical mapping.

Steps:

1.    Create Excel file with one data connection in the Excel Sheet.

2.    Create a sheet in Excel pointing to the data connection.

This will lead to point the connection to the data source, and help create a table in Sheet.

As soon as ok is pressed, the data from the connection is pulled.

We can save this Excel as a template and next time we just need to refresh the connection and the data will be refreshed in the Excel Sheet.

This is the day to day process which requires manual effort, WHAT IF WE CAN AUTOMATE THE REFRESH.

It is possible through SSIS services, and Microsoft Interop libraries.

Steps:

1. Create a SSIS package with a Script task with C#.

Edit the script and include the following code in the Script task

Use below script to refresh the data in the Excel. We need to include the reference for the Interop libraries.

using Excels = Microsoft.Office.Interop.Excel;

string filepath = @"D:\Excel Files\Employee Files.xlsx";

Excels.Application xlApp;
Excels.Workbook xlWorkBook;
Excels.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excels.Application();
xlWorkBook = xlApp.Workbooks.Open(filepath);
xlWorkSheet = (Excels.Worksheet)xlWorkBook.Worksheets.get_Item(1);

xlWorkBook.RefreshAll();
xlApp.ErrorCheckingOptions.NumberAsText = true;
xlApp.DisplayAlerts = false;
xlWorkBook.Save();
xlWorkBook.Close(true, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

Benefits:

1. This process is used if we need to generate custom reports.
2. We can use the existing data in excel sheet to create pivot table, and create custom data formats.

Feel free to contact us if there is any issue.