asked
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 _ _ 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
Sorry, the development station and the server are both 64 bit - Windows 2008 R2 sp1.
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.
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.
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.
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.
