ConnectionString Task

I needed a task, which gets all defined connections of a SSiS module, iterates over these and constructs valid connection strings for these, which could be used to connect via .Net SQLClient class to the defined database.
To control which one of the managers we want to use, we define a variable named "varConnectionManagerName".
There are several possibilities which one of the defined connections will be written into the output variables named as "varConnectionString_<Name 1...n>", which must be also defined as variables. Expression <Name_1> is the term we wrote into the variable varConnectionManagerName for defining the 'right' connection!

Cases

1. varConnectionManagerName doesn't exist
We build the string from the first found connection and write out the string into the variabe
varConnectionString

2. varConnectionManagerName exists and contains one connection name
We build the string from the connection defined and write out the string into the variabe "varConnectionString"

3. varConnectionManagerName exists and contains more than one connection names (separated by a coma)
We build the strings from the connections defined and write out the strings into the variabes
varConnectionString_<Name 1>, varConnectionString_<Name 2>, ...

If there is no SSPI (System accout login), the variable "Password" must be defined with the valid password for the server login. Becarefll with that, becuase it is stored in cleartext form. But as far as I know, there is no other possibility, brecause the password is not stored in the connection and the DTSX file, depending on the seurity level!

Here is the code for this script task

Code

'_________________________________________________________________________________
' This script task gets a Connection string and shpoud be the first task in a
' package. It also uses the internal Logging stufff coded in the MyLog class, which
' uses the variable "Logging"
' Used Read variables
' varConnectionManagerName = Name with a valid Connection Manager Name (or more)
' only, if no ConnectionManagerName is given
' Servername = Variable with the Servername to connect to
' Database = Variable with the Database to connect to
' Username = Variable with the Username used to connect to Server/Database
' Database = Variable with the Password used to connect to Server/Database
' Used Write Variables (Depending on the content of varConnectionManagerName)
' varConnectionString [Only one ConnectionManagerName used]
' varConnectionString_<ConnectionManagerName> [at least 2 ConnectionManagers used]
'_________________________________________________________________________________

Imports System
Imports System.Text
Imports System.Data
Imports System.Math
Imports System.Collections.Generic
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
Private mConnection As ConnectionStringItererator

Public Sub Main()
Dim result As Integer
Try
mConnection = New ConnectionStringItererator()
mConnection.init()
result = Dts.Results.Success
Catch ex As Exception
result = Dts.Results.Failure
Dts.Events.FireError(&H1, ex.Source, ex.ToString, "", 0)
End Try
Dts.TaskResult = result
End Sub

Public Class ConnectionStringItererator
Private mConnectionManagers As String
Private mConnection As Dictionary(Of String, ConnectionString)

Sub New()
mConnection = New Dictionary(Of String, ConnectionString)
End Sub

Sub init()
Dim var As MyVariables = New MyVariables(Dts)
Dim v As String()
Dim i As Integer
Dim connectionString As ConnectionString

mConnectionManagers = var.Read("varConnectionManagerName").ToString

v = Me.mConnectionManagers.Split(","c)
For i = 0 To v.Length - 1
connectionString = New ConnectionString(Dts)
connectionString.VariableNames("") = var.VariableNames("")
mConnection.Add(v(i), connectionString)
connectionString.ConnectionManagerName = v(i)
If v.Length > 1 Then
connectionString.ConnectionStringName = "varConnectionString_" + v(i)
Else
connectionString.ConnectionStringName = "varConnectionString"
End If
connectionString.process()
Next
End Sub
End Class

' _______________________________________________________________________
' Complete handling for getting a valid connection string
' This function is with little modifications also usable in DataFlow Tasks
'
' Needed variables
' varConnectionManagerName = Name with a valid Connection Manager Name
' Servername = Variable with the Servername to connect to
' Database = Variable with the Database to connect to
' Username = Variable with the Username used to connect to Server/Database
' Database = Variable with the Password used to connect to Server/Database
' _______________________________________________________________________
Public Class ConnectionString
Inherits MyLog

