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

SSIS - dynamic connection - verify data source using script task and email if connection Expand / Collapse
Author
Message
Posted Friday, June 11, 2010 12:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Monday, October 13, 2014 6:49 PM
Points: 614, Visits: 336
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.
Post #935851
Posted Tuesday, August 28, 2012 9:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, August 28, 2012 6:50 PM
Points: 4, Visits: 3
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.
Post #1351034
Posted Friday, July 18, 2014 9:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:17 PM
Points: 160, Visits: 294
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
Post #1594124
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse