Data Access Technologies

(Data Access, XML, SSIS, LINQ, System.Data ...)

The SSIS and Excel Story Continues

The SSIS and Excel Story Continues

  • Comments 11

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

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • 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 :)

  • Hey Mohan,

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

    HTH!

    Debs!

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

  • 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

  • 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

    C:\Windows\SysWOW64\config\systemprofile\Desktop

    C:\Windows\System32\config\systemprofile\Desktop

Page 1 of 1 (11 items)