Private mConnectionManagerName As String
Private mServername As String
Private mDatabase As String
Private mUsername As String
Private mPassword As String
Private mConnectionString As String
Private mIsChecked As Boolean
Private mVarConnectioNStringName As String

' ********************************************
' Use this for ControlFlow Task
' *********************************************
Private mConnections As Connections

' *********************************************
' Use this for DataFlow Task
' *********************************************
'Private mConnections As IDTsConnections

'___________________________________________________________________
' Constructor
' Parameter
' Main : the ScriptTask itself / DTS
' varServername : Variablenname of the SSiS-variable which containss the Servername to connect to
' varDatabase : Variablenname of the SSiS-variable which contains the Database to connect to
' varUsername : Variablenname of the SSiS-variable which contains the Username
' varPassword : Variablenname of the SSiS-variable which contains the password
'___________________________________________________________________
Sub New(ByRef Main As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel, Optional ByVal varServername As String = "", Optional ByVal varDatabase As String = "", Optional ByVal varUsername As String = "", Optional ByVal varPassword As String = "")
MyBase.New(Main, "Logging")
Try

mMain = Main

mConnections = Dts.Connections
'mConnections = mMain.ComponentMetaData.RuntimeConnectionCollection

Me.log(Me.LOG_UserVars, "", "")
Me.log(Me.LOG_SysVars, "", "")

mVarConnectioNStringName = "varConnectionString"
mConnectionString = ""
mConnectionManagerName = ""
If varServername.Length > 0 Then mServername = Me.Read(varServername).ToString() Else mServername = ""
If varDatabase.Length > 0 Then mDatabase = Me.Read(varDatabase).ToString() Else mDatabase = ""
If varUsername.Length > 0 Then mUsername = Me.Read(varUsername).ToString() Else mUsername = ""
If varPassword.Length > 0 Then mPassword = Me.Read(varPassword).ToString() Else mPassword = ""

Catch ex As Exception
Me.log(Me.LOG_Info, "[1000 Execption ]" + ex.ToString, ex.ToString)
End Try
End Sub

'___________________________________________________________________
' Compute an appropriate ConnectionString
'___________________________________________________________________
Public Function process() As String
process = Me.ConnectionString
End Function

Private ReadOnly Property ConnectionManagerConnectionString() As String
Get
'Me.ConnectionString = mConnections(Me.ConnectionManagerName).ConnectionManager.ConnectionString
ConnectionManagerConnectionString = mConnections(Me.ConnectionManagerName).ConnectionString
End Get
End Property

Public Property ConnectionStringName() As String
Get
ConnectionStringName = mVarConnectioNStringName
End Get
Set(ByVal value As String)
mVarConnectioNStringName = value
End Set
End Property

Public Property ConnectionManagerName() As String
Get
If mConnectionManagerName.Length = 0 Then mConnectionManagerName = Me.Read("varConnectionManagerName", Nothing, "NotExist").ToString()
If String.Compare(mConnectionManagerName, "NotExist") <> 0 Then
If mIsChecked = False Then ' Do this only once
mIsChecked = True
Dim singleConnectionManager As String
Dim i As Integer
Dim isMatching As Boolean

For Each var As ConnectionManager In mConnections
' For Each var As IDTSRuntimeConnection90 In mConnections
If Not var Is Nothing Then
i = i + 1
If i = 1 Then singleConnectionManager = var.Name ' Store the first found, so we could use this, if nothing matches
If String.Compare(var.Name, mConnectionManagerName, True) = 0 Then ' If the ConnectionManager Name is the same like in the given variable
isMatching = True ' Remember this state
mConnectionManagerName = var.Name ' Ok set the property
Me.log(Me.LOG_Verbose, "[1002 ConnectionManagerName ]", "Find matching connection manager : '{0}'", var.Name)
Else
Me.log(Me.LOG_Verbose, "[1003 ConnectionManagerName ]", "Read connection manager : '{0}'", var.Name)
End If
End If
Next
If isMatching = False Then ' Nothing found, so log this state
mConnectionManagerName = singleConnectionManager ' Take the first found
Me.log(Me.LOG_Warning, "[1004 ConnectionManagerName]", "No match found, use first ConnectionManager : '{0}'", singleConnectionManager)
End If
End If
ConnectionManagerName = mConnectionManagerName
Else
ConnectionManagerName = ""
End If
End Get
Set(ByVal value As String)
mConnectionManagerName = value
End Set
End Property

Public Property ConnectionString() As String
Get
If mConnectionString.Length = 0 Then Me.ConnectionString = Me.Read(Me.ConnectionStringName).ToString()
If mConnectionString.Length = 0 Then Me.Search()
ConnectionString = mConnectionString
End Get
Set(ByVal value As String)
If value.Length > 0 Then
mConnectionString = value
Call Me.setProperties()
Call Me.computeString()
End If
End Set
End Property
Public Property Database() As String
Get
Database = mDatabase
End Get
Set(ByVal value As String)
If mConnectionString.Length = 0 Then Call Me.process()
mDatabase = value
Me.computeString()
End Set
End Property
Public Property Servername() As String
Get
Servername = mServername
End Get
Set(ByVal value As String)
If mConnectionString.Length = 0 Then Call Me.process()
mServername = value
Me.computeString()
End Set
End Property
Public Property Username() As String
Get
Username = mUsername
End Get
Set(ByVal value As String)
If mConnectionString.Length = 0 Then Call Me.process()
mUsername = value
Me.computeString()
End Set
End Property
Public Property Password() As String
Get
Password = mPassword
End Get
Set(ByVal value As String)
If mConnectionString.Length = 0 Then Call Me.process()
mPassword = value
Me.computeString()
End Set
End Property

Public Sub makePersistant()
Call Me.Write(Me.ConnectionStringName, Me.ConnectionString)
End Sub

'___________________________________________________________________
' Search an appropriate ConnectionString in the package environement
'___________________________________________________________________
Private Function Search() As String
If Me.ConnectionManagerName.Length = 0 Then ' If there is none, try to make the ConnectionString by our own
Call Me.computeString()
Me.log(Me.LOG_Info, "[1005 Search ]", "Cannot find connection manager by Name/Property, build our own : '{0}'", Me.ConnectionString)
Else ' Ok, we have a ConnectionManager, which could be used
Me.ConnectionString = Me.ConnectionManagerConnectionString
Me.log(Me.LOG_Info, "[1005 Search ]", "Used connection manager : '{0}' : '{1}'", ConnectionManagerName, Me.ConnectionString)
End If

Call Me.makePersistant() ' In each case, write out the ConnectionString

End Function

'___________________________________________________________________
' Build the connection string from OLEDB to standart .net
'___________________________________________________________________
Public Function computeString() As String
If String.Compare(Me.Servername, ".") = 0 Or String.Compare(Me.Servername, "localhost", True) = 0 Or Me.Username.Length = 0 Then
mConnectionString = "Data Source=" + mServername + _
";Initial Catalog=" + mDatabase + _
";Integrated Security=SSPI"
Else
mConnectionString = "Data Source=" + mServername + _
";User ID=" + mUsername + _
";Password=" + mPassword + _
";Initial Catalog=" + mDatabase + _
";Integrated Security=No"
End If
End Function

'___________________________________________________________________
' Set the internal Paramteres from the build up connection string
'___________________________________________________________________
Sub setProperties()
Try
Dim v() As String
Dim keyValues() As String
v = Split(mConnectionString, ";") ' Separate all properties
For i As Integer = 0 To UBound(v) ' Iterate over all properties and take only these ones, which are allowed
keyValues = Split(LCase(v(i)), "=") ' Split the key and values
If UBound(keyValues) > 0 Then ' Ok, there are values
Select Case keyValues(0) ' Separate the intersting ones
Case "data source"
mServername = keyValues(1)
Case "user id"
mUsername = keyValues(1)
Case "password"
mPassword = keyValues(1)
Case "initial catalog"
mDatabase = keyValues(1)
End Select
End If
Next
Catch ex As Exception
Me.log(Me.LOG_Info, "[1001 Execption ]" + ex.ToString, ex.ToString)
End Try
End Sub
End Class

' ********************************************************************
' ********************************************************************
' Standard Logging
' ********************************************************************
' ********************************************************************
Class MyLog
Inherits MyVariables
'================================================
' DataFlow
'Protected mEvents As IDTSComponentMetaData90
'Protected mMain As ScriptMain
'Protected mVars As IDTSVariables90
'================================================
' ControlFlow
'Protected mEvents As IDTSComponentEvents
'Protected mMain As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
'Protected mVars As Variables

Private mSysLogLevel As Integer
Private mID As Integer
Private mShowMsgbox As Boolean
Private mTaskname As String

Public Const LOG_Error As Integer = &H1
Public Const LOG_Warning As Integer = &H2
Public Const LOG_Info As Integer = &H4
Public Const LOG_Verbose As Integer = &H8
Public Const LOG_MsgBox As Integer = &H10
Public Const LOG_SQLStatement As Integer = &H20
Public Const LOG_UserVars As Integer = &H100
Public Const LOG_SysVars As Integer = &H200

'_____________________________________________________________________
' Constructor
'_____________________________________________________________________
Sub New(ByRef main As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel, ByVal LogLevelVariable As String)
MyBase.New(main)
Try
mID = 0
mShowMsgbox = False
mMain = mMain
mTaskname = ""

mEvents = mMain.Events '====== ControlFlow
'mEvents = mMain.ComponentMetaData '====== DataFlow

mSysLogLevel = CInt(Me.Read(LogLevelVariable, "0"))
Catch ex As Exception
mEvents.FireError(1, Me.ToString, ex.ToString, "", 0)
End Try
End Sub

'_____________________________________________________________________
' Property SysLogLevel
'_____________________________________________________________________
Public Property SysLogLevel() As Integer
Get
SysLogLevel = mSysLogLevel
End Get
Set(ByVal value As Integer)
mSysLogLevel = value
End Set
End Property

'_____________________________________________________________________
' Some explicit properties
'_____________________________________________________________________
ReadOnly Property Taskname() As String
Get
If mTaskname.Length = 0 Then mTaskname = Me.Read("System::PackageName").ToString + "." + Me.Read("System::TaskName").ToString
Taskname = mTaskname
End Get
End Property
ReadOnly Property isSysVars() As Boolean
Get
isSysVars = ((mSysLogLevel And Me.LOG_SysVars) > 0)
End Get
End Property
ReadOnly Property isUserVars() As Boolean
Get
isUserVars = ((mSysLogLevel And Me.LOG_UserVars) > 0)
End Get
End Property
ReadOnly Property isMsgBox() As Boolean
Get
isMsgBox = ((mSysLogLevel And Me.LOG_MsgBox) > 0)
End Get
End Property

'_____________________________________________________________________
' Function setID
' Description
' This function sets the internal id field which will be used instead
' of the LogLevel value for the Identifier printed out
' The value will be resetted after each Write function call.
' We return a reference of us back to the caller to mkae suche constructs possible :
' myInstance.SetID(&h1000).Write(MyClass.LogError, "MySource", "Test")
'_____________________________________________________________________
Function setID(ByVal id As Integer) As MyLog
mID = id
Return (Me)
End Function

