SSIS

Question

SSIS

mtz676

SSCrazy Eights

Points: 9214
More actions
April 23, 2015 at 11:45 pm

#148889

I am looping through a list of SQL Server connection in SSIS which are dynamically changed in the connection managers in for each loop.How do I error handle a situation where the SSIS is unable to connect to the server.If it unable to connect it should ignore it send an email/save the datasource conn in a SQL table and still go through the remaining list of data source connection until the for loop ends.I have listed all the sql servers in a table which is the source for the for loop.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Emil B SSCertifiable Points: 5568 More actions · Answer 1

You need a script task that will try to open that connection, if it succeeds it sends a success and continues as normal, if it fails than you do the rest either in the script task itself or add few more blocks to handle sending email and writing to sql table.You can change the maximum error count to continue on connection failure.

something like that:

mtz676 SSCrazy Eights Points: 9214 More actions · Answer 2

Can we do this without using script task ? Basically without coding ! Not good at coding.

Emil B SSCertifiable Points: 5568 More actions · Answer 3

I had the same problem as you building the attached solution and that was the only decend way i found. Script itself is not complicated, please find mine below:

public void Main()

{

bool IsValid = true;

string Provider = "SQLNCLI10.1";

string ServerName = Dts.Variables["User::ServerName"].Value.ToString();

string Catalog = Dts.Variables["User::Catalog"].Value.ToString();

string UserId = Dts.Variables["User::UserId"].Value.ToString();

string Password = Dts.Variables["User::Password"].Value.ToString();

OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder();

builder["Provider"] = Provider;

builder["Data Source"] = ServerName;

builder["Initial Catalog"] = Catalog;

builder["User ID"] = UserId;

builder["Password"] = Password;

OleDbConnection connection = new OleDbConnection(builder.ConnectionString);

try

{

connection.Open();

}

catch

{

IsValid = false;

}

if (IsValid == true)

{

Dts.Variables["User::ConnString"].Value = builder.ConnectionString;

Dts.TaskResult = (int)ScriptResults.Success;

}

if (IsValid == false)

{

Dts.Events.FireError(0, "Connection Error", "Error initialising connection to server: " + ServerName, String.Empty, 0);

Dts.TaskResult = (int)ScriptResults.Failure;

}