SSIS VB 2008 Help

• rocky_498

  SSCertifiable

  Points: 6532

  More actions

  December 31, 2014 at 8:34 am

  #397821

  Hi Guys,

  Simple Script Task, just rename Excel tab, here is my code...

  Imports System

  Imports System.Data

  Imports System.Math

  Imports Microsoft.SqlServer.Dts.Runtime

  Imports Microsoft.Office.Interop.Excel

  Imports System.IO

  Imports System.Text

  <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

  Public Sub Main()

  Dts.VariableDispenser.LockForRead("User::FileName") ' File Name Variable

  Dim variablesList As Variables

  Dts.VariableDispenser.GetVariables(variablesList)

  'Dim SFileName As String

  'SFileName = variablesList("User::Filename").Value.ToString

  ' Dim vars As Variables 'New Added For Variable

  Dim oMissing As Object = System.Reflection.Missing.Value

  Dim xl As New Microsoft.Office.Interop.Excel.ApplicationClass()

  Dim xlBook As Microsoft.Office.Interop.Excel.Workbook

  Dim xlSheet As Microsoft.Office.Interop.Excel.Worksheet

  'Dts.VariableDispenser.LockForRead("Filename") 'New Added For Variable

  'Start For Variable

  'Dim File As String 'New Added For Variable

  'File = CType(vars("Filename").Value, String) 'New Added For Variable

  'MsgBox(Prompt:="Filename")

  'Dim laPath As String = "C:\Excel\ABC_dr_daily_lf_10-07-14.xls" 'My File Name

  Dim lapath As String = variablesList("User::FileName").Value.ToString 'New Added For Variable

  'MsgBox(Prompt:="Filename")

  xlBook = DirectCast(xl.Workbooks.Open(laPath, oMissing, oMissing, oMissing, oMissing, oMissing, _

  oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, _

  oMissing, oMissing, oMissing), Workbook)

  xlSheet = DirectCast(xlBook.Worksheets.Item(1), Worksheet)

  xlSheet.Name = "data"

  xlBook.Save()

  xl.Application.Workbooks.Close()

  Dts.TaskResult = ScriptResults.Success

  End Sub

  End Class

  Finally I intalled MS Excel 2007 on my Dev Server. Here is the error that I am receiving....

  Error: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.InvalidCastException:

  Unable to cast COM object of type 'Microsoft.Office.Interop.Excel.ApplicationClass' to interface type 'Microsoft.Office.Interop.Excel._Application

  '. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D5-0000-0000-C000-000000000046}' failed due to the following error: Library not registered.

  Please help me, this is very urgent.

  Thank You.