'_____________________________________________________________________
' Function UserVariables
' Description
' Concatenates all User variables
'_____________________________________________________________________
Function UserVariables() As String
Dim varnames As String = ""
Try
For Each var As Variable In mVars
Dim content As String = _
var.QualifiedName + " = " + var.Value.ToString() + " [ " + _
"DataType: " + var.DataType.ToString() + "; " + _
"EvaluateAsExpression: " + var.EvaluateAsExpression.ToString() + "]"

If Not var Is Nothing Then
If varnames.Length = 0 Then varnames = content Else varnames = varnames + "," + content
End If
Next
Catch ex As Exception
mEvents.FireInformation(&H5000, Me.ToString(), "Error : " + ex.Message, "", 0, True)
End Try
UserVariables = varnames
End Function

'_____________________________________________________________________
' Function log
' Description
' This function logs the given Message, which could be build up like the
' WriteFunction in the System.Console with optional paramters {0} .. {n}
' LogLevel : Print out only if this level is included in the binary SysLogLevel
' Message : Message string
' Source : Which Task should be print out in the message
' parameter : Optional Values to build into the message
'_____________________________________________________________________
Sub log(ByVal logLevel As Integer, ByVal source As String, ByVal message As String, ByVal ParamArray parameter() As Object)
Dim Id As Integer

If (logLevel And mSysLogLevel) > 0 Then
If parameter.Length > 0 Then message = New StringBuilder("").AppendFormat(message, parameter).ToString
If mID = 0 Then Id = logLevel Else Id = mID ' if we had no internal id, use the external
If source.Length = 0 Then source = Me.Taskname ' If we had no source, get it form system

If logLevel = Me.LOG_Info Or logLevel = Me.LOG_Verbose Then
mEvents.FireInformation(Id, source, message, "", 0, True)
ElseIf logLevel = Me.LOG_Warning Then
mEvents.FireWarning(Id, source, message, "", 0)
ElseIf logLevel = Me.LOG_Error Then
mEvents.FireError(Id, source, message, "", 0)
ElseIf logLevel = Me.LOG_MsgBox Then
MsgBox(message.Replace(",", vbCr), , CStr(Id))
ElseIf logLevel = Me.LOG_UserVars Then
mEvents.FireInformation(Id, source, Me.UserVariables(), "", 0, True)
End If
mID = 0 ' Reset the internal ID
End If
End Sub
End Class

'___________________________________________________________
' Class for handling variables in ControlFlow
' This should be instanciated the very fist step
'___________________________________________________________
Class MyVariables
'================================================
' DataFlow
'Private mEvents As IDTSComponentMetaData90
'Private mMain As ScriptMain
'Private mVars As IDTSVariables90

'================================================
' ControlFlow
Protected mEvents As IDTSComponentEvents
Protected mMain As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel
Protected mVars As Variables

Private mVarNamesCount As Integer
Private mVarNames As String

'___________________________________________________________
' Constructor
'___________________________________________________________
Sub New(ByRef main As Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptObjectModel)

mMain = main ' '====== ControlFlow
mEvents = mMain.Events

'mMain = main ' '====== DataFlow
'mEvents = mMain.ComponentMetaData

Call Me.getVariableNames()
Call Me.getVars()
End Sub

'___________________________________________________________
' Returns back the initial variable names
' or if compareName is given, the we return back each name
' which contains CompareName
'___________________________________________________________
Property VariableNames(ByVal CompareName As String) As String
Get
If CompareName.Length = 0 Then
VariableNames = mVarNames
Else
Dim result As String = ""
For Each varName As String In Split(mVarNames, ",")
If varName.Contains(CompareName) Then
If result.Length = 0 Then result = varName Else result = result & "," & varName
End If
Next
VariableNames = result
End If
End Get
Set(ByVal value As String)
Dim v() As String = value.Split(","c)
Me.mVarNamesCount = v.Length
mVarNames = value
Me.getVars()
End Set
End Property

'___________________________________________________________
' Returns back the initial variable collection
'___________________________________________________________
ReadOnly Property Variables() As Variables
Get
Variables = Me.getVars()
End Get
End Property

