March 4, 2015 at 8:36 am
I am writing out to a file from multiple databases. If One database is not available I don't want the package to fail but continue to the next database. Can anyone show me how to achieve this?
1. I have Execute SQL Task - Gets DB connection string stored in a table
2. I have a Data Flow Task inside a Foreach Loop Container
If the connection fails I want to skip the task and go to the next database connection.
March 4, 2015 at 8:44 am
You have FailPackageOnFailure option in the properties of your containers
March 4, 2015 at 8:55 am
I was actually doing same thing last week, but instead of passing whole connection string i passed all the details in separate variables. Anyway i have a script task in my For Each Loop to build connection string and than validate it by trying to open connection, if it fails it reports an error and failure and goes to next connection
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;
}
}
March 4, 2015 at 9:38 am
I tried this but the connection does not move to the next
public void Main()
{
// TODO: Add your code here
bool IsValid = true;
OleDbConnection conn = new OleDbConnection(Dts.Variables["User::dynamicConnection"].Value.ToString());
try
{
conn.Open();
}
catch
{
IsValid = false;
}
if (IsValid == true)
{
Dts.Variables["User::dynamicConnection"].Value = conn.ConnectionString.ToString();
Dts.TaskResult = (int)ScriptResults.Success;
}
if (IsValid == false)
{
Dts.Events.FireError(0, "Connection Error", "Error initialising connection to server: " + conn.ConnectionString.ToString(), String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}
}
March 4, 2015 at 12:03 pm
- are you getting an error message?
- did you remember to add
using System.Data.OleDb;
at the beginning?
- did you try to debug and find out at which line it fails?
- make sure that your For Each Loop has Max Error Count more than one so when a connection fails it won't stop the whole package and will loop to another connection.
- Also change the Max Error Count for the package as it will report failure if one of the connections will fail
March 5, 2015 at 3:47 am
Kutang Pan (3/4/2015)
- are you getting an error message?- did you remember to add
using System.Data.OleDb;at the beginning?
- did you try to debug and find out at which line it fails?
- make sure that your For Each Loop has Max Error Count more than one so when a connection fails it won't stop the whole package and will loop to another connection.
- Also change the Max Error Count for the package as it will report failure if one of the connections will fail
just to interject a question,does the package have to keep running after any error occurs or just if the connection fails go to the next one & if any other failure fail the package
March 5, 2015 at 3:57 am
I'm just pointing out the things to look into, not saying thats the only way to do it, even if it might have sound like that:hehe:
March 5, 2015 at 4:06 am
Kutang Pan (3/5/2015)
I'm just pointing out the things to look into, not saying thats the only way to do it, even if it might have sound like that:hehe:
Yeah,I've done something like this in the past but can't recall where that project is atm,it was also for a different employer.
Just increasing the max errors allowed will do what looks to be required but if there are multiple errors within the for each loop, what to do then ?
I believe FailPackageOnFailure option set to false on the for each loop could do the trick,then again I'm not sure if this feature works as intended.
Actually I just found the solution I was thinking off,I'll see if I can get a version without any 'sensitive' information
March 5, 2015 at 10:14 am
I came across this example. This is exactly what I wanted to do. It works great! I could even leave the MaximumErrorCount property of the Foreach Loop to 0.
Now working on how to write each step into a log file.
http://dba.stackexchange.com/questions/78587/how-do-i-continue-after-a-failure-in-ssis/94512#94512
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply