c# - Missing library to reference OLEDB connection types

Question

I got the following piece of code from SSIS Team Blog to cast the OLEDB connection type so that it can be used by the AcquireConnection () method. Now I am not sure why the Dts.Connections part is not working. I dont know the library that I would have to add to make it work. I pretty much added the most important ones including the Dts.RuntimeWrap. Please let me know if you need more information on the question.

ConnectionManager cm = Dts.Connections["oledb"];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

EDIT Below is the entire code for this component.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.OleDb;
using System.Data.Common;
using System.Linq;
using System.Configuration;
using System.Collections;

//using System.Data.OleDb;

namespace AOC.SqlServer.Dts.Tasks
{

    [DtsTask(
        DisplayName = "Custom Logging Task",
        Description = "Writes logging info into a table")]
    public class CustomLoggingTask : Task
    {

        private string _packageName;
        private string _taskName;
        private string _errorCode;
        private string _errorDescription;
        private string _machineName;
        private double _packageDuration;

        private string _connectionName;
        private string _eventType;
        private string _executionid;
        private DateTime _handlerdatetime;
        private string _uid;
        public string ConnectionName
        {
            set
            {
                _connectionName = value;
            }
            get
            {
                return _connectionName;
            }
        }


        public string Event
        {
            set
            {
                _eventType = value;
            }
            get
            {
                return _eventType;
            }
        }

public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
            const string METHOD_NAME = "CustomLoggingTask-Validate";

            try
            {

                if (string.IsNullOrEmpty(_eventType))
                {
                    componentEvents.FireError(0, METHOD_NAME, "The event property must be specified", "", -1);
                    return DTSExecResult.Failure;
                }


                if (string.IsNullOrEmpty(_connectionName))
                {
                    componentEvents.FireError(0, METHOD_NAME, "No connection has been specified", "", -1);
                    return DTSExecResult.Failure;
                }


                //SqlConnection connection = connections[_connectionName].AcquireConnection(null) as SqlConnection;
                DbConnection connection = connections[_connectionName].AcquireConnection(null) as DbConnection;

                ConnectionManager cm = Dts.Connections["oledb"];
                Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
                OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;



                if (connection == null)
                {
                    componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET connection", "", -1);
                    return DTSExecResult.Failure;
                }

               if (!variableDispenser.Contains("System::SourceID"))
                {
                    componentEvents.FireError(0, METHOD_NAME, "No System::SourceID variable available. This task can only be used in an Event Handler", "", -1);
                    return DTSExecResult.Failure;
                }

                return DTSExecResult.Success;
            }
            catch (Exception exc)
            {
                componentEvents.FireError(0, METHOD_NAME, "Validation Failed: " + exc.ToString(), "", -1);
                return DTSExecResult.Failure;
            }
        }


public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
       {
          try
            {
                string commandText =
@"INSERT INTO SSISLog (EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, Host, ExecutionID, EventHandlerDateTime,UID)
VALUES (@EventType, @PackageName, @TaskName, @EventCode, @EventDescription, @PackageDuration, @Host, @Executionid, @handlerdatetime,@uid)";

                ReadVariables(variableDispenser);
                DbConnection connection = connections[_connectionName].AcquireConnection(transaction) as DbConnection;
                //SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
                DbCommand command = null;
                //using (SqlCommand command = new SqlCommand())
                if (connection is SqlConnection)
                    command = new SqlCommand();
                else if (connection is OleDbConnection)
                    command = new OleDbCommand();

                {
                    command.CommandText = commandText;
                    command.CommandType = CommandType.Text;
                    command.Connection = connection;

                    command.Parameters.Add(new SqlParameter("@EventType", _eventType));
                    command.Parameters.Add(new SqlParameter("@PackageName", _packageName));
                    command.Parameters.Add(new SqlParameter("@TaskName", _taskName));
                    command.Parameters.Add(new SqlParameter("@EventCode", _errorCode ?? string.Empty));
                    command.Parameters.Add(new SqlParameter("@EventDescription", _errorDescription ?? string.Empty));
                    command.Parameters.Add(new SqlParameter("@PackageDuration", _packageDuration));
                    command.Parameters.Add(new SqlParameter("@Host", _machineName));
                    command.Parameters.Add(new SqlParameter("@ExecutionID", _executionid));
                    command.Parameters.Add(new SqlParameter("@handlerdatetime", _handlerdatetime));
                    command.Parameters.Add(new SqlParameter("@uid", _uid));
                    command.ExecuteNonQuery();
                }
                connection.Close();
                return DTSExecResult.Success;

            }
            catch (Exception exc)
            {
                componentEvents.FireError(0, "CustomLoggingTask-Execute", "Task Errored: " + exc.ToString(), "", -1);
                return DTSExecResult.Failure;
            }

        }


        private void ReadVariables(VariableDispenser variableDispenser)
        {
            variableDispenser.LockForRead("System::StartTime");
            variableDispenser.LockForRead("System::PackageName");
            variableDispenser.LockForRead("System::SourceName");
            variableDispenser.LockForRead("System::MachineName");
            variableDispenser.LockForRead("System::ExecutionInstanceGUID");
            variableDispenser.LockForRead("System::EventHandlerStartTime");
            variableDispenser.LockForRead("User::UID");
            bool includesError = variableDispenser.Contains("System::ErrorCode");
            if (includesError)
            {
                variableDispenser.LockForRead("System::ErrorCode");
                variableDispenser.LockForRead("System::ErrorDescription");
            }

            Variables vars = null;
            variableDispenser.GetVariables(ref vars);

            DateTime startTime = (DateTime)vars["System::StartTime"].Value;
            _packageDuration = DateTime.Now.Subtract(startTime).TotalSeconds;
            _packageName = vars["System::PackageName"].Value.ToString();
            _taskName = vars["System::SourceName"].Value.ToString();
            _machineName = vars["System::MachineName"].Value.ToString();
            _executionid = vars["System::ExecutionInstanceGUID"].Value.ToString();
            _handlerdatetime = (DateTime)vars["System::EventHandlerStartTime"].Value;
            _uid = vars["User::UID"].Value.ToString();
            if (includesError)
            {
                _errorCode = vars["System::ErrorCode"].Value.ToString();
                _errorDescription = vars["System::ErrorDescription"].Value.ToString();
            }

            // release the variable locks.
            vars.Unlock();

            // reset the dispenser
            variableDispenser.Reset();
        }
    }

}

Answer 1

With the code you posted, I finally figured out what you were trying to accomplish.

These are the things I've done to get it to work:

A) The compile error you're receiving:

"Error 1 The type or namespace name 'Connections' does not exist in the namespace 'AOC.SqlServer.Dts' (are you missing an assembly reference?)"

is simply because the way you're trying to get the oledb connection manager:

ConnectionManager cm = Dts.Connections["oledb"];

The Dts object is a facility available in the script task component. You should replace that line with:

ConnectionManager cm = connections["oledb"];

I've seen in your code that you access a Connection Manager in this way, so maybe it was something you just left behind.

B) To validate if the connection manager is either an ADO.NET connection manager or an OLEDB one, changed this part of your code:

DbConnection connection = connections[_connectionName].AcquireConnection(null) as DbConnection;

ConnectionManager cm = Dts.Connections["oledb"];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
OleDbConnection conn = cmParams.GetConnectionForSchema() as OleDbConnection;

if (connection == null)
{
    componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET connection", "", -1);
    return DTSExecResult.Failure;
}

First, added a private variable at the class level to store the connection:

private DbConnection _connection;

and then, modified the validation to check if the connection is ADO.NET, and in case it's not, to check if it's OLEDB:

_connection = connections[_connectionName].AcquireConnection(null) as DbConnection;

if (_connection == null)
{
    ConnectionManager cm = connections[_connectionName];
    Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
    _connection = cmParams.GetConnectionForSchema() as OleDbConnection;

    if (_connection == null)
    {
        componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET or OLEDB connection", "", -1);
        return DTSExecResult.Failure;
    }
}

Note that I replaced the hardcoded "oledb" with the _connectionName variable, and also modified the error string when _connection is null in both cases.

C) To execute the command using an OLEDB provider were necesary the following changes:

• Use the new _connection variable, wich holds the previously retrieved connection.
• The parameters added to the command must be OleDbParameter instead of SqlParameter.
• The OLE DB .NET Provider does not support named parameters. So, the VALUES part in the INSERT statement command needs to be modified to use ?.

D) This is the complete working code:

using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;

//using System.Data.OleDb;

namespace AOC.SqlServer.Dts.Tasks
{
    [DtsTask(
        DisplayName = "Custom Logging Task",
        Description = "Writes logging info into a table")]
    public class CustomLoggingTask : Task
    {
        private string _packageName;
        private string _taskName;
        private string _errorCode;
        private string _errorDescription;
        private string _machineName;
        private double _packageDuration;

        private string _connectionName;
        private string _eventType;
        private string _executionid;
        private DateTime _handlerdatetime;
        private string _uid;

        public string ConnectionName
        {
            set { _connectionName = value; }
            get { return _connectionName; }
        }

        public string Event
        {
            set { _eventType = value; }
            get { return _eventType; }
        }

        private DbConnection _connection;

        public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
        {
            const string METHOD_NAME = "CustomLoggingTask-Validate";

            try
            {
                if (string.IsNullOrEmpty(_eventType))
                {
                    componentEvents.FireError(0, METHOD_NAME, "The event property must be specified", "", -1);
                    return DTSExecResult.Failure;
                }

                if (string.IsNullOrEmpty(_connectionName))
                {
                    componentEvents.FireError(0, METHOD_NAME, "No connection has been specified", "", -1);
                    return DTSExecResult.Failure;
                }

                //SqlConnection connection = connections[_connectionName].AcquireConnection(null) as SqlConnection;
                _connection = connections[_connectionName].AcquireConnection(null) as DbConnection;

                if (_connection == null)
                {
                    ConnectionManager cm = connections[_connectionName];
                    Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject as Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
                    _connection = cmParams.GetConnectionForSchema() as OleDbConnection;

                    if (_connection == null)
                    {
                        componentEvents.FireError(0, METHOD_NAME, "The connection is not a valid ADO.NET or OLEDB connection", "", -1);
                        return DTSExecResult.Failure;
                    }
                }

                if (!variableDispenser.Contains("System::SourceID"))
                {
                    componentEvents.FireError(0, METHOD_NAME, "No System::SourceID variable available. This task can only be used in an Event Handler", "", -1);
                    return DTSExecResult.Failure;
                }

                return DTSExecResult.Success;
            }
            catch (Exception exc)
            {
                componentEvents.FireError(0, METHOD_NAME, "Validation Failed: " + exc.ToString(), "", -1);
                return DTSExecResult.Failure;
            }
        }

        public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
        {
            try
            {
                string commandText = null;

                ReadVariables(variableDispenser);
                //DbConnection connection = connections[_connectionName].AcquireConnection(transaction) as DbConnection;
                //SqlConnection connection = (SqlConnection)connections[_connectionName].AcquireConnection(transaction);
                DbCommand command = null;

                //using (SqlCommand command = new SqlCommand())
                if (_connection is SqlConnection)
                {
                    commandText = @"INSERT INTO SSISLog (EventType, PackageName, TaskName, EventCode, EventDescription, PackageDuration, Host, ExecutionID, EventHandlerDateTime,UID)
                                    VALUES (@EventType, @PackageName, @TaskName, @EventCode, @EventDescription, @PackageDuration, @Host, @Executionid, @handlerdatetime,@uid)";

                    command = new SqlCommand();

                    command.Parameters.Add(new SqlParameter("@EventType", _eventType));
                    command.Parameters.Add(new SqlParameter("@PackageName", _packageName));
                    command.Parameters.Add(new SqlParameter("@TaskName", _taskName));
                    command.Parameters.Add(new SqlParameter("@EventCode", _errorCode ?? string.Empty));
                    command.Parameters.Add(new SqlParameter("@EventDescription", _errorDescription ?? string.Empty));
                    command.Parameters.Add(new SqlParameter("@PackageDuration", _packageDuration));
                    command.Parameters.Add(new SqlParameter("@Host", _machineName));
                    command.Parameters.Add(new SqlParameter("@ExecutionID", _executionid));
                    command.Parameters.Add(new SqlParameter("@handlerdatetime", _handlerdatetime));
                    command.Parameters.Add(new SqlParameter("@uid", _uid));
                }
                else if (_connection is OleDbConnection)
                {
                    commandText = @"INSERT INTO SSISLog (EventType,PackageName,TaskName,EventCode,EventDescription,PackageDuration,Host,ExecutionID,EventHandlerDateTime,UID)
                                    VALUES (?,?,?,?,?,?,?,?,?,?)";

                    command = new OleDbCommand();

                    command.Parameters.Add(new OleDbParameter("@EventType", _eventType));
                    command.Parameters.Add(new OleDbParameter("@PackageName", _packageName));
                    command.Parameters.Add(new OleDbParameter("@TaskName", _taskName));
                    command.Parameters.Add(new OleDbParameter("@EventCode", _errorCode ?? string.Empty));
                    command.Parameters.Add(new OleDbParameter("@EventDescription", _errorDescription ?? string.Empty));
                    command.Parameters.Add(new OleDbParameter("@PackageDuration", _packageDuration));
                    command.Parameters.Add(new OleDbParameter("@Host", _machineName));
                    command.Parameters.Add(new OleDbParameter("@ExecutionID", _executionid));
                    command.Parameters.Add(new OleDbParameter("@handlerdatetime", _handlerdatetime));
                    command.Parameters.Add(new OleDbParameter("@uid", _uid));
                }

                command.CommandText = commandText;
                command.CommandType = CommandType.Text;
                command.Connection = _connection;

                command.ExecuteNonQuery();
                _connection.Close();
                return DTSExecResult.Success;

            }
            catch (Exception exc)
            {
                componentEvents.FireError(0, "CustomLoggingTask-Execute", "Task Errored: " + exc.ToString(), "", -1);
                return DTSExecResult.Failure;
            }
        }

        private void ReadVariables(VariableDispenser variableDispenser)
        {
            variableDispenser.LockForRead("System::StartTime");
            variableDispenser.LockForRead("System::PackageName");
            variableDispenser.LockForRead("System::SourceName");
            variableDispenser.LockForRead("System::MachineName");
            variableDispenser.LockForRead("System::ExecutionInstanceGUID");
            variableDispenser.LockForRead("System::EventHandlerStartTime");
            variableDispenser.LockForRead("User::UID");
            bool includesError = variableDispenser.Contains("System::ErrorCode");
            if (includesError)
            {
                variableDispenser.LockForRead("System::ErrorCode");
                variableDispenser.LockForRead("Syste