CODEWITHSUNDEEP
ssis
Yonas
Yonas

Reputation: 50

Using Multiple Connection strings in SSIS

This is my first question on stackoverflow I hope to see an answer quickly. I have a requirement to create a package(SSIS) that pulls data from one SOURCE then filter the data and push it to multiple servers. So I tried it using Execute SQL Task to query the connectionStrings then using Foreach Loop Container loop over each connections. I have done this from the the suggestion [shree-pat18] did in this Question (This image explains it all).

But one of the major problem is connection for sites are bad most of the time. So I want to make sure the connection works before i proceed to the next step if the connection is slow or offline i want to skip that site and go to the next iteration skipping all other tasks. If anyone has a better solution to do this i'm open to suggestion thanks in advance.

Upvotes: 0

Views: 1231

Answers (2)

cdonner
cdonner

Reputation: 37708

I use a script task called VerifyConnections in pretty much every package that I write. I found this code a long time ago and don't remember its source, so I can't give credit to the author. It iterates through all connections in the in the package, creates a connection, and writes the results to the log. It find it very useful during development and testing. You can easily change the code or combine it with Sam's suggestion so that it does what you need.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace VerifyConnectionsScript
{
  [System.AddIn.AddIn("ScriptMain", Version="1.0", Publisher="", Description="")]
  public partial class ScriptMain :
          Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
  {
    enum ScriptResults
    {
      Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
      Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    public void Main()
    {
      bool failure = false;
      bool fireAgain = true;
      foreach (var ConnMgr in Dts.Connections) {
        Dts.Events.FireInformation(1, "", String.Format("ConnectionManager='{0}', 
            ConnectionString='{1}'", ConnMgr.Name, ConnMgr.ConnectionString), "", 0, 
            ref fireAgain);
        try {
          ConnMgr.AcquireConnection(null);
          Dts.Events.FireInformation(1, "", 
               String.Format("Connection acquired successfully on '{0}'", 
                 ConnMgr.Name), "", 0, ref fireAgain);
          }
          catch (Exception ex) {
            Dts.Events.FireError(-1, "", 
            String.Format("Failed to acquire connection to '{0}'. Error ='{1}'", 
                     ConnMgr.Name, ex.Message), "", 0);
            Exception iex = ex.InnerException;
            while (iex != null) {
              Dts.Events.FireError(-1, "", 
              String.Format("Inner exception message='{0}'", iex.Message), "", 0);
              iex = iex.InnerException;
            }
            failure = true;
          }
        }
        if (failure)
          Dts.TaskResult = (int)ScriptResults.Failure;
        else
          Dts.TaskResult = (int)ScriptResults.Success;
      }
   }
}

Upvotes: 0

Sam
Sam

Reputation: 150

Welcome to stackoverflow!

I see that you use a Script Task as the first step of your For Each Loop container.

The Idea

I don't know exactly what you are doing in this script task but I suggest that you update it in order to check if the connection to the site is working and is fast enough according to your requirements.

The Proposed Solution

I assume you are using C# as the script language.

  1. Create a isSiteLive variable as a Boolean with the value False at your package level
  2. Set it as a ReadWriteVariables in your Script Task using the Script Task Editor:

screenshot

  1. Then, update your script to ping the remote site to check if it is live. You can also set an acceptable timeout according to your requirements

You can use the following code:

public static bool PingHost(string nameOrAddress, int timeout)
 {
    bool pingable = false;
    Ping pinger = new Ping();

    try
    {
        PingReply reply = pinger.Send(nameOrAddress, timeout);

        pingable = reply.Status == IPStatus.Success;
    }
    catch (PingException)
    {
        // Discard PingExceptions and return false;
    }

    return pingable;
 }
  1. If the site is pingable then set the isSiteLive variable to True, if not, set it to False
  2. Back in the SSIS Control Flow, double-click the link after your Script Task to add a constraint, choose Expression as an Evaluation operation and put @[User::isSiteLive] as an Expression:

screenshot

  1. As a consequence, the loop will ignore the next tasks if the site is down or takes too long to respond.

Hope this helps.

Upvotes: 1

Related Questions