I saw the last post was back in August. Was a solution ever found? I'm having the same type of problem using VB as the language for an SSIS Script Task in BIDS 2008. I'm trying to reference System.Runtime.InteropServices to programmatically open a set of Excel workbooks, run a macro in each, then close the files. These workbooks are used later as data sources for a data flow task.
I used thearticle from http://support.microsoft.com/kb/306682/EN-USas a starting point. A windows application built using VS2005 Professional using VB works fine.
I then tried porting over to a script task in SSIS using BIDS 2008 and hit the same roadblock described above.
------------------------------------------------
The application event log, as in the posts above, gives the description:
The global template information is out of date. Regenerate the templates by running 'VSTA.exe /installvstemplates' or reinstalling the application. Note: corrective action requires Administrator privileges.
----------------------------------------------------------------------
My script code thusfar:
' 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
Imports
System.Runtime.InteropServices
<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
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts property. Connections, variables, events,
' and logging features are available as members of the Dts property as shown in the following examples.
'
' To reference a variable, call Dts.Variables("MyCaseSensitiveVariableName").Value
' To post a log entry, call Dts.Log("This is my log text", 999, Nothing)
' To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, True)
'
' To use the connections collection use something like the following:
' ConnectionManager cm = Dts.Connections.Add("OLEDB")
' cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"
'
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Help, press F1.
Public Sub Main()
'
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oBooks As Excel.Workbooks
oExcel = CreateObject(
"Excel.Application")
oExcel.Visible =
True
oBooks = oExcel.Workbooks
oBook = oBooks.Open(
\\xxxxxxxxxxxxxxxxxx\Occupation Codes.xls) [path obfuscated]
oBook.Close(
False)
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook)
oBook =
Nothing
System.Runtime.InteropServices.Marshal.ReleaseComObject(oBooks)
oBooks =
Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel)
oExcel =
Nothing
'
Dts.TaskResult = ScriptResults.Success
End Sub
End
Class
--------------------------------------------
Error list descriptions:
Type 'Excel.Application' is not defined.
Type 'Excel.Workbook' is not defined.
Type 'Excel.Workbooks' is not defined.
Other recent topics
