다음을 통해 공유

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

Comments

  • Anonymous
    May 26, 2011
    The comment has been removed

  • Anonymous
    October 13, 2011
    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

  • Anonymous
    November 03, 2011
    Thanks so much. Exactly what i was looking for :)

  • Anonymous
    May 17, 2012
    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 :)

  • Anonymous
    May 31, 2012
    Hey Mohan, Sorry, I somehow missed your post. Do you still have the problem? HTH! Debs!

  • Anonymous
    August 07, 2013
    Is there any way to unprotect the sheet without open the workbook?

  • Anonymous
    January 23, 2014
    I followed the above steps. still the script fails in Job

  • Anonymous
    February 25, 2014
    Thank you so very much, this obscured thing had me puzzled and debugging for hours before I got to this article...

  • Anonymous
    April 28, 2014
    Mohan Deval 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

  • Anonymous
    July 07, 2014
    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.

  • Anonymous
    July 15, 2014
    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 C:WindowsSysWOW64configsystemprofileDesktop C:WindowsSystem32configsystemprofileDesktop

    • Anonymous
      December 20, 2018
      @Michael Gyekye you are a hero

  • Anonymous
    October 16, 2014
    @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.

  • Anonymous
    October 16, 2014
    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

  • Anonymous
    October 28, 2014
    If the account which is running EXCEL is administrator then this will work: For 64-bit (x64), create this folder: C:WindowsSysWOW64configsystemprofileDesktop For 32-bit (x86), create this folder: C:WindowsSystem32configsystemprofileDesktop 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

  • Anonymous
    February 11, 2015
    This worked like a charm. Thanks for sharing the knowledge.

  • Anonymous
    March 10, 2015
    Snehadeep thanks for the advice! I tried the "solutions" mentioned in MSDN, I followed a thousand and more procedures described in the official forums, but none had worked ... so far. Now it took was a click and the infamous job went smoothly! Again, thank you, you saved my sanity and my sleep !!!

  • Anonymous
    June 01, 2015
    The solution about creating the 'desktop' folders works, but seems very nonsensical.  It would have been better if the authors of this solution from MS would have shared the reasoning behind why this works.  

  • Anonymous
    August 18, 2015
    Worked perfectly for me. Thank you

  • Anonymous
    February 27, 2017
    Great thank you....................problem has been solved,

  • Anonymous
    March 22, 2018
    Hi All Doing the DCOM permission changes worked for me However it only works when the User Account running the SQL Agent is actually logged into the Server. If the account is not logged in, then it fails.Ideas anyone?Cheers

  • Anonymous
    May 02, 2018
    This did the trick for me,Thanks.