Click here to Skip to main content
13,251,998 members (59,732 online)
Click here to Skip to main content
Add your own
alternative version


12 bookmarked
Posted 31 Dec 2007

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

, 17 Dec 2008
Rate this:
Please Sign up or sign in to vote.
Format Excel Sheet from DTS when Excel is not installed on SQL Server


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 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")
  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
 '-----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$"    
 end if
End Function


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.


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


About the Author

India India
Having 7+ years of experince in Visual Basic and SQL Sevrer technologies

You may also be interested in...

Comments and Discussions

-- There are no messages in this forum --
Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171114.1 | Last Updated 18 Dec 2008
Article Copyright 2007 by s_arun_kumar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid