June 30, 2010 at 12:36 pm
Hi All,
I'm attempting to create a new connection manager in an SSIS package using the script task. This is SSIS for SQL Server 2008 (building in VS 2008).
I pulled the VB code from http://msdn.microsoft.com/en-us/library/ms136093.aspx. I removed the file connection portion as I am not interested in that. This leaves me with the following:
Imports Microsoft.SqlServer.Dts.Runtime
Module Module1
Sub Main()
' Create a package, and retrieve its connections.
Dim pkg As New Package()
Dim pkgConns As Connections = pkg.Connections
' Add an OLE DB connection to the package, using the
' method defined in the AddConnection class.
Dim myOLEDBConn As New CreateConnection()
myOLEDBConn.CreateOLEDBConnection(pkg)
' View the new connection in the package.
Console.WriteLine("Connection description: {0}", _
pkg.Connections("SSIS Connection Manager for OLE DB").Description)
' View the second connection in the package.
Console.WriteLine("Connection description: {0}", _
pkg.Connections("SSIS Connection Manager for Files").Description)
Console.WriteLine()
Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count)
Console.Read()
End Sub
End Module
' This class contains the definitions for multiple
' connection managers.
Public Class CreateConnection
' Private data.
Private ConMgr As ConnectionManager
' Class definition for OLE DB provider.
Public Sub CreateOLEDBConnection(ByVal p As Package)
ConMgr = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Initial Catalog=AdventureWorks;" & _
"Data Source=(local);"
ConMgr.Name = "SSIS Connection Manager for OLE DB"
ConMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database."
End Sub
End Class
I ran a build on the code successfully. However, when I execute the task it fails with error "Error: Cannot load script for execution."
Admittedly I am not very seasoned at VB but this seems fairly straightforward. Does anyone know how I can either fix this code to run or is there another approach to this problem?
Thanks.
June 30, 2010 at 1:19 pm
I was able to get the script to execute. The code is as follows:
' 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
' 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.
Sub Main()
' Create a package, and retrieve its connections.
Dim pkg As New Package()
Dim pkgConns As Connections = pkg.Connections
' Add an OLE DB connection to the package, using the
' method defined in the AddConnection class.
Dim myOLEDBConn As New CreateConnection()
myOLEDBConn.CreateOLEDBConnection(pkg)
'Console.WriteLine()
'Console.WriteLine("Number of connections in package: {0}", pkg.Connections.Count)
'Console.Read()
End Sub
Public Class CreateConnection
' Private data.
Private ConMgr As ConnectionManager
' Class definition for OLE DB provider.
Public Sub CreateOLEDBConnection(ByVal p As Package)
ConMgr = p.Connections.Add("OLEDB")
ConMgr.ConnectionString = "Provider=SQLNCLI10.1;" & _
"Integrated Security=SSPI;Initial Catalog=AdventureWorks;" & _
"Data Source=NSIMMONSPC\\SQLDEV1;"
'ConMgr.Name = "SSIS Connection Manager for OLE DB"
ConMgr.Name = "SSIS_test"
ConMgr.Description = "OLE DB connection to the AdventureWorks2008R2 database."
End Sub
End Class
End Class
The issue was that I had originally omitted the following code
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Now that the script runs, it doesn't do what I expected. I was expecting to see a new connection manager show up in the package. If this connection manager is only available during the execution of the package, how can I use this newly created connection manager in a data flow task?
Thanks!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply