Format Excel Sheet from DTS when Excel is Not Installed on SQL Server

Introduction

This article helps us to understand formatting of an Excel sheet from DTS package. Usually this can be achieved by using VBScript provided Microsoft Excel is installed.

This article helps to show how to do additional formatting of the spreadsheet when Excel is not installed on SQL Server.

Using the Code

Below ActiveX scripts show how to format an Excel sheet through Data pump task using Template file.

As a first step, create a pre-formatted template file. That is how we want it.

Copy the template file to your desired output location and then export data using Datapump task.

The below code extracts data from "publishers" table which is located in "Pubs" database...

'**********************************************************************
'  Visual Basic ActiveX Script
'************************************************************************
Dim Rs
dim con
Dim sConString
dim strFile
dim FSO 
Dim oTask_create_table
dim oTask_select_query
dim pkg
dim connectionExcel
dim oStep_create_table 
DIM oStep_select_query
'-------------MAIN FUNCTION-----------------------
Function Main()
 dim Contract_number
 dim fso
 dim fold
 dim counter 
 dim fil
 dim rsContract 
 dim SqlString
 
   ' ----------create a new instance of an ADO Connection object--------- 
    Set con = CreateObject("ADODB.Connection")
    Set Rs  = CreateObject("ADODB.Recordset")
 set fso = CREATEOBJECT("Scripting.FileSystemObject")


 '-------------Connection String Settings------------------------
  sConString = "Provider=SQLOLEDB.1;Persist Security Info=True;_
	Trusted_Connection=Yes;Initial Catalog = _
	" & DTSGlobalVariables("gv_database").Value & ";_
	Data Source= "  & DTSGlobalVariables("gv_servername").Value  
  con.open sConString 
  SET pkg = DTSGlobalVariables.Parent
 
  SET oTask_select_query = pkg.Tasks("DTSTask_DTSDataPumpTask_1")   
  SET oStep_select_query = pkg.Steps("DTSStep_DTSDataPumpTask_1")    
  set connectionExcel = pkg.Connections("Microsoft Excel 97-2000")
  Create_summary_Sheet 
  con.close  
  Main = DTSTaskExecResult_Success
End Function

Function Create_summary_Sheet
 dim sSQLText
 dim x
 dim File_Name
 
 SET  rsColumnRemap  = CreateObject("ADODB.Recordset")
 set fso = CREATEOBJECT("Scripting.FileSystemObject")
 
 Set rs  = CreateObject("ADODB.Recordset")
 sSQLText = "select count('x')  'C'  from publishers "
 Rs.ActiveConnection = con 
 Rs.Source = sSQLText
 Rs.Open
 '-----If the no. of records is >0, then prepare excel sheet for that contract.
 
 if Rs.fields("C") > 0 then
  '**************COPY TEMPLATE FILE TO REPORT LOCATION*********************
  
  filename = fso.GetFolder(DTSGlobalVariables("gv_OutputExcelPath").Value)  _
	& "\Report.xls"
  fso.CopyFile  DTSGlobalVariables("gv_ReportTemplate").value,  filename  , true
  
  connectionExcel.DataSource = filename   
  sSQLText = "select * from publishers"
  oTask_select_query.Properties("SourceSQLStatement").Value = sSQLText      
  oTask_select_query.CustomTask.DestinationObjectName = "Sheet1$"    
  oStep_select_query.Execute   
    
 end if
 rs.Close
 
End Function

Enhancement

The above code could produce a formatted output file only for the particular format. We cannot generate output files with different types of format in a single stroke.

For this, we need to dynamically remap source and destination columns.