The SSIS and Excel Story Continues

Folks, in my continued experimentation with SSIS and Excel I found out another roadblock which is typically permission related and want to highlight the same in this post. This time I used the script task to read and save an Excel (.xls) document using the Excel.Application class. The code typically loads an excel file based on Package Variable values, makes some modifications and saves it back and it is as simple as below:

 

==============================================================

Public Sub Main()
'
' Add your code here
'
Dim objExcel As Object
Dim vSrcPath As String
objExcel = CreateObject("Excel.Application")
vSrcPath = Dts.Variables("User::RebateDefSrcDir").Value.ToString + "\" + Dts.Variables("User::FileName").Value.ToString + ".xls"
objExcel.Workbooks.Open(vSrcPath)
objExcel.Sheets("Define Rebate").Select()
objExcel.ActiveSheet.Unprotect(Password:="")
objExcel.Sheets("Select Rebate Suppliers").Select()
objExcel.ActiveSheet.Unprotect(Password:="")
objExcel.Sheets("Add Tier Details").Select()
objExcel.ActiveSheet.Unprotect(Password:="")
objExcel.Workbooks(1).Save()
objExcel.Workbooks.Close()
objExcel = Nothing
Dts.TaskResult = Dts.Results.Success

 End Sub

==============================================================

This runs fine from BIDS as well as DtExecUI but whenever I tried to execute as a scheduled Sql job in a x64 Server it failed with the error: (Note, it runs fine even from job in x86 platform)

Error: 2010-03-25 19:00:46.74
Code: 0x00000002
Source: <Script_Task_Name>
Description: The script threw an exception: Open method of Workbooks class failed
End Error

My further investigation indicated that it is due to some DCOM permission settings for Microsoft Excel Application in Component Services. We need to run the command MMC comexp.msc, go to the properties of Microsoft Excel Application, under Identity, change it to The Interactive User from The Launching User (which is set by default). After making this change I was able to run the package as a scheduled Sql job successfully. However, there is still 1 little caveat when we are on x64 Windows 2008 (R2 also) Operating System where Component Services launched in 64 Bit does not show Microsoft Excel Application. In such a scenario we need to launch Component Services in x86 mode with the command MMC comexp.msc /32, rest of things remain the same.

 

Author : Debarchan(MSFT), SQL Developer Engineer, Microsoft 

Reviewed by : Jason(MSFT), SQL Escalation Services, Microsoft