SSIS (SQL Server Integration Service) provides a number of convenient tasks to enable data integration. Exporting data from database to Excel file is a common task in ETL (Extract, Transform, Load) projects. Constantly the users/customers may raise format request regarding the Excel extract. To generate formatted Excel through SSIS is not straightforward but still feasible. I will summarize several ways to implement this in this article.

Approach 1: VSTO/Microsoft Office COM APIs

The first approach is to invoke COM APIs in a script task. This will be done by using programing mode to create unmanaged Office Excel objects.

Check out my previous post about this in the following page: <https://kontext.tech/Blog/DotNetEssential/Archive/2010/3/14/93ece962fe2f1bd6e7113a31.html> (in Chinese).

MSDN also provides many examples about how to access Office Interop Objects: <http://msdn.microsoft.com/en-AU/library/dd264733.aspx>

Approach 2: SSIS Data Flow Task by Using Excel Template

A simple direct way is to create one formatted Excel template and use it as the Oledb connection destination and directly insert data into it. Modify the properties of the Excel Destination Component and set Access Mode to "Open RowSet", then on the Open RowSet Property specify the Excel Tab and the Starting and ending cell.  For instance 'Sheet1$A3:C', this would insert data into the worksheet from row 3 which give you flexibility to add multiple header rows as you wish.

This can also be used if you want to skip rows when selecting data from Excel worksheets. Find the example at <http://www.bidn.com/forums/microsoft-business-intelligence/integration-services/1541/export-data-from-sql-server-to-excel-skip-first-4-rows-in-excel-sheet>

Inspired by this approach, you can also use VBA or VSTO to dynamically change the format of rows/columns at runtime.

info Last modified by Raymond at 8 months ago * This page is subject to Site terms.

More from Kontext

local_offer mssql local_offer t-sql

visibility 22
thumb_up 0
access_time 8 months ago

This code snippet shows how to calculate time differences.

open_in_new Code snippets

local_offer mssql local_offer t-sql

visibility 14
thumb_up 0
access_time 8 months ago

This code snippet shows how to convert string to date.

open_in_new Code snippets

local_offer mssql local_offer t-sql

visibility 21
thumb_up 0
access_time 8 months ago

JSON is commonly used in modern applications for data storage and transfers. Pretty much all programming languages provide APIs to parse JSON. 

open_in_new Code snippets

local_offer mssql local_offer t-sql

visibility 16
thumb_up 0
access_time 8 months ago

In Oracle database, you can select from dual table if you only want to return a one row result set. In many other databases, the query engine supports select directly from constant values without specifying a table name. 

open_in_new Code snippets

info About author

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Dark theme mode

Dark theme mode is available on Kontext.

Learn more arrow_forward

Kontext Column

Created for everyone to publish data, programming and cloud related articles. Follow three steps to create your columns.


Learn more arrow_forward