Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Loop through connection manager Expand / Collapse
Author
Message
Posted Wednesday, March 18, 2009 7:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 28, 2012 11:53 PM
Points: 2, Visits: 110
I have a problem.

1. a table has conection string of various servers e.g

Record 1:

Server1

REcord 2:

Server 2

Record 3:

Server 3



2. In the SSIS pacakge i am trying to implement following:
Step1: Loop through the connection string of all the servers using for each loop and assign it to connection manager.
Step2: If any of the connection string is invalid or server not available the the package must evaluate that and execute the package for the next server.

In my case, server 2 connection string is invalid .
My requirement is the package shlud process for server 1 and server 3 and report error for server 2.


Following is the below code i have written in script task.

This code is "ConnMgr.AcquireConnection(Nothing)" is returning me true for server 2 connection which is invalid.

After debugging I found that its taking the connection string of server 1 which vaild.
Please suggest a solution for the same.

Public Class ScriptMain

' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.

Public Sub Main()
'
' Add your code here
'
Dim ConnMgr As ConnectionManager = Dts.Connections("hydhtc99600.acQuire_Chile")
Dim ConnStr As String = ConnMgr.ConnectionString
'Dts.Transaction()
'MsgBox(ConnStr)

Try
ConnMgr.AcquireConnection(Nothing)

Dts.Events.FireInformation(1, "", "Connection Successful", "", 0, False)
Dts.Events.FireInformation(2, "", "Connection String = " + ConnStr, "", 0, False)
ConnMgr.ReleaseConnection(Nothing)
Dts.TaskResult = Dts.Results.Success
Catch ex As Exception
Dts.Events.FireError(-1, "", "Connection Failed", "", 0)
Dts.Events.FireError(2, "", "Connection String = " + ConnStr, "", 0)
ConnMgr.ReleaseConnection(Nothing)
Dts.TaskResult = Dts.Results.Failure
End Try

End Sub

End Class

Post #678438
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse