Click here to Skip to main content
15,063,287 members
Articles / Database Development / SQL Server / SQL Server 2008
Posted 20 Dec 2011


43 bookmarked

Dynamically creating Excel File, ExcelSheets and Exporting Data from SQL Server using SSIS 2005

Rate me:
Please Sign up or sign in to vote.
4.63/5 (16 votes)
15 Jan 2012CPOL6 min read
Export SQL Server Tables to Excel Files Dynamically

This article is a sponsored article. Articles such as these are intended to provide you with information on products and services that we consider useful and of value to developers


This article is intended primarily for intermediate to advanced SSIS (SQL Server Integration Services) users who are familiar with SQL Server Business Intelligence. Basic understanding of script task, Execute Sql Task, parameters and variables are required, as it is not exactly a tutorial, but a description of the implementation of exporting the database table objects to Excel Objects through SSIS without the need of any templates and much of programming.This package will help to migrate data in a simple and efficient way. This package could also be useful to the users of SQL Server 2008 database, which could help in replacing the deprecated sp_makewebtask feature for exporting the data to excel.

The Goal of this article is to help the users by providing the concept to create a Excel file with unlimited sheets with all the data from the SQL Sever database eliminating the need of SSIS data conversion tasks, mapping and predefined column names, data types and etc. The same concept of developing generic packages can be applied similarly when migrating SQL Server data to different databases like Oracle, Teradata, MS-Access and etc using the connection manager with related data source providers can be achieved through the SSIS ETL Tool.

This Generic DTSX package is basically to demonstrate a method of data migration to export SQL Server database tables to Excel file with multiple sheets. After reading many blogs about people having problems in generating excel files and sheets dynamically from SQL Server 2000/2005 through SSIS (SQL Server Integration Services). Here is the simple DTSX package which creates a excel file on the fly and dumps the data on the sheets.

Currently Most of the Microsoft Dynamics ERP software products like (Navision, Axapta, Great Plains, Solomon, Concorde, Point of Sale, Retail Management System, Customer Relationship Management and etc) are also using the SQL Server and Excel for data migration and reporting purpose.

Little bit of tweaks in the below code in the tasks could help many users to achieve their goals in a easiest way.

Below is the demonstration of the steps to achieve this.


The dtsx package should look like the above Image. The package is developed using Visual Studio 2005.


Change the database connection to SQL Server 2000/2005 and point to the database which you want to export (currently it is pointed to master database). Change the Variable DestExcelFilePath Value from 'C:\SSIS' for creating a file at the desired location.

Note: This package should work properly if all the Table names and Column names in the database are followed as per the microsoft naming standards.


Excel file treats each sheet as a table.Excel-95/2003 generates only 65335 rows for sheet and 255 sheets maximum if its Excel-95, There is no rows or sheets limitation if you are using Excel 2007 and above. There are certain limitations to Excel previous versions. Read online documentation for more information.

Following are few links on Excel differences and comparisons.

The above package uses the SQL Server OPENROWSET Object for data migration. For more information on the OPENROWSET functionality and its options refer online documentation at the following link

Explanation and use of OPENROWSET, SP_SERVEROPTION, SP_MAKEWEBTASK and server options like 'DATA ACCESS','SHOW ADVANCED OPTIONS', 'AD HOC DISTRIBUTED QUERIES' is out of scope of this article.

Using the Code

These options need to be configured 'Show Advanced Options' and 'Ad Hoc Distributed Queries' on the SQL Server before running the package. The package will fail without these Options. And importantly the database login should have admin privileges to execute the package tasks successfully. If you want to run this code, then better check with your DBA's regarding permissions you require.

/*-- This has to executed on the server before running the package.
  -- These Options are required to run the OPENROWSET Function.                                                      This has to be just executed once.*/

SP_CONFIGURE 'Show Advanced Options', 1
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1

[Get List of Tables to Process] Execute SQL Task: The query is to retrieve all the table names which are less then 65335 rows. ( Customize this query as per your requirement.)

 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is get the List of Tables from the Database.
 COMMENTS     : So that it can be used for creating objects in xls file via SSIS.
                Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition)
 SSIS TASK    : [Get List of Tables to Process] SSIS Execute SQL Task   

DECLARE @Server varchar(50) 
-- This ServerOption Should be Turned on 
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'

SELECT [TableName] = 
                FROM sysobjects so, sysindexes si 
                WHERE so.xtype = 'U' AND = OBJECT_ID(
/*Comment the following line if you are using Excel 2007.*/
                AND si.rows < 65335 
/*Uncomment the following code if you want to avoid the table names where you dont have data in it.*/
                --AND si.rows > 0
                GROUP BY 
                ORDER BY 1 DESC

[Excel Script Generator] Execute SQLTask:The query in this task builds the table structure with the data types required for the Excel.(Most of the SQL data types have been handled here. If you want add any other specific data type or a different database then you need to modify this.)

 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for Generating a Script Table for Excel.
 COMMENTS     : So that it can be used for creating objects in xls file via SSIS.
                Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition) 
 SSIS TASK    : [Excel Script Generator] Execute SQL Task 
DECLARE @vsTableName VARCHAR(100)  
SET @vsTableName =?
     Handled Most of the regular SQL data types below. */
  SELECT @vsSQL = 'CREATE TABLE ' + '`' + @vsTableName + '`' + CHAR(10) + '(' + CHAR(10)  
  --SELECT @vsSQL = @vsSQL + '`' + RTRIM(sc.Name) + '` ' +  
  SELECT @vsSQL = @vsSQL + '[' + sc.Name + '] ' +  
  CASE WHEN st.Name IN ('nvarchar','ntext','text','varchar','varchar','char','nchar','xml','uniqueidentifier') THEN  'LongText'  
       WHEN st.Name IN ('tinyint','int','smallint','bigint','float','numeric','decimal','money','smallmoney','bit') THEN  'Long'
       WHEN st.Name IN ('date','datetime','timestamp') THEN  'datetime'
  ELSE ' ' END + ',' + CHAR(10) 
  FROM sysobjects so  
  JOIN syscolumns sc ON =  
  JOIN systypes st ON st.xusertype = sc.xusertype  
  WHERE = @vsTableName 
  /* Avoiding the following data types here... */
  AND st.Name not in ( 'image','sysname','binary','varbinary','xml','uniqueidentifier') 
  SELECT SUBSTRING(@vsSQL,1,LEN(@vsSQL) - 2) + CHAR(10) + ')'  AS ExcelTableName   

[Create Tables in Excel] Execute SQLTask: The create table script query from the above task is passed to this task, which is executed in the Excel file with the Excel Connection Manager for creating sheets with the table structure and data types required for the Excel. The destination file could be found with the Name (DynamicExcelFileDDMMYYYY.xls). Basically this task is the key to create all the sheets in the excel destination file.

[Get Column Names] Execute SQLTask: The task builds the column names and passes it to the below [Insert Script Generation] Script Task which is used in the query with OPENROWSET.

 CREATED ON      : This is created and tested in SQL SERVER 2000 and SQL SERVER 2005.
 PURPOSE      : This Object is Created for Generating Column names.
 COMMENTS     : So that it can be used for creating objects in xls file via SSIS.
                Tested on SQL Server 8.00.194 - RTM (Desktop Engine) ,SQL Server 9.00.4035.00 - SP3 (Developer Edition)

DECLARE @SRCOBJECT varchar(8000)
DECLARE @SRCOBJECT1 varchar(8000)

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'LongText','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1 ,'Long','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'DateTime','')

SET @SRCOBJECT1= Replace(@SRCOBJECT1,')','')

SET @SRCOBJECT1 = Replace(@SRCOBJECT1,'`','')


[Insert Script Generation] ScriptTask: This script task code in is to build a Query using SQL Server OPENROWSET function which finally inserts the data into the ExcelSheets. The query is passed to the Execute SQL Task through a variable and is executed on the Excel file with the Excel Connection Manager.

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

    Public Sub Main()
        ' Insert Script Generation

        Dim excelFilePath As String = CStr(Dts.Variables("User::DestExcelFilePath").Value) + CStr(Dts.Variables("DestExcelFileName").Value)
        Dim TableName, ExcelTable As String
        TableName = CStr(Dts.Variables("User::ExcelTable").Value)

        If TableName.Length > 30 Then
            ExcelTable = Strings.Left(TableName, 31)
            'Excel sheet Name can only be 30 characters length.
            ExcelTable = TableName
        End If
       'Retrieve the ExcelColumnNames from the Variable and build the String Here.
        Dim ColumnNames As String = CStr(Dts.Variables("User::ExcelColumns").Value)
        Dim strCn As String = " Insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
                "'Excel 8.0;Database=" + excelFilePath + "','SELECT " + ColumnNames + " FROM [" + ExcelTable + "$]') SELECT " + ColumnNames + " FROM [" + TableName + "]"

        'Uncomment the following message to check the query.
        Dts.Variables("User::InsertScripter").Value = strCn
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class 


The above mentioned demonstration of steps determine that excel file with multiple sheets along with data migration from SQL Server can be dynamically created using SSIS.


Hence it can be concluded that exporting the database table objects to a Excel file with multiple sheets is achieved through SSIS without the need of any templates, DLL's and much of programming.

This Article is basically intended to demonstrate the concept of developing a generic package to create sheets and table structures in a excel file with SSIS and to understand the excel functionalities. The same concept can be applied similarly when migrating SQL Server data to different databases. Its not a good Idea to export a huge database with the above package where it might end up using the system memory and other resources.

Since the Web Assistant features like SP_MAKEWEBTASK which help in exporting the data objects to html, excel, word, etc are no longer available in SQL Server 2008. This package could also be useful to the users of SQL Server 2008 database, which could help in replacing the deprecated sp_makewebtask for exporting the data to excel file.

There are many articles on creating & handling excel files including formatting the cells, inserting formulas and manipulating the excel objects which can be achieved through various programming languages. But this article is intended for the SQL Server Integration Services users who are aware of its benefits and limitations.


The above image is provided as a sample to show the excel file created through this package.

Hope this article will serve the purpose. Any suggestions or corrections are most welcome.


Points of Interest

Reverse Engineering, Up-gradations, Conversions, Integrations and Data migration.

Have loads of real time experience on million dollar Banking and Financial data migration projects.


