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

• Download SSIS_Excel.zip - 53.11 KB

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.

[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.

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.

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.

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