Script Task to update table using Connection Manager connection
-
All, I'm sure this is something pretty simple, but I've ran into a mental block (plus I'm pretty new to this vbscript stuff). I've got the below code, basically, it takes a connection string from a variable set, tests it's connection, decides if its active or inactive, then it's supposed to update a table with that status. The connection it is supposed to use is thru a connection in Connection Manager. I've bolded the lines I'm having an issue with.
Here's where I'm blocked: How do I access this connection from within the Script Task (it's an OLEDB connection), and what are the commands that I need to use to pass the UPDATE query to that connection to execute?
Any help is greatly appreciated, I'm at a loss for what else to google search on.
Imports System
Imports System.Data
Imports System.Math
Imports System.Data.SqlClient
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
Public Sub Main()
Dim conn As New SqlClient.SqlConnection("source")
Dim bFailure As Boolean = False
Dim Connection As String = Dts.Variables("TestConnectionString").Value.ToString
Dim sStatus As String = Dts.Variables("sStatus").Value.ToString
Dim strSQLQuery As String = "UPDATE DBA_Admin.dbo.ServerConnectString SET Status = "
Dim strSQLQueryEnd As String = " WHERE ConnectionString = '"
conn.ConnectionString = Connection.Substring(21, Connection.Length() - 21)
strSQLQueryEnd = strSQLQueryEnd + Connection
Try
conn.Open()
Catch ex As Exception
bFailure = True
Finally
conn.Close()
End Try
If bFailure = True Then 'Could not connect using connect string
sStatus = "Inactive"
Else 'Successful connect using connect string
sStatus = "Active"
End If
'Now update the Status = sStatus in DBA_Admin.dbo.ServerConnectStrings
strSQLQuery = strSQLQuery & "'" & sStatus & "' " & strSQLQueryEnd & "'"
Dts.Connections("source").AcquireConnection(Nothing) ' These are the lines that I am having problems with.
Dim cmd As SqlCommand = New SqlCommand(strSQLQuery) '
conn.Open() '
cmd.ExecuteNonQuery() '
conn.Close() '
conn = Nothing '
Dts.TaskResult = ScriptResults.Success
End Sub
End Class
-
You CANNOT uses OLEDB connections from script. You have to use ADO.NET connections instead.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
You can use OLEDB connection in Script Task as below.
Dim cm As ConnectionManager
Dim cmParam As Wrapper.IDTSConnectionManagerDatabaseParameters100
cm = Dts.Connections("oledb connection name")
cmParam = CType(cm.InnerObject, Wrapper.IDTSConnectionManagerDatabaseParameters100)
Dim conn As OleDb.OleDbConnection = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand
cmd.Connection = conn
You have to add DTSRuntimeWrap reference to make this work
Dipak
-
Dipak,
Of course you are correct. I forgot about this approach. There is one shortcoming though. You cannot enlist in the current transaction.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply