What is the preferred way to execute SQL in a custom SSIS task?

Question

I am writing a custom SSIS task, which, as one of its functions, must execute a stored procedure in connection with a database. I can not find any information on how this is done.

I am using ADO.NET Connection Manager to connect to a database and I want to write my task in C #.

What is the preferred way to execute SQL in a custom SSIS task?

Answer 1

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

• , Connections Package.
• AcquireConnection , .
• SQL-, .

, SSIS.

ADO.NET :

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;
    }
}

, , , .

!