Loop through connection manager

• s.hariharan16

  SSC Enthusiast

  Points: 188

  More actions

  March 18, 2009 at 7:35 am

  #129897

  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