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
             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
             With newBook
                 .SaveAs(sFilename, FileFormat:=56)
             End With
             oSheet1 = Nothing
             oSheet6 = Nothing
             newBook = Nothing
             appExcel = Nothing
         Catch ex As Exception
             Dts.TaskResult = ScriptResults.Failure
         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
     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("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

avatar image

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

avatar image

Daniel Ross
2.9k 11 15 18

(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

avatar image

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

avatar image

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jan 11, 2012 at 07:50 AM

Seen: 7853 times

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

Copyright 2016 Redgate Software. Privacy Policy