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.)
DECLARE @Server varchar(50)
SET @Server = @@SERVERNAME
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)
GROUP BY so.name
ORDER BY 1 DESC
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()
Dim excelFilePath As String = CStr(Dts.Variables("User::DestExcelFilePath").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)
Else
ExcelTable = TableName
End If
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.
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
