Click here to Skip to main content
12,632,062 members (28,305 online)
Click here to Skip to main content
Add your own
alternative version

Stats

40.9K views
1.3K downloads
28 bookmarked
Posted

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

, 11 Jan 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Export SQL Server tables to HTML/Excel/Word Document files dynamically.

Introduction

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.

The dtsx package should look like the above Image.

Background

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 http://msdn.microsoft.com/en-us/library/aa238843(v=sql.80).aspx

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 BY      : VENKAT CHAITANYA KANUMUKULA 
 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) 
SET @Server = @@SERVERNAME 
-- This ServerOption Should be Turned on the Server. Not required every time.
EXEC sp_serveroption  @Server,'DATA ACCESS','TRUE'

SELECT [TableName] = so.name 
                FROM sysobjects so, sysindexes si 
                WHERE so.xtype = 'U' AND si.id = OBJECT_ID(so.name)
/*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 so.name 
                ORDER BY 1 DESC 

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

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.

SSIS_Html1.jpg

[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()
        ' CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
        ' 
        '
        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.
        Else
            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

Conclusion

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.

SSIS_Html2.jpg

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.

SSIS_Html3.jpg

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.

SSIS_Html4.jpg

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

SSIS_Html5.jpg

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.

References

http://msdn.microsoft.com/en-us/library/aa238843(v=sql.80).aspx

History

9 Th Jan 2012

4 Th Jan 2012

License

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

Share

About the Author

Venkat Chaitanya Kanumukula
Software Developer (Senior)
United States United States
Mr. Kanumukula is a Sr.DevOps / Sr.Developer, and has been in the industry since Oct 1997.

• Around 18 years of experience in the areas of application programming, system design, development, deployment, execution, testing and Implementation of business applications.
• Experience in managing both on-premise & cloud hosting infrastructure/applications and operations including Data Center maintenance.
• Experience using Dynamics 365 for Operations/Microsoft Dynamics AX 2012/R2/R3( Inc - Visual Studio.Net, X++, C#, SharePoint, SQL Server, BizTalk, MS Windows Server, DataCenter and etc).
• Experience in ERP upgrades Dynamics 365, Azure Administration and hosting, LCS (Life Cycle Services - Project management tool for Dyanmics AX ERP).
• Experience with Commitment to Quality - technical quality assurance (before, during and after development). Create partnership with project manager to give technical assistance regarding important decisions.
• Well-Versed with Agile, SCRUM and CMMI process and 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 and adapted to new processes and technologies at ease without any training provided from the organizations while working at my previous employments, Yet on demand delivered all tasks 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-orientated and Customer service oriented.

You may also be interested in...

Pro
Pro

Comments and Discussions

 
Question1) Web Assistant Procedures does not exist 2) could not find sp_makewebtask Pin
Member 1168422312-May-15 3:55
memberMember 1168422312-May-15 3:55 
AnswerRe: 1) Web Assistant Procedures does not exist 2) could not find sp_makewebtask Pin
Venkat Chaitanya Kanumukula9-Jun-15 19:34
memberVenkat Chaitanya Kanumukula9-Jun-15 19:34 
GeneralMy vote of 1 Pin
Jeff Moden16-Feb-14 6:06
memberJeff Moden16-Feb-14 6:06 
SuggestionRe: My vote of 1 Pin
Venkat Chaitanya Kanumukula5-Mar-14 17:47
memberVenkat Chaitanya Kanumukula5-Mar-14 17:47 
GeneralA bit of a rant... Pin
Jeff Moden16-Feb-14 6:03
memberJeff Moden16-Feb-14 6:03 
GeneralRe: A bit of a rant... Pin
Venkat Chaitanya Kanumukula19-Mar-14 15:41
memberVenkat Chaitanya Kanumukula19-Mar-14 15:41 
QuestionIf the database and application are on two different servers Pin
aneesh_kamble13-May-13 21:25
memberaneesh_kamble13-May-13 21:25 
AnswerRe: If the database and application are on two different servers Pin
Venkat Chaitanya Kanumukula18-Jun-13 14:12
memberVenkat Chaitanya Kanumukula18-Jun-13 14:12 
AnswerRe: If the database and application are on two different servers Pin
Venkat Chaitanya Kanumukula19-Mar-14 15:33
memberVenkat Chaitanya Kanumukula19-Mar-14 15:33 
GeneralMy vote of 5 Pin
manoj kumar choubey1-Apr-12 23:14
membermanoj kumar choubey1-Apr-12 23:14 
Question.NET way Pin
JamesHoward97210-Jan-12 23:05
memberJamesHoward97210-Jan-12 23:05 
AnswerRe: .NET way Pin
Venkat Chaitanya Kanumukula11-Jan-12 5:34
memberVenkat Chaitanya Kanumukula11-Jan-12 5:34 
AnswerRe: .NET way Pin
Member 871442128-Nov-13 11:30
memberMember 871442128-Nov-13 11:30 
QuestionCould not find stored procedure 'sp_makewebtask' Pin
Sukh Veer Singh10-Jan-12 20:18
memberSukh Veer Singh10-Jan-12 20:18 
AnswerRe: Could not find stored procedure 'sp_makewebtask' Pin
Venkat Chaitanya Kanumukula11-Jan-12 5:59
memberVenkat Chaitanya Kanumukula11-Jan-12 5:59 
GeneralMy vote of 3 Pin
Kacha Jatin10-Jan-12 19:03
memberKacha Jatin10-Jan-12 19:03 
good article
QuestionMessage Automatically Removed Pin
7-Jan-12 2:12
groupsghjyuk7-Jan-12 2: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.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.161208.2 | Last Updated 11 Jan 2012
Article Copyright 2012 by Venkat Chaitanya Kanumukula
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid