centralizing logic and connection strings in package

Question

Post reply

centralizing logic and connection strings in package

cafescott

SSCarpal Tunnel

Points: 4724
More actions
June 5, 2012 at 1:15 pm

#275197

Hello,
I have a pair of SSIS newbie questions. After having spent some time trying to search for answers here I am.
I have a package with multiple tasks. For each task so far I have included a subroutine that enables me to send debugging info into a table. I'd like to just have this subroutine defined in one place for the entire package. Unfortunately, the subroutine is only being defined for the given task. In other words, at the top of the first task I have a sub defined:
Public sub Write_Debug_Info (ByVal strToWrite as string)
This is visible to the task it is defined in, but I would like it to be visible to the entire package. Can anyone explain how this is accomplished?
Second, I have been unable to figure out to use the OLE DB\SQL Server connection manager in order to write to connection string within the debug subroutine. Here's the complete subroutine in question:
Public Sub Write_Debug_Stmt(ByVal bDebugTable As Boolean, ByVal strToWrite As String)
If Not bDebugTable Then Exit Sub
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
myConnection = New SqlConnection("server=myServer;Integrated Security=SSPI;database=AdventureWorks2008R2")
myConnection.Open()
myCommand = New SqlCommand("Insert into tblDebug select '" & strToWrite & "'", myConnection)
ra = myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
I don't want to hard-code the connection string here. I'd really like to use the Connection manager's string. Unfortunately, I have tried this (and it doesn't work):
myConnection = New SqlConnection(DTS.Connections.Item("myServer.AdventureWorks2008R2").ConnectionString).
Is it possible to use the Connection Manager's connection string with a sqlcommand object? If so, how is it done?
Thanks,

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

robert.gerald.taylor SSChampion Points: 13133 More actions · Answer 1

1) Use configurations to store your connection string.

2) Map connection string value to a variable.

3) Use the Expressions property in the connection manager to use that variable for the ConnectionString.

4) Then your SQL task's connection property is your connection manager.

You can also write out the value of the variable in your .Net debug code in your script task:

Dts.Variables["YourConnStrVariable"].Value.ToString()

Make sure you put the User::YourConnStrVariable in the ReadOnlyVariables property of the script task

HTH,

Rob

Rock from VbCity Hall of Fame Points: 3991 More actions · Answer 2

cafescott (6/5/2012)
Hello,
I have a pair of SSIS newbie questions. After having spent some time trying to search for answers here I am.
I have a package with multiple tasks. For each task so far I have included a subroutine that enables me to send debugging info into a table. I'd like to just have this subroutine defined in one place for the entire package. Unfortunately, the subroutine is only being defined for the given task. In other words, at the top of the first task I have a sub defined:
Public sub Write_Debug_Info (ByVal strToWrite as string)
This is visible to the task it is defined in, but I would like it to be visible to the entire package. Can anyone explain how this is accomplished?
Second, I have been unable to figure out to use the OLE DB\SQL Server connection manager in order to write to connection string within the debug subroutine. Here's the complete subroutine in question:
Public Sub Write_Debug_Stmt(ByVal bDebugTable As Boolean, ByVal strToWrite As String)
If Not bDebugTable Then Exit Sub
Dim myConnection As SqlConnection
Dim myCommand As SqlCommand
myConnection = New SqlConnection("server=myServer;Integrated Security=SSPI;database=AdventureWorks2008R2")
myConnection.Open()
myCommand = New SqlCommand("Insert into tblDebug select '" & strToWrite & "'", myConnection)
ra = myCommand.ExecuteNonQuery()
myConnection.Close()
End Sub
I don't want to hard-code the connection string here. I'd really like to use the Connection manager's string. Unfortunately, I have tried this (and it doesn't work):
myConnection = New SqlConnection(DTS.Connections.Item("myServer.AdventureWorks2008R2").ConnectionString).
Is it possible to use the Connection Manager's connection string with a sqlcommand object? If so, how is it done?
Thanks,

Hi, look at the package events, you will find a couple executing before and after each component, a good location for your logger.

Hope this helps,
Rock from VbCity

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 3

Try this:

1. Create a variable which will store the connection string (in below code: strSourceConnection).

2. In script-task:

ConnectionManager SourceoledbConnectionManager = Dts.Connections["SourceConnection"];

string strSourceConnString = Dts.Variables["strSourceConnection"].Value.ToString();

Dts.Connections["SourceConnection"].Properties["ConnectionString"].SetValue(SourceoledbConnectionManager, strSourceConnString );

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 4

Thanks for the reply. I actually now have the XML file working:

http://www.sqlservercentral.com/Forums/Topic1312110-364-1.aspx?Update=1

There were two questions I asked at the top of this thread. One has been answered. The other was: How can I create some VB.NET procedures that the entire package can see? (When I create a routine in a task, it is visible only to the current task. However, when I am defining some routines to facilitate logging, I'd rather put single copies of them in one place that the other tasks can see, rather than copying them to every task.)

thanks for the help.