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

6:49:00 am SQL Server 1 Comments

 

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 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.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.
This DTSX package is basically to demonstate a method of data migration to export SQL Server 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 (NAV, Axapta, Great Plains, Solomon, CRM and etc) are using the SQL Server and Excel for data migration.
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.

Background

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.

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
GO
RECONFIGURE
GO
SP_CONFIGURE 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

[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 BY      : Al-Salman Rehman
 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 
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)
/*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 so.name 
                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 then you need to modify this.)

 /********************************************************************** 
 CREATED BY      : AL-Salman Rehman 
 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 =?
DECLARE @vsSQL VARCHAR(8000)  
 
BEGIN  
  /* EXCEL TABLE SCRIPT GENERATOR 
     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 sc.id = so.id  
  JOIN systypes st ON st.xusertype = sc.xusertype  
  WHERE so.name = @vsTableName 
  /* Avoiding the following data types here... */
  AND st.Name not in ( 'image','sysname','binary','varbinary','xml','uniqueidentifier') 
  ORDER BY  
  sc.ColID  
  
  SELECT SUBSTRING(@vsSQL,1,LEN(@vsSQL) - 2) + CHAR(10) + ')'  AS ExcelTableName   
 END

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. 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 SQL Server OPENROWSET function which finally creates the ExcelSheets in the destination file with the Name (DynamicExcelFileDDMMYYYY.xls). For more information on the OPENROWSET functionality refer online documentation.

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

Public Class ScriptMain

    Public Sub Main()
        ' CREATED BY      : VENKAT CHAITANYA KANUMUKULA 
        ' 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.
        Else
            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.
        'MsgBox(strCn)
        Dts.Variables("User::InsertScripter").Value = strCn
        Dts.TaskResult = Dts.Results.Success
    End Sub

End Class

Conclusion

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

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 creating sheets and table structures in a excel file with SSIS and to understand the excel functionalities. 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.
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 limitations.
Hope this article will serve the purpose. Any suggestions or corrections are most welcome.



DONWLOAD EXAMPLE