What is the preferred way of executing SQL in a Custom SSIS task?

Question

I'm writing a Custom SSIS task that, as one of it's functions, should execute a stored procedure on a database connection. I can't seem to find any information about how this is done.

I'm using an ADO.NET Connection Manager to connect to the database and I wish to write my Task in C#.

What is the preferred way of executing SQL in a Custom SSIS task?

Answer 1

The answer to this depends somewhat on what connection manager you are using to connect to the database, however the general approach is the same:

1. Get the relevant connection manager in your custom task using the Connections property of the Package object.
2. Call the AcquireConnection method on the connection manager to obtain a connection to your database.
3. Execute your SQL statements using the supplied connection.

This approach allows you to take advantage of the configuration and management of connections provided by SSIS.

For an ADO.NET connection manager, the following code can be used:

public override DTSExecResult Validate(
  Connections connections, VariableDispenser variableDispenser,
  IDTSComponentEvents componentEvents, IDTSLogging log)
{
    // Validate connection exists.
    if(!connections.Contains("YourConnection"))
    {
        componentEvents.FireError(0, "CustomTask", 
            "Invalid connection manager.", "", 0);
        return DTSExecResult.Failure;
    }

    return DTSExecResult.Success;
}

public override DTSExecResult Execute(Connections connections, 
  VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, 
  IDTSLogging log, object transaction)
{
    ConnectionManager cm = connections["YourConnection"];

    try
    {
        SqlConnection connection 
            = cm.AcqureConnection(transaction) as SqlConnection;

        if(connection == null)
        {
            componentEvents.FireError(0, "CustomTask", 
                "Failed to acquire ADO.NET connection.", "", 0);
            Return DTSExecResult.Failure;
        }

        // TODO: Use connection to execute SQL.
    }
    catch(Exception ex)
    {
        componentEvents.FireError(0, "CustomTask", 
            ex.Message, "", 0);

        Return DTSExecResult.Failure;
    }
}

You will want some better error handling and I'm not sure about how to handle the lifespan of the connection, whether you should open it manually or dispose after use.

Good luck!