Formating Excel in script task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

Formating Excel in script task

Author

Topic

bob12
Starting Member

8 Posts

Posted - 2009-04-16 : 23:08:32

Hi,
I am able to dump data into excel sheet using Data Flow task. Now I want to make header bold and add autofilter. How can I do that. Can some one give me Ideas How to format Headers in script task.

Thank you
Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-17 : 11:15:58

create an excel object in script task and access the created excel file through it and change the properties.

bob12
Starting Member

8 Posts

Posted - 2009-04-17 : 23:39:43

I am new to this. Can you give sample code?

Thank you
Bob

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-04-18 : 15:28:50

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81979

bob12
Starting Member

8 Posts

Posted - 2009-04-18 : 21:24:37

HI,
I don't have Microsoft.Office.Interop.Excel.ApplicationClass. So I am using below code to connect to excel but I am not able to figure out how to make first row bold and Autofilter

' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.

Imports System
Imports System.IO
Imports System.Data.OleDb
Imports System.Xml
Imports System.Data
Imports System.Data.SqlClient
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
Dim fileToTest As String
Dim tableToTest As String
Dim connectionString As String
Dim excelConnection As OleDbConnection
Dim excelTables As DataTable
Dim excelTable As DataRow
Dim currentTable As String
Dim fileExists As Boolean
fileToTest = "Report.xls"
If (File.Exists(fileToTest)) Then
fileExists = True
Else
fileExists = False
End If
If fileExists Then
connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & fileToTest & _
";Extended Properties=Excel 8.0"
excelConnection = New OleDbConnection(connectionString)
excelConnection.Open()
excelTables = excelConnection.GetSchema("Tables")

'How to add autofilter to the Sheet??????
' How to make first row bold and back ground color?????
End If
Dts.TaskResult = Dts.Results.Success
End Sub

End Class

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources