个人中心
gpt4 book ai didi

c# - 缺少引用 OLEDB 连接类型的库

转载 作者：行者123 更新时间：2023-11-30 16:23:46 29 4
gpt4 key购买 nike

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

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;

编辑下面是这个组件的完整代码。 

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

}

最佳答案

根据您发布的代码，我终于明白了您要完成的任务。

这些是我为让它工作所做的事情:

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 连接管理器的方式:

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

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

ConnectionManager cm = connections["oledb"];

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

B) 要验证连接管理器是 ADO.NET 连接管理器还是 OLEDB 连接管理器，更改这部分代码:

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

首先，在类级别添加一个私有(private)变量来存储连接:

private DbConnection _connection;

然后，修改验证以检查连接是否为 ADO.NET，如果不是，则检查它是否为 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 时的错误字符串例。

C) 要使用 OLEDB 提供程序执行命令，需要进行以下更改:

  • 使用新的 _connection 变量，它保存之前检索到的连接。
  • 添加到命令的参数必须是OleDbParameter而不是SqlParameter。
  • OLE DB .NET Provider不支持命名参数。因此，INSERT语句命令中的VALUES部分需要修改为使用?。

D) 这是完整的工作代码:

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

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

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

干杯。

关于c# - 缺少引用 OLEDB 连接类型的库，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/11265706/

29 4 0
文章推荐： javascript - .click 将一段 json 添加到面板中
文章推荐： C - 创建带有值的动态矩阵
文章推荐： c# - 我们如何将我们的 C# 代码集成到 Drupal CMS 中
文章推荐： c# - 访问 azure 表实体
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com