SSIS - when a connection is down

Question

Hi.

I've got a simple SSIS package that I edit in VS2008. What is basically does is run the same SQL StoredProc, residing in different SQL servers, and aggregating their output into a flat file. Simply put, four OLE DB Data Source going to a 'Union All' to output.

If one of the servers is down, how can I make the package skip the validation for that source and therefore the processing, while still aggregating what's possible?

I've had a quick read at ConnectionStrings being in a file, using scripts, etc. hopefully I can avoid this.

I thank you in advance for any help.

Answer 1

Here are a few possible solutions:

1. If the database itself is unavailable but the server is available, then you could change the default connection to something that is always available like a system database (master, msdb, tempdb, model). I use this technique when querying log shipped databases since they are periodically in a "Restore" state each hour of the day.

2. If there is another server that is always available, then you could create a linked server between the two and resolve the error handling with the always available database server.

3. You could have separate data flows and each of them could append data to the file instead of overwriting the contents. You could then set DelayValidation = True and MaximumErrorCount <> 0. This would allow each data flow to succeed while avoiding causing a package failure error.

4. Use a script task to execute the queries. This would give you the most control over how to handle exceptions with the data source. Sorry for the weirdly formed sample code below, but the code block isn't formatting this nicely like it normally does for me, so my attempts to make it work added lots of dead space. The point of the sample code is to show you how to query the database and catch errors. You could always make it ignore the errors and complete successfully.

      // Try-Catch block
       try
   
   
   {
   
   
       bool fireAgain = true;
   
   
       string SQLCommandText = "EXEC dbo.usp_some_stored_procedure_or_select_statement;";
   
   
   SqlConnection SQLConnection = new SqlConnection("Data Source=SomeServerName;Initial Catalog=master;Integrated Security=SSPI;Application Name=SSIS-My Package Name;Connect Timeout=600");
   
   
   SqlCommand SQLCommand = new SqlCommand(SQLCommandText, SQLConnection);
   
   
   SQLCommand.CommandTimeout = 60 * 60;
   
   
   SqlDataAdapter SQLDataAdapter = new SqlDataAdapter(SQLCommand);
   
   
   DataTable dt = new DataTable();
   
   
   SQLDataAdapter.Fill(dt);
   
   
   SQLConnection.Close();
   
   
   RowsRemaining = dt.Rows.Count;
   
   
   Dts.Events.FireInformation(0, "DataTable Rows", RowsRemaining.ToString(), "", 0, ref fireAgain);
   
   
   }
   
   
   catch (SqlException e)
   
   
   {
   
   
   Dts.Events.FireError(0, "SqlException", e.Message, "", 0);
   
   
   Error = 1;
   
   
   }
   
   
   // Return results.
   
   
   if (Error == 0)
   
   
   {
   
   
   Dts.TaskResult = (int)ScriptResults.Success;
   
   
   }
   
   
   else
   
   
   {
   
   
   Dts.TaskResult = (int)ScriptResults.Failure;
   
   
   }