Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
refresh existing excel file with ssis VB script

Answered refresh existing excel file with ssis VB script

  • Tuesday, January 22, 2013 5:44 PM
     
     
    Sign In to Vote
    0
    Hi All, I tried your solution and I got two errors and one warnning:
    Error: 0x4 at Script Task: The binary code for the script is not found. 
    Error: 0xC0024107 at Script Task: There were errors during task validation.
    Warning: 0x80019002 at Package_REFRESH_EXCEL_FILE: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. 



    I run like admin
    the system is win 7/64bit with SQL 2008 r2
    I had added the Microsoft.Office.Interop.Excel  without problem
    The excel file is share locally
    '--------------the code start
    ' Microsoft SQL Server Integration Services Script Task
    ' Write scripts using Microsoft Visual Basic 2008.
    ' The ScriptMain is the entry point class of the script.
    Imports System
    Imports System.Data
    Imports System.Math
    Imports Microsoft.SqlServer.Dts.Runtime

    <System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
    <System.CLSCompliantAttribute(False)> _
    Partial Public Class ScriptMain
    Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
    Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
    Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum



    ' To open Help, press F1.
        Public Sub Main()
            '
            ' Add your code here
            '
            Dts.TaskResult = ScriptResults.Success

            Dim excel As New Microsoft.Office.Interop.Excel.Application
            Dim wb As Microsoft.Office.Interop.Excel.Workbook

         
            wb = excel.Workbooks.Open("\\Computer_name\GM\test\TEST.xlsx")
            wb.RefreshAll()
            Threading.Thread.Sleep(300)
            wb.Save()
            wb.Close()

            excel.Quit()
            Runtime.InteropServices.Marshal.ReleaseComObject(excel)
        End Sub
        
    End Class


    '--------------the code end 

    What could be  missing?
    which book do you recomend me to start learn Vbasic for SQL ssis? I am new in this type of stuff.

    I really need help..

    thanks in advance

    cris


    • Edited by perezco Tuesday, January 22, 2013 5:44 PM grammar correction
    •  
     

All Replies

  • Tuesday, January 22, 2013 5:53 PM
    Moderator
     
     
    Sign In to Vote
    0

    Hi Chris,

    Why would you refresh an Excel file?

    At what line it dies?

    I think you need to install Excel itself in addition to the interop libs. This is why it does not work.

    Not sure why you are asking about books. VB and SSIS are different things, chose a priority.

    I can recommend of a SSIS book: http://www.amazon.com/dp/0470247959/?tag=stackoverfl08-20 for SSIS 2008 or http://amzn.to/WTq4rE for 2012

    Arthur My Blog

     
  • Tuesday, January 22, 2013 5:53 PM
     
     
    Sign In to Vote
    0

    Hi,

    1. Check that Excel is installed on the machine where your SSIS package runs.

    2. Don't forget to build your script code once you finished typing or editing it. SSIS needs your script to be "built" before launching it (that's the 0x4 error)

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu


     
  • Tuesday, January 22, 2013 6:06 PM
     
     
    Sign In to Vote
    0

    Why would you refresh an Excel file?

    >>> b/C  the file have multiple tabs with Microsft SQL Query conenctions

    The system has already installed Office 2010 (that include MS excel 2012) .. in 64bit version:  14.0.6106.5005

    Thank you for your prompt response!!! :)

    cris



    • Edited by perezco Tuesday, January 22, 2013 6:26 PM restate sentence
    •  
     
  • Tuesday, January 22, 2013 6:36 PM
    Moderator
     
     
    Sign In to Vote
    0
    Perhaps this is due to the fact you operate on a share - e.g. no permission to refresh this data source. Again, at what step the code breaks?

    Arthur My Blog

     
  • Tuesday, January 22, 2013 8:23 PM
     
     
    Sign In to Vote
    0

    I change >>

    wb = excel.Workbooks.Open("\\Computer_name\GM\test\TEST.xlsx")

    for 

    wb = excel.Workbooks.Open("F:\SQL_software\test\TEST.xlsx")     ''FULL permission to me"

    now it one one error and one warring

    Error: 0x1 at Script Task: Cannot execute script because the script entry point is invalid.
     
    Warning: 0x80019002 at Package_REFRESH_EXCEL_FILE: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. 


    Regards, 

    Cristina

    cris

     
  • Tuesday, January 22, 2013 8:28 PM
     
     Answered
    Sign In to Vote
    0

    Hi,

    Error 0x1 usually comes if you remove some of the autogenerated script code (the source lines created by SSIS, not the ones you type)

    Solution : copy/paste your script code into Notepad, delete the script component, create a new one, repaste your code and rebuild it.

    On the other hand, I recommend to avoid mapped drives (like F:) on SSIS components.

    SSIS runs under a Windows user of its own, not necessarily yours. That means that your F: may not be visible to SSIS.

    Better stick to the \\computername\sharename\... notation

    Sebastian Sajaroff Senior DBA Pharmacies Jean Coutu

    • Marked As Answer by perezco Tuesday, January 22, 2013 9:10 PM
    •  
     
  • Tuesday, January 22, 2013 9:10 PM
     
     
    Sign In to Vote
    0

    It work !!!!

    "Solution : copy/paste your script code into Notepad, delete the script component, create a new one, repaste your code and rebuild it."

    Thanks Sebastian,  Arthur !!! :)

    cris