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
============================================================== 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
You are Great..... I passed the Excel hurdle, but now my SSIS Job Agent proxy account Screams at me saying cannot create ActiveX Component. Which additional permissions I need to give to create an AcvtiveX Component...? Please help.
many many thanks, slight addendum from me though
the interactive user...there won't be one if you aren't logged on the server and I got a
failed due to the following error: 8000401a
from the script task, but setting the user explicitly to a network user instead of interactive one worked
Thanks so much. Exactly what i was looking for :)
You are such a life saver mate... took us weeks of headache and I finally stumble on your blog! Am smiling now and thanks to you, this works a charm :)
Sorry, I somehow missed your post. Do you still have the problem?
Is there any way to unprotect the sheet without open the workbook?
I followed the above steps. still the script fails in Job
Thank you so very much, this obscured thing had me puzzled and debugging for hours before I got to this article...
make sure you are running the Job as your SQL Proxy account, and that account is also the owner. I think you can also set the owner to sa, but it must run as the SQL Proxy account/credential
Thank you very much. I've been searching for an answer to this for two weeks now. I was even able to delete my proxy's. Thanks.
MMC configurations did not work for me, but this worked for me.
Make sure these 2 folder paths exists on the server and the run job again
@Michael Gyekye, you are my hero of the day.I have been struggling with this problem for four days now. Finally your approach worked for me.
Ramesh and Michael,
That's actually another interop issue blogged here - blogs.msdn.com/.../error-microsoft-office-excel-cannot-access-the-file-while-accessing-microsoft-office-11-0-object-library-from-ssis.aspx
If the account which is running EXCEL is administrator then this will work:
For 64-bit (x64), create this folder: C:\Windows\SysWOW64\config\systemprofile\Desktop
For 32-bit (x86), create this folder: C:\Windows\System32\config\systemprofile\Desktop
Otherwise To resolve this issue follow these steps:
1. Login to your Server as a administrator
2. Go to "Start" -> "Run" and enter "MMC comexp.msc /32"
3. 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).
4. Go to the properties of Microsoft Office Excel 2007 Workbook, under Identity, change it to The Interactive User from The Launching User (which is set by default).
5. Go to Security tab for Microsoft Excel Application and select Customize for
" Launch and Activation Permissions" and add ACCOUNT (under which EXCEL is running) to it and give it "Local launch" and "Local Activation" permission
6. Go to Security tab for Microsoft Office Excel 2007 Workbook and select Customize for
" Access Permissions " and add ACCOUNT (under which EXCEL is running) to it and give it "Local Access" permission
This worked like a charm. Thanks for sharing the knowledge.