SSIS VB 2008 Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

SSIS VB 2008 Help

Author

Topic

tooba111
Starting Member

22 Posts

Posted - 2014-12-31 : 10:34:52

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources