none
Exception handling in SSIS Package Dynamic Connection Not Found

    Question

  • SSIS Scenario

    I have a SQL variable of type object. It contains all the connection to different servers/databases. I want to connect to those databases one by one and run a query.

    Exception Handling

    If the SSIS Connection manager(Dynamic connection manger) is unable to find the connection to the server (probably the server is down) I want to Skip that connection (Database/server) and log that into the table and move onto the next Connection (Database/Server). The SSIS package should not crash.

    My Implementation

    I have Successfully Configured the SSIS package to Use Connection Manager (Dynamic Connection Manager) and Foreach loop to loop through the SSIS variable of type object (not for loop as shown below). but I am not able to skip the Connection if the server/database is not found. it generates error that the server/databsse not found / problem with the connection and the SSIS package fails.

    my experience in SSIS is one week old

    Any help will be appreciated.


    Saturday, August 03, 2013 9:36 PM

Answers

  • You have test the connection string before using the same...

    Use a script task to do so in 

    try 

    {

           

    using(SqlConnection conn = new SqlConnection(cs)) {
    conn.Open(); // throws if invalid

    If open then

    conn.Close(); // throws if invalid    }

    catch(Exception ex)

    {

    //catch not valid connection;

    }

    }


    Thanks and Regards, Prajesh Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Sunday, August 04, 2013 11:06 AM