Click here to Skip to main content
15,672,024 members
Articles / Web Development / HTML
Posted 4 Jan 2012


28 bookmarked

Dynamically Generating Html/Excel/Word Document Files with SQL Server data using SSIS 2005

Rate me:
Please Sign up or sign in to vote.
3.90/5 (6 votes)
11 Jan 2012CPOL4 min read
Export SQL Server tables to HTML/Excel/Word Document files dynamically.


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 HTML/Excel/Word documents using SSIS. This package will help to migrate data in a well formatted HTML files in simple and efficient way. The Goal of this article is to help the users by providing the concept to create either HTML/Excel/Word document files with all the data from the SQL Sever database tables with a better presentation and good look and feel without the need of any templates, DLL's and much of programming.

This package is basically to demonstrate a method of data migration to export SQL Server tables to well formatted static HTML pages. Here is the simple DTSX package which creates either an HTML/Excel file on the fly based on the variable value settings. The package is developed using Visual Studio 2005.

Below is the demonstration of the steps to achieve this.

Image 1

The dtsx package should look like the above Image.


The above package uses the SP_MAKEWEBTASK SQL Server Object for creating the Html files. This SQL Server object does all the HTML presentation & formatting including table name as heading.

For more information on the SP_MAKEWEBTASK functionality and its options refer online documentation at the following link

Using the code

The ServerOption should be turned on, these 'DATA ACCESS' and 'Web Assistant Procedures' options needed to be reconfigured 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. These options are currently set in the following task.

[Get List of Tables to Process] Execute SQL Task: The query is to retrieve all the table names. (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 the Server. Not required every time.
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'

SELECT [TableName] = 
                FROM sysobjects so, sysindexes si 
                WHERE so.xtype = 'U' AND = OBJECT_ID(
/*Uncomment the following line if you are using Excel 2003.*/
                --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 

-- Comment this later after executing. This is not required every time.
EXEC sp_configure 'Web Assistant Procedures', 1;

Change the Variable DestFileType value either HTM , HTML or XLS , XLSX or Doc , Docx as shown in the below image.Change the database connection to SQL Server 2000/2005 and point to the database which you want to export. Change the Variable DestExcelFilePath Value from 'C:\SSIS' for creating a file at the desired location. Package should work properly if all the Table names and Column names are followed as per the microsoft naming standards.


[Insert Script Generation] ScriptTask: This script task code in VB.NET is to build a query using the SQL Server SP_MAKEWEBTASK function which finally creates the files in the destination folder with the name either (TableName.Html/TableName.xls/TableName.doc or etc) based on the DestFileType variable value is set to.

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

Public Class ScriptMain

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

        FileType = CStr(Dts.Variables("User::DestFileType").Value)

        If TableName.Length > 30 Then
            ExcelTable = Strings.Left(TableName, 31)
            'Excel sheet Name can only be 30 characters length.
            ExcelTable = TableName
        End If

        ' The sp_serveroption has to be set to TRUE on the Server for running SP_MAKEWEBTASK.
        ' To avoid runtime errors in column names replace '*' with column names in brackets[].

        Dim strCn As String = "EXEC  sp_makewebtask  " & _
        "@outputfile = '" + excelFilePath + ExcelTable + "." + FileType + "', " & _
        "@query = 'SELECT * FROM dbo.[" + TableName + "]', " & _
        "@colheaders =1,@FixedFont=0,@lastupdated=0," & _
        "@resultstitle= '" + TableName + " Table '"

        Dts.Variables("User::InsertScripter").Value = strCn
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class


The above mentioned demonstration of steps determine that multiple HTML/XLS/Word document files along with data migration from SQL Server can be dynamically created using SSIS 2005. 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.


Hence it can be concluded that exporting the database table objects to a HTML/Excel/Word document files is achieved through easy steps in SSIS with a minimum programming.

If the DestFileType variable value is set to XLS or XLSX. It should look like the below image.


Note: When the Variable DestFileType value is set to XLS or XLSX the following script task will create the excel file but with html body inside it.

If the DestFileType variable value is set to HTM or HTML. It should look like the below image.


If the DestFileType variable value is set to DOC or DOCX. It should look like the below image.


Note: When the Variable DestFileType value is set to DOC or DOCX the following package will create the Word document file but with html body inside it.

The file extensions are not limited to above mentioned. If you are using Open office there are various other extension available.

The above images are provided as a sample just to show how the formatting and presentation of output files look like after exporting the tables to HTML ,Excel and Word document files.

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.



9 Th Jan 2012

4 Th Jan 2012


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

Written By
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

Question1) Web Assistant Procedures does not exist 2) could not find sp_makewebtask Pin
Member 1168422312-May-15 2:55
Member 1168422312-May-15 2:55 
AnswerRe: 1) Web Assistant Procedures does not exist 2) could not find sp_makewebtask Pin
Venkat Chaitanya Kanumukula9-Jun-15 18:34
Venkat Chaitanya Kanumukula9-Jun-15 18:34 
GeneralMy vote of 1 Pin
Jeff Moden16-Feb-14 5:06
Jeff Moden16-Feb-14 5:06 
SuggestionRe: My vote of 1 Pin
Venkat Chaitanya Kanumukula5-Mar-14 16:47
Venkat Chaitanya Kanumukula5-Mar-14 16:47 
GeneralA bit of a rant... Pin
Jeff Moden16-Feb-14 5:03
Jeff Moden16-Feb-14 5:03 
GeneralRe: A bit of a rant... Pin
Venkat Chaitanya Kanumukula19-Mar-14 14:41
Venkat Chaitanya Kanumukula19-Mar-14 14:41 
QuestionIf the database and application are on two different servers Pin
aneesh_kamble13-May-13 20:25
aneesh_kamble13-May-13 20:25 
AnswerRe: If the database and application are on two different servers Pin
Venkat Chaitanya Kanumukula18-Jun-13 13:12
Venkat Chaitanya Kanumukula18-Jun-13 13:12 
AnswerRe: If the database and application are on two different servers Pin
Venkat Chaitanya Kanumukula19-Mar-14 14:33
Venkat Chaitanya Kanumukula19-Mar-14 14:33 
GeneralMy vote of 5 Pin
Manoj Kumar Choubey1-Apr-12 22:14
professionalManoj Kumar Choubey1-Apr-12 22:14 
Question.NET way Pin
JamesHoward97210-Jan-12 22:05
JamesHoward97210-Jan-12 22:05 
AnswerRe: .NET way Pin
Venkat Chaitanya Kanumukula11-Jan-12 4:34
Venkat Chaitanya Kanumukula11-Jan-12 4:34 
AnswerRe: .NET way Pin
Member 871442128-Nov-13 10:30
Member 871442128-Nov-13 10:30 
QuestionCould not find stored procedure 'sp_makewebtask' Pin
Sukh Veer Singh10-Jan-12 19:18
Sukh Veer Singh10-Jan-12 19:18 
AnswerRe: Could not find stored procedure 'sp_makewebtask' Pin
Venkat Chaitanya Kanumukula11-Jan-12 4:59
Venkat Chaitanya Kanumukula11-Jan-12 4:59 
GeneralMy vote of 3 Pin
Kacha Jatin10-Jan-12 18:03
Kacha Jatin10-Jan-12 18:03 
QuestionMessage Closed Pin
7-Jan-12 1:12
sghjyuk7-Jan-12 1:12 

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.