June 11, 2010 at 12:35 am
Hi,
I would like to check for the dynamic connection / data sources in the script task and send email if a server failed upon connection. My output does not show for SQL2. What am I missing in the code below as it doesn’t seem to loop through all server list (i am not getting the output for sql2) and how to implement email into this?
Below are my screens and code:
Variables: User::Server – object data type (set for execute sql task)
User::DataSource – String data type (variable mapping in foreach loop container)
ServerList datasource – contains a table with servernames: SQL1, SQL2 (both these servers exists)
DelayValidation is set to True on all tasks.
Script code:
Public Sub Main()
'
' Add your code here
'
Dim ConnMgr As ConnectionManager
'= Dts.Connections("Source")
Dim ConnStr As String
Dim bFlag As Boolean = False
'MsgBox(ConnStr)
'ConnMgr = Dts.Connections("Source")
‘For Each ConnMgr In Dts.Connections("Source")
For Each ConnMgr In Dts.Connections
ConnStr = ConnMgr.ConnectionString
Try
ConnMgr.AcquireConnection(Nothing)
Dts.Events.FireInformation(1, "", "Connection acquired successfully on " + ConnMgr.Name, "", 0, False)
Dts.Events.FireInformation(2, "", "Connection string = " + ConnStr, "", 0, False) Catch ex As Exception
Dts.Events.FireError(1, "", "Connection failed on " + ConnMgr.Name, "", 0)
Dts.Events.FireError(2, "", "Connection String = " + ConnStr, "", 0)
bFlag = True
End Try
ConnMgr.ReleaseConnection(Nothing)
Next
Dts.TaskResult = Dts.Results.Success
End Sub
Information: 0x1 at Script Task: Connection acquired successfully on ServerList
Information: 0x2 at Script Task: Connection string = Data Source=Server1;Initial Catalog=Sname;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Information: 0x1 at Script Task: Connection acquired successfully on Source
Information: 0x2 at Script Task: Connection string = Data Source=SQL1;Initial Catalog=Test;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Information: 0x1 at Script Task: Connection acquired successfully on ServerList
Information: 0x2 at Script Task: Connection string = Data Source=Server1;Initial Catalog= Sname;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Information: 0x1 at Script Task: Connection acquired successfully on Source
Information: 0x2 at Script Task: Connection string = Data Source=SQL1;Initial Catalog=Test;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;
Source connection managers – this is dynamically set:
Expression: "Data Source=" + @[User::DataSource] + ";Initial Catalog=Test;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Thanks for the help in advance.
- Nsh.
August 28, 2012 at 9:21 am
Not sure why you have the variable 'Server' set up with a Datatype of Object.
Second
Not sure if just saying 'aquireconn' in the foreach will mean you arfe testign connectivity.
Email sending : use the sendmail task.
July 18, 2014 at 9:29 am
Hi SSChasing.
I've been trying to accomplish the same thing using a variety of methods, but haven't been successful yet. Were you ever able to find a way to record the failed connections while continuing to process the successful ones?
Thanks.
Jamie
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply