SSIS: Use Sql connection inside script component

If the requirement is to run dynamic query on each row, you might get inside Script comoponent. Here is how you can grab SQL connection and run your queries;

Your connection is defined here on script component;

public class ScriptMain : UserComponent
{
    IDTSConnectionManager100 connMgr;
    SqlConnection sqlConn;
    SqlDataReader sqlReader;

    public override void AcquireConnections(object Transaction)
    {
        connMgr = this.Connections.MyConn;
        sqlConn = (SqlConnection)connMgr.AcquireConnection(null);
    }

    public override void ReleaseConnections()
    {
        //base.ReleaseConnections();
        connMgr.ReleaseConnection(sqlConn);
    }

    public override void PreExecute()
    {
        base.PreExecute();
        string KeyValue = Variables.vFileOutputString;

        String vSqlStatement = "SELECT SqlStatement FROM [DataHub].[vw_LastStepForRglData] WHERE ADNumber = '" + KeyValue + "' "; 
        SqlCommand cmd = new SqlCommand(vSqlStatement, sqlConn);
        sqlReader = cmd.ExecuteReader();
    }

    public override void PostExecute()
    {
        base.PostExecute();
        sqlReader.Close();
    }

    public override void CreateNewOutputRows()
    {
        while (sqlReader.Read())
        {
            {
                DynamicSqlBuffer.AddRow();
                DynamicSqlBuffer.SqlStatement = sqlReader.GetString(0);
            }
        }
    }
}

Hope this will help.

Add to favorites