Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
error job After got SSIS VB Script be able to refresh existing excel file without human intervention

Answered error job After got SSIS VB Script be able to refresh existing excel file without human intervention

  • Friday, January 25, 2013 11:06 PM
     
     
    Sign In to Vote
    0

    Hi everyone,

    After got  SSIS VB Script be  able to refresh existing excel file  without human intervention,

    and changed the security level to No encryption.

    My sql server services are:

    Common Name

    State

    Log on

    SQL Server

    Running

    Domain\My_user

    SQL Server Agent

    Running

    Domain\My_user

    SQL Server Analysis Services

    Running

    Domain\My_user

    SQL Server Browser

    Stop

    NT_autorithy\local_Server

    SQL Server Full Text Search

    Running

    NT_autorithy\local_Server

    SQL Server Integration Services

    Running

    Domain\My_user

    SQL Server Reporting Services

    Running

    NT_autorithy\networkservices

     

     I Still NOT be able to get work in JOB.

    What could be the issue?

    message:

    -------------------Start error message

    Executed as user: domain\my_user. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.4000.0 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  5:45:54 PM  Error: 2013-01-25 17:45:56.11     Code: 0x00000001     Source: Script Task      Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Runtime.InteropServices.COMException (0x800A03EC): Microsoft Excel cannot access the file '\\computer_name\test\test.xlsm'. There are several possible reasons:   

    ? The file name or path does not exist. 

    ? The file is being used by another program. 

    ? The workbook you are trying to save has the same name as a currently open workbook.    

    at Microsoft.Office.Interop.Excel.Workbooks.Open(String Filename, Object UpdateLinks, Object ReadOnly, Object Format, Object Password, Object WriteResPassword, Object IgnoreReadOnlyRecommended, Object Origin, Object Delimiter, Object Editable, Object Notify, Object Converter, Object AddToMru, Object Local, Object CorruptLoad)    

    at ST_6ff4b1e36fef45a5b3d3f0c9e3b0c825.vbproj.ScriptMain.Main()     --- End of inner exception stack trace ---    

    at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)    

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)    

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)    

    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)    

    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()  End Error  DTExec: The package execution returned DTSER_FAILURE (1). 

    Started:  5:45:54 PM  Finished: 5:45:56 PM  Elapsed:  1.498 seconds.  The package execution failed.  The step failed.

    -------------------End error message

    fails in SQL Agent Job (works when executed from BIDS)!!!

     I tested on the server itself, or on my machine for development and I get the same thing

    Thanks in advance!!



    cris


    • Edited by perezco Friday, January 25, 2013 11:07 PM restate sentence
    • Edited by perezco Friday, January 25, 2013 11:31 PM restate sentence
    • Edited by perezco Saturday, January 26, 2013 1:42 AM cosmetic change
    • Edited by perezco Saturday, January 26, 2013 1:45 AM cosmetic look
    • Edited by perezco Saturday, January 26, 2013 3:14 AM cosmetic
    • Edited by perezco Saturday, January 26, 2013 3:26 AM cosmetic
    • Edited by perezco Saturday, January 26, 2013 3:28 AM adding details
    •  
     

