x

How to Format Excel spreadsheet in a Script Task

I have a SSIS script task that formats an Excel spreadsheet - this was converted from DTS/ActiveX where I had no problems. I've checked the GAC to make sure that the Interop.Excel is there and it is. I can run this step in BIDS without a problem, but when I run this as a scheduled job it fails - or more accurately, it runs but doesn't format the spreadsheet. Since I've got 100s of similar tasks to convert, I can't continue to manually execute the process every time it needs to run. What am I missing? Here is the main part of the script:

' The ScriptMain is the entry point class of the script.
Option Strict Off
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Tasks
Imports System.IO
Imports System.Data.OleDb
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.DateTime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
         Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum
    Public Sub Main()
        Dim appExcel As Object
        Dim newBook As Object
        Dim oSheet1 As Object
        Dim oSheet2 As Object
        Dim oSheet3 As Object
        Dim oSheet4 As Object
        Dim oSheet5 As Object
        Dim oSheet6 As Object
        Dim sFilename As String
        Try
            appExcel = CreateObject("Excel.Application")
            sFilename = "Career Sales Force_Weekly.xls"
            appExcel.DisplayAlerts = False
            'Open the workbook specified
            newBook = appExcel.Workbooks.Open(sFilename)
            oSheet1 = newBook.worksheets("Current Week")
            oSheet2 = newBook.worksheets("Career Sales Force YTD")
            oSheet3 = newBook.worksheets("Current Week Students")
            oSheet4 = newBook.worksheets("YTD Students")
            oSheet5 = newBook.worksheets("Current Week FSSR")
            oSheet6 = newBook.worksheets("YTD FSSR")
            'Set Dates
            SetDates(oSheet1)
            ...
            SetDates(oSheet6)

            SetBolding(oSheet1)
            ...
            SetBolding(oSheet6)

            With newBook
                .SaveAs(sFilename, FileFormat:=56)
            End With
            appExcel.quit()

            oSheet1 = Nothing
            ...
            oSheet6 = Nothing
            newBook = Nothing
            appExcel = Nothing
        Catch ex As Exception
            Dts.TaskResult = ScriptResults.Failure
            System.Windows.Forms.MessageBox.Show(ex.ToString)
            Throw
        End Try
        Dts.TaskResult = ScriptResults.Success
    End Sub

Public Sub SetBolding(ByVal oSheetX As Object)
        Dim c As Object
        c = oSheetX.UsedRange
        For Each x In c.Columns("a").Cells
            If x.Value = "TOTAL" Or x.Value = "NATIONAL" Then
                x.EntireRow.Font.Bold = True
            End If
        Next
    End Sub

    Public Sub SetDates(ByVal oSheetX As Object)
        Dim StartDate As String
        Dim EndDate As String
        StartDate = oSheetX.Range("T4").Value
        EndDate = oSheetX.Range("U4").Value
        oSheetX.Range("A1").Value = "Weekly Data for " & StartDate & " to " & EndDate
        oSheetX.Range("U1").EntireColumn.Delete()
        oSheetX.Range("T1").EntireColumn.Delete()
        oSheetX.Range("A1").EntireRow.Font.Bold = True
        oSheetX.Range("A3").EntireRow.Font.Bold = True
    End Sub
End Class
more ▼

asked Jan 11, 2012 at 07:50 AM in Default

PurpleLady gravatar image

PurpleLady
21 1 1 3

By chance is your workstation where you are developing and testing this 32 bit and the server you are trying to run it on 64 bit?
Jan 11, 2012 at 07:52 AM Tim
Sorry, the development station and the server are both 64 bit - Windows 2008 R2 sp1.
Jan 11, 2012 at 07:57 AM PurpleLady
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

I have used the excel interop assemblies fairly extensively in the past, but it has been a while so I'm a little rusty.

Am i right in saying that it is opening an existing excel document? If so, where is it getting the file path from? i.e. D:\files\fileToFormat.xls. I can see it's creating the excel object, but the file name is just a filename and no path so how does it know where to save it?

There also might be a security issues, the sql agent doesnt normally have access to the local drives, so I suggest creating a identity (not your own credentials, a service account) and SSIS proxy account using that identity to run the package, and make sure that account has access to the file location.
more ▼

answered Jan 11, 2012 at 04:36 PM

Daniel Ross gravatar image

Daniel Ross
2.9k 11 13 14

(comments are locked)
10|1200 characters needed characters left
Thanks, Daniel. I'd removed the path from the sample but in script it is fully-qualified. I've checked the permissions and they seem to be fine. Let me qualify that - I have plenty of other jobs which are creating spreadsheets in the same location without a problem. But you may be onto something since I was able to expand the task error handling and now get "Microsoft Office Excel cannot open or save any more documents because there is not enough available memory or disk space. " although there are 17G available and nothing in any of the temp folders. Since I got this error after rebooting, I don't think memory (4G) is an issue. I'm going to try logging in as the sql agent account - maybe that'll give me a better idea.
more ▼

answered Jan 13, 2012 at 04:13 AM

PurpleLady gravatar image

PurpleLady
21 1 1 3

ok, it seems that the script (or another script) is not closing the excel object down correctly. try logging into the server and see if there are lots of excel instances running in the processes tab. make sure you show process from other users. if there are then they are not closing down correctly.
Jan 15, 2012 at 06:53 PM Daniel Ross
(comments are locked)
10|1200 characters needed characters left
Thanks, Daniel. I was finally able to get it to run by setting the properties of Microsoft Excel Application, under Identity, to The Interactive User from The Launching User. This setting is different from what it was on the old win2003 production server. If I understand what it's doing, it retains the admin account information and uses that account to launch excel.
more ▼

answered Jan 16, 2012 at 04:45 AM

PurpleLady gravatar image

PurpleLady
21 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x942
x117

asked: Jan 11, 2012 at 07:50 AM

Seen: 5005 times

Last Updated: Jan 11, 2012 at 08:01 AM