access_time 7 years ago languageEnglish
more_vert

Generate Formatted Excel Destination (Output) in SSIS Data Flow Task

visibility 14,978 comment 0

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 Administrator 8 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Follow Kontext

Get our latest updates on LinkedIn.

Want to contribute on Kontext to help others?

Learn more

More from Kontext

visibility 718
thumb_up 1
access_time 3 years ago
visibility 86
thumb_up 0
access_time 3 years ago
visibility 61
thumb_up 2
access_time 3 years ago