c# - from - que es oledbdataadapter

Falta la biblioteca para hacer referencia a los tipos de conexión OLEDB

Question

Obtuve el siguiente fragmento de código del Blog del equipo SSIS para transmitir el tipo de conexión OLEDB para que pueda ser utilizado por el método AcquireConnection (). Ahora no estoy seguro de por qué la parte Dts.Connections no funciona. No conozco la biblioteca que tendría que agregar para que funcione. Agregué bastante los más importantes, incluido el Dts.RuntimeWrap. Por favor, avíseme si necesita más información sobre la pregunta.

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;

EDITAR A continuación se encuentra el código completo de este componente.

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

Con el código que publicaste, finalmente descubrí lo que intentabas lograr.

Estas son las cosas que he hecho para que funcione:

A) El error de compilación que estás recibiendo:

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

es simplemente porque la forma en que intentas obtener el administrador de conexión oledb :

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

El objeto Dts es una función disponible en el componente de tarea de script. Debes reemplazar esa línea con:

ConnectionManager cm = connections["oledb"];

He visto en tu código que accedes a Connection Manager de esta forma, así que tal vez fue algo que acabas de dejar.

B) Para validar si el administrador de conexión es un administrador de conexión ADO.NET o uno OLEDB, cambió esta parte de su código:

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

Primero, agregué una variable privada en el nivel de clase para almacenar la conexión:

private DbConnection _connection;

y luego, modificó la validación para verificar si la conexión es ADO.NET y, en caso de que no lo sea, para verificar si es 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;
    }
}

Tenga en cuenta que reemplacé el "oledb" codificado con la variable _connectionName , y también modifiqué la cadena de error cuando _connection es nula en ambos casos.

C) Para ejecutar el comando usando un proveedor OLEDB fueron necesarios los siguientes cambios:

• Use la nueva variable _connection , que contiene la conexión previamente recuperada.
• Los parámetros agregados al comando deben ser OleDbParameter lugar de SqlParameter .
• El proveedor OLE DB .NET no admite parámetros con nombre. Entonces, ¿la parte de VALUES en el comando de instrucción INSERT necesita ser modificada para usar ? .

D) Este es el código de trabajo completo:

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("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();
        }
    }
}

Solo para que conste, les dejo algunos enlaces que he encontrado útiles para desarrollar, implementar y depurar componentes personalizados (¡pero tal vez ya los haya revisado!):

http://bennyaustin.wordpress.com/2009/06/30/steps-to-build-and-deploy-custom-ssis-components/

http://msdn.microsoft.com/en-us/library/ms403356%28v=sql.105%29.aspx

http://toddmcdermid.blogspot.com.ar/2009/06/converting-your-script-task-into-custom_22.html

Aclamaciones.