缺少引用 OLEDB 连接类型的库 [英] Missing library to reference OLEDB connection types

问题描述

我从 SSIS 团队博客 来转换 OLEDB 连接类型，以便它可以被 AcquireConnection() 方法使用.现在我不确定为什么 Dts.Connections 部分不起作用.我不知道我必须添加的库才能使其工作.我几乎添加了最重要的，包括 Dts.RuntimeWrap.如果您需要有关该问题的更多信息，请告诉我.

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

}

推荐答案

通过你发布的代码，我终于弄清楚你想要完成什么.

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) 您收到的编译错误:

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

仅仅是因为您尝试获取oledb连接管理器的方式:

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

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

Dts 对象是脚本任务组件中可用的工具.您应该将该行替换为:

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

ConnectionManager cm = connections["oledb"];

我在您的代码中看到您以这种方式访问​​ Connection Manager，所以也许这是您刚刚留下的东西.

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) 要验证连接管理器是 ADO.NET 连接管理器还是 OLEDB 连接管理器，请更改代码的这一部分:

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;

然后，修改验证以检查连接是否为 ADO.NET，如果不是，则检查它是否为 OLEDB:

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

请注意，我将硬编码的 "oledb" 替换为 _connectionName 变量，并在 _connection 为 null 时修改了错误字符串案例.

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) 要使用 OLEDB 提供程序执行命令，必须进行以下更改:

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

• 使用新的 _connection 变量，其中包含先前检索到的连接.
• 添加到命令中的参数必须是OleDbParameter，而不是SqlParameter.
• OLE DB .NET 提供程序 不支持命名参数.所以，INSERT语句命令中的VALUES部分需要修改为使用?.

• 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) 这是完整的工作代码:

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

为了记录，我给您留下了一些我发现对开发、部署和调试自定义组件有用的链接(但也许您已经浏览过它们！):

Just for the record, I leave you some links that I've found useful for develop, deploy and debug custom components (but maybe you have already went through them!):

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

干杯.

这篇关于缺少引用 OLEDB 连接类型的库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！