'___________________________________________________________
' Get the initial Variable names
'___________________________________________________________
Private Sub getVariableNames()
mVarNames = ""
mVarNamesCount = 0

For Each var As Variable In mMain.Variables
If mVarNames.Length = 0 Then mVarNames = var.Name Else mVarNames = mVarNames + "," + var.Name
mVarNamesCount += 1
Next
End Sub

'___________________________________________________________
' Recreates the initial variable collection
'___________________________________________________________
Private Function getVars() As Variables
Try
If Not mVars Is Nothing Then
If mVars.Locked Then mVars.Unlock()
Else
If mMain.Variables.Locked Then mMain.Variables.Unlock()
Call mMain.VariableDispenser.Reset()
End If
For Each name As String In Split(mVarNames, ",")
If name.Length > 0 Then Call mMain.VariableDispenser.LockForRead(name)
Next
Call mMain.VariableDispenser.GetVariables(mVars)
getVars = mVars
Catch ex As Exception
mEvents.FireError(1, Me.ToString, ex.ToString, "", 0)
End Try
End Function

'_________________________________________________________________________________
' Read variables
' Parameter
' varname : Variablename, must be in the read or write section of the task
' If you want to query system vars, you must use the syntax 'System::<defined varname>'
' checkForExistance : Bool var to check or not before using the vars
'_________________________________________________________________________________
Function Read(ByVal varName As String, Optional ByVal DefaultValue As Object = Nothing, Optional ByVal NoExisting As String = "", Optional ByVal checkForExistance As Boolean = True) As Object
Dim result As Object
Dim var As Variable

Try
If Not checkForExistance Or mMain.VariableDispenser.Contains(varName) Then
mMain.VariableDispenser.Reset()
Call mMain.VariableDispenser.LockOneForRead(varName, mVars)
result = mVars(varName).Value ' Read variable
Call mVars.Unlock()
Else
mEvents.FireWarning(&H1001, "ReadVariable", "Warning variable '" + varName + "' doen't exist in the collection", "", 0)
If Not DefaultValue Is Nothing Then result = DefaultValue Else result = NoExisting
End If
Catch ex As Exception
mEvents.FireInformation(&H1002, "ReadVariable", "Error reading var " + varName + " : " + ex.Message, "", 0, True)
Throw ex
End Try

If Not DefaultValue Is Nothing Then
If TypeOf (result) Is String Then
If Len(Trim(result.ToString)) = 0 Then
result = DefaultValue
End If
End If
End If

Return result
End Function ' ReadVariable

'_________________________________________________________________________________
' Write variables
' Parameter
' varname : Variablename, must be in the read or write section of the task
' If you want to query system vars, you must use the syntax 'System::<defined varname>'
' value: Varibale value as Object to write to
' checkForExistance : Bool var to check or not before using the vars
'_________________________________________________________________________________
Function Write(ByVal varName As String, ByVal value As Object, Optional ByVal checkForExistance As Boolean = True) As Object
Dim result As Object
Dim var As Variable

Try
If Not checkForExistance Or mMain.VariableDispenser.Contains(varName) Then
mMain.VariableDispenser.Reset()
Call mMain.VariableDispenser.LockOneForWrite(varName, mVars)
mVars(varName).Value = value ' Write variable
Call mVars.Unlock()
Else
mEvents.FireWarning(&H1001, Me.ToString, "Warning variable '" + varName + "' doen't exist in the collection", "", 0)
End If
Catch ex As Exception
mEvents.FireInformation(&H1002, Me.ToString, "Error write var " + varName + " : " + ex.Message, "", 0, True)
Throw ex
End Try
Return result
End Function ' ReadVariable
End Class

End Class


Submitted by	Category	Review Cycle	.	Public
Joachim Mutter/sysarc on 06/11/2008 at 06:13 PM	SSiS\Code