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.Sheets(“Define Rebate”).Select()
        objExcel.Sheets(“Select Rebate Suppliers”).Select()
        objExcel.Sheets(“Add Tier Details”).Select()
        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 (18)

  1. Mohan Deval says:

    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.

  2. Darth Vodka says:

    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

  3. sh0ck_wave says:

    Thanks so much. Exactly what i was looking for :)

  4. G3 says:

    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 :)

  5. Debarchan says:

    Hey Mohan,

    Sorry, I somehow missed your post. Do you still have the problem?



  6. biru says:

    Is there any way to unprotect the sheet without open the workbook?

  7. Ajil says:

    I followed the above steps. still the script fails in Job

  8. Ivan says:

    Thank you so very much, this obscured thing had me puzzled and debugging for hours before I got to this article…

  9. Todd V says:

    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

  10. ClaytonCo says:

    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.

  11. Michael Gyekye says:

    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



  12. Ramesh says:

    @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.

  13. Vikas says:

    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

  14. Suman Akkisetty says:

    This worked like a charm. Thanks for sharing the knowledge.

  15. Jovally says:

    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 !!!

  16. Isaac says:

    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.  

  17. Lee Everest says:

    Worked perfectly for me.

    Thank you