All Replies

  • Friday, January 25, 2013 11:32 PM
    Moderator
     
     
    Sign In to Vote
    0

    The message says that the process cannot access the file.  If you have verified the file path is valid and that the file exists then the issue is the file security.  If you are running the package from Visual Studio then make sure that your, or the security account you are running under, has permission to the file.  From the message you are using a shared folder so insure that the share and NTFS permission allow your account access.  If you are running the package from SQL Server Agent then make sure that the SQL Server Agent account has the permissions, both NTFS and share permissions, to access the file.

    Hope this helps

    David Dye My Blog

     
  • Saturday, January 26, 2013 1:01 AM
     
     
    Sign In to Vote
    0

    The Dtsx come only with one  VB script, and run fine.

    No problem with the share

    No problem with the Visual Studio

    ......

            ' Disable Excel UI elements

         

            excel.Visible = False 

            excel.DisplayAlerts = False

            excel.AskToUpdateLinks = False

            excel.AlertBeforeOverwriting = False

            excel.EnableEvents = False 

         .......

    Or maybe could be related to some COM permission settings for Microsoft Excel Application in Component Services??? security setting?? Microsoft Excel application

    I am very newbie in run VB script in dtsx. 

    I got those UI elements from 'Microsoft.Office.Interop.Excel'

    I am open to other approach to get excel refresh.

    Excel 2010   is installed it, sql server 2008 r2

    Regards,

    cris

    • Edited by perezco Saturday, January 26, 2013 1:16 AM adding details
    • Edited by perezco Saturday, January 26, 2013 1:39 AM adding more details
    • Edited by perezco Saturday, January 26, 2013 1:49 AM add details
    • Edited by perezco Saturday, January 26, 2013 1:53 AM adding details
    • Edited by perezco Saturday, January 26, 2013 4:42 AM adding more details
    •  
     
  • Saturday, January 26, 2013 11:12 AM
    Moderator
     
     
    Sign In to Vote
    0

    Two often seen problems when it works in BIDS but not as JOB:

    1) Security: the jobstep doesn't use your account: http://microsoft-ssis.blogspot.com/2012/09/who-is-running-package.html
    2) 32bit: Excel provider is 32bit: http://microsoft-ssis.blogspot.com/2012/09/swtich-package-from-64bit-to-32bit.html

    And since you're using the Microsoft.Office.Interop.Excel assembly, you need Office (/excel) installed on your server (no best practice/not supported).

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Saturday, January 26, 2013 3:57 PM
     
     
    Sign In to Vote
    0

    The two systems ( the one for production and the other one for envelopment) are be able to run multiple other Job(s).

    both has the same version of SQL server 2008 r2 in X64, WIn 7 in x64, and office 2010 installed in x64 (Ms Excel 2010is included in the installation).

    The Microsoft Office 2010: Primary Interop Assemblies Redistributable is already installed in both systems too...

    cris


    • Edited by perezco Saturday, January 26, 2013 4:18 PM adding coments
    •  
     
  • Saturday, January 26, 2013 4:09 PM
    Moderator
     
     
    Sign In to Vote
    0

    The two systems ( the one for production and the other one for envelopment) are be able to run multiple other Job(s).

    both has the same version of SQL server 2008 r2 in X64, WIn 7 in x64, and office 2010 installed in x64.

    The Microsoft Office 2010: Primary Interop Assemblies Redistributable is already installed in both systems too...

    cris


    Installing Primary Interop Assemblies is not the same as installing office. PIA is only a communicator between .Net and the COM libraries from office.

    Please mark the post as answered if it answers your question | My SSIS Blog: http://microsoft-ssis.blogspot.com | Twitter

     
  • Saturday, January 26, 2013 4:22 PM
    Moderator
     
      Has Code
    Sign In to Vote
    0

    You stated earlier that you were open to another means of accomplishing this and I think it would be much cleaner to use a SQL Agent job with a PowerShell task that will refresh the spreadsheey.  Possibly something like below 

    $ExcelApp = new-object -ComObject Excel.Application
$ExcelApp.Visible = $false
$ExcelApp.DisplayAlerts = $false
$Workbook = $ExcelApp.Workbooks.Open("RefreshTest.xlsx", 3, $false, 5, $null, $null, $true)
$Workbook.RefreshAll()
$Workbook.Save()
$ExcelApp.Quit()
    This post might also be helpful:
    http://dbcodesnippets.blogspot.com/2012/12/powershell-script-to-refresh-excel-data.html

    David Dye My Blog

     
  • Saturday, January 26, 2013 4:28 PM
     
     
    Sign In to Vote
    0

    On Monday, I will try these two options at least first in the development system: 

    Excel Interop: System.Runtime.InteropServices.COMException : Microsoft Excel cannot access the file

    SETTING DCOM SECURITY

    Based on these two links:

    http://social.technet.microsoft.com/Forums/en/winserverpowershell/thread/99628eb9-d0c4-4736-87cc-6a69f43e5de2

    http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/bbd4fa90-af9a-451b-b04c-f1e7161fe5b4

    I will be posting which one work

    Regards,

    cris


    • Edited by perezco Saturday, January 26, 2013 8:28 PM cosmetic
    •  
     
  • Saturday, January 26, 2013 8:36 PM
     
     
    Sign In to Vote
    0

    1) Security: the jobstep doesn't use your account: http://microsoft-ssis.blogspot.com/2012/09/who-is-running-package.html  

    >>> I will try this one too, 

    thanks

    cris

     
  • Monday, January 28, 2013 3:00 PM
     
     Answered
    Sign In to Vote
    0

    AMAZING but works!!!!!!!!

    the first thing that I test on Monday!!!!!

    On Monday, I will try these two options at least first in the development system: 

    Excel Interop: System.Runtime.InteropServices.COMException : Microsoft Excel cannot access the file

    cris

    • Marked As Answer by perezco Monday, January 28, 2013 3:01 PM
    •