Last Updated on 16 Th Jan 2012.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Venkat Chaitanya Kanumukula
Technical Lead
United States United States
Mr. Kanumukula is a IT-Professional and has been in the industry since 1997.
• Around 15+ years of experience in the areas of system design & architecture, application programming, development, testing, deployments, execution with implementations & Roll-outs of business applications in cloud/on-premise.
• Experience with Dynamics 365 for F&O/Dynamics AX, D365 for Business Central/NAV,(Inc -Visual Studio.Net, X++, AL, C#, CSide, SharePoint, SQLServer, SFDC, Power-BI, Power Apps, Dataverse/Common Data Service, CRM, SSIS/SSRS/SSAS, BizTalk, IOT, IIOT, JAVA, AWS, GCP, Azure, API, etc)
• Experience in managing Operations & Data Center maintenance both on-premise & cloud hosting, infrastructure/applications assessments & build systems.
• Experience in ERP upgrades, Mulesoft API's,MS Dynamics 365,Azure administration & hosting, LCS-Life Cycle Services.
• Experience with Commitment to Quality, technical quality assurance(before, during & after development). Create partnership with project manager to give technical assistance regarding important decisions.
• Well-Versed with Agile, SCRUM & CMMI process & methodologies to support rapid iterative Quality software development.

A highly motivated, self-starter & problem solver with multi-tasking skills, Had managed and served through an established process to get the job done in a timely and efficient manner with an eye on every detail during ERP-implementations. Flexible to work under stress and have ability to prioritize workload and stay organized in a fast-paced environment.

Learned & adapted to new technologies & tools at ease, aggressively delivered tasks on-demand at a fast-pace satisfying the needs of product owners, product development, program managers, Vendors, Higher Management, Internal/External Clients.

Have a proactive & positive attitude with a willingness to do what it takes to complete the job. Self-managed work style within a team environment. Extremely detail & Customer service oriented.

Comments and Discussions

QuestionIs the data actually being moved in this process? Pin
Member 1450465120-Jun-19 5:36
MemberMember 1450465120-Jun-19 5:36 
AnswerRe: Is the data actually being moved in this process? Pin
Venkat Chaitanya Kanumukula25-Oct-19 8:05
MemberVenkat Chaitanya Kanumukula25-Oct-19 8:05 
Questiondata is not coming from the tables only columns names.. Pin
Raju rokr23-Jan-15 2:14
MemberRaju rokr23-Jan-15 2:14 
AnswerRe: data is not coming from the tables only columns names.. Pin
Venkat Chaitanya Kanumukula4-Mar-15 22:01
MemberVenkat Chaitanya Kanumukula4-Mar-15 22:01 
Questioni want to achieve the same but from a stored procedure Pin
Raju rokr23-Jan-15 1:13
MemberRaju rokr23-Jan-15 1:13 
AnswerRe: i want to achieve the same but from a stored procedure Pin
Venkat Chaitanya Kanumukula4-Mar-15 21:42
MemberVenkat Chaitanya Kanumukula4-Mar-15 21:42 
Questionerror when creating tables in Excel Pin
kicza.chats26-Nov-14 1:17
Memberkicza.chats26-Nov-14 1:17 
AnswerRe: error when creating tables in Excel Pin
Venkat Chaitanya Kanumukula10-Jan-15 1:48
MemberVenkat Chaitanya Kanumukula10-Jan-15 1:48 
Questionfacing window 32bit and 64 bit issue Pin
sanchit bidhuri25-Jul-14 2:29
Membersanchit bidhuri25-Jul-14 2:29 
AnswerRe: facing window 32bit and 64 bit issue Pin
Venkat Chaitanya Kanumukula13-Nov-14 1:55
MemberVenkat Chaitanya Kanumukula13-Nov-14 1:55 
QuestionGeneration of hml file Pin
aneesh_kamble13-May-13 20:28
Memberaneesh_kamble13-May-13 20:28 
AnswerRe: Generation of hml file Pin
Venkat Chaitanya Kanumukula18-Jun-13 14:06
MemberVenkat Chaitanya Kanumukula18-Jun-13 14:06 
QuestionNeed Help Pin
Ratnesh N Bharos17-Apr-13 0:07
MemberRatnesh N Bharos17-Apr-13 0:07 
QuestionNeed help!! Pin
Vikky2107310-Feb-13 21:25
MemberVikky2107310-Feb-13 21:25 
AnswerRe: Need help!! Pin
Venkat Chaitanya Kanumukula11-Feb-13 19:15
MemberVenkat Chaitanya Kanumukula11-Feb-13 19:15 
GeneralRe: Need help!! Pin
Vikky2107311-Feb-13 19:20
MemberVikky2107311-Feb-13 19:20 
QuestionQuestion Pin
andrey.koshkarov231-Jan-13 3:37
Memberandrey.koshkarov231-Jan-13 3:37 
AnswerRe: Question Pin
Venkat Chaitanya Kanumukula8-Feb-13 19:09
MemberVenkat Chaitanya Kanumukula8-Feb-13 19:09 
GeneralMy vote of 5 Pin
vkushwaha30-Jan-13 10:02
Membervkushwaha30-Jan-13 10:02 
QuestionExcel SSIS Pin
Rio Khn2-Nov-12 1:37
MemberRio Khn2-Nov-12 1:37 
QuestionIssue in solving a requirement in SSIS Pin
prabusuccess11-Oct-12 21:52
Memberprabusuccess11-Oct-12 21:52 
AnswerRe: Issue in solving a requirement in SSIS Pin
Venkat Chaitanya Kanumukula16-Oct-12 6:26
MemberVenkat Chaitanya Kanumukula16-Oct-12 6:26 
GeneralMy vote of 5 Pin
dineshvishe20-Aug-12 8:27
Memberdineshvishe20-Aug-12 8:27 
GeneralRe: My vote of 5 Pin
Venkat Chaitanya Kanumukula8-Feb-13 19:27
MemberVenkat Chaitanya Kanumukula8-Feb-13 19:27 
AnswerRe: My vote of 5 Pin
Venkat Chaitanya Kanumukula11-Feb-13 19:41
MemberVenkat Chaitanya Kanumukula11-Feb-13 19:41 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.