This site uses cookies to deliver our services. By using this site, you acknowledge that you have read and understand our Cookie and Privacy policy. Your use of Kontext website is subject to this policy. Allow Cookies and Dismiss

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

8283 views 0 comments last modified about 4 years ago Raymond

In this page

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: <http://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.

Related pages

Use Hadoop File System Task in SSIS to Write File into HDFS

219 views   0 comments last modified about 4 months ago

Context SQL Server Integration Service ( SSIS ) has tasks to perform operations against Hadoop, for example: Hadoop File System Task Hadoop Hive Task Hadoop Pig Task In Data Flow Task, you can also use: Hadoop HDFS Source ...

View detail

Create ETL Project with Teradata through SSIS

7962 views   2 comments last modified about 4 years ago

Infosphere DataStage is adopted as ETL (Extract, Transform, Load) tool in many Teradata based data warehousing projects. With the Teradata ODBC and .NET data providers, you can also use the BI tools from Microsoft, i.e. SSIS. In my previous post, I demonstrated how to install Teradata Tool...

View detail

Create ETL Project for Collecting Sales Data using SSIS

1404 views   0 comments last modified about 5 years ago

Serial: An Introduction to SQL Server Features Case Scenario For this ETL project, the requirements are listed below: Sales data will be pushed to specified shared fold...

View detail

Add comment

Please login first to add comments.  Log in New user?  Register

Comments (0)

No comments yet.