SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS - dynamic connection - verify data source using script task and email if connection


SSIS - dynamic connection - verify data source using script task and email if connection

Author
Message
Nsh
Nsh
SSC Eights!
SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)SSC Eights! (826 reputation)

Group: General Forum Members
Points: 826 Visits: 413
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:BigGrinataSource – 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:BigGrinataSource] + ";Initial Catalog=Test;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"


Thanks for the help in advance.
- Nsh.
mp83399
mp83399
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 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.
jamie.chicago
jamie.chicago
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 380
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search