缺少库引用OLEDB连接类型 [英] Missing library to reference OLEDB connection types
本文介绍了缺少库引用OLEDB连接类型的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我得到了下面这段code从<一个href="http://blogs.msdn.com/b/mattm/archive/2008/08/22/accessing-oledb-connection-managers-in-a-script.aspx"相对=nofollow> SSIS团队博客投的OLEDB连接类型，以便它可以通过的AcquireConnection（）方法。现在，我不知道为什么Dts.Connections部分不能正常工作。我不知道，我必须补充，使其工作库中。我pretty的多加入最重要的包括Dts.RuntimeWrap。请让我知道如果你需要对这个问题的详细信息。
的ConnectionManager厘米= Dts.Connections [OLEDB];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject为Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
OleDbConnection的康恩= cmParams.GetConnectionForSchema（）作为OleDbConnection的;
修改 下面是整个code这个组件。
使用系统;
使用System.Collections.Generic;
使用System.Data这;
使用System.Data.SqlClient的;
使用Microsoft.SqlServer.Dts.Runtime;
使用System.Data.OleDb;
使用System.Data.Common;
使用System.Linq的;
使用System.Configuration;
System.Collections中使用;
//使用System.Data.OleDb;
命名空间AOC.SqlServer.Dts.Tasks
{
[DtsTask（
显示名称=自定义日志记录任务，
说明=将日志信息到表）]
公共类CustomLoggingTask：任务
{
私人字符串_packageName;
私人字符串_taskName;
私人字符串_error code;
私人字符串_errorDescription;
私人字符串_MachineName;
私人双人_packageDuration;
私人字符串_connectionName;
私人字符串_eventType;
私人字符串_executionid;
私营的DateTime _handlerdatetime;
私人字符串_uid;
公共字符串了ConnectionName
{
组
{
_connectionName =价值;
}
得到
{
返回_connectionName;
}
}
公共字符串事件
{
组
{
_eventType =价值;
}
得到
{
返回_eventType;
}
}
公众覆盖DTSExecResult验证（连接连接，VariableDispenser variableDispenser，IDTSComponentEvents componentEvents，IDTSLogging日志）
{
常量字符串METHOD_NAME =CustomLoggingTask - 验证;
尝试
{
如果（string.IsNullOrEmpty（_eventType））
{
componentEvents.FireError（0，METHOD_NAME，事件属性必须指定，，-1）;
返回DTSExecResult.Failure;
}
如果（string.IsNullOrEmpty（_connectionName））
{
componentEvents.FireError（0，METHOD_NAME，没有连接已指定，，-1）;
返回DTSExecResult.Failure;
}
// SqlConnection的连接=连接[_connectionName] .AcquireConnection（空）为SqlConnection的;
的DbConnection连接=连接[_connectionName] .AcquireConnection（空）作为的DbConnection;
的ConnectionManager厘米= Dts.Connections [OLEDB];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject为Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
OleDbConnection的康恩= cmParams.GetConnectionForSchema（）作为OleDbConnection的;
如果（连接== NULL）
{
componentEvents.FireError（0，METHOD_NAME，连接不是有效的ADO.NET连接，，-1）;
返回DTSExecResult.Failure;
}
如果（！variableDispenser.Contains（系统::的SourceID））
{
componentEvents.FireError（0，METHOD_NAME，没有系统::的SourceID变量可用此任务只能在事件处理程序使用，，-1）;
返回DTSExecResult.Failure;
}
返回DTSExecResult.Success;
}
赶上（例外EXC）
{
componentEvents.FireError（0，METHOD_NAME，验证失败：+ exc.ToString（），，-1）;
返回DTSExecResult.Failure;
}
}
公众覆盖DTSExecResult执行（连接连接，VariableDispenser variableDispenser，IDTSComponentEvents componentEvents，IDTSLogging日志，对象交易）
{
尝试
{
字符串的CommandText =
@INSERT INTO SSISLog（事件类型，软件包名，TASKNAME，事件code，EventDescription，PackageDuration，主机，流程id，EventHandlerDateTime，UID）
VALUES（@EventType，@PackageName，@TaskName，@事件code，@EventDescription，@PackageDuration，@host，@Executionid，@ handlerdatetime，@ UID）;
ReadVariables（variableDispenser）;
的DbConnection连接=连接[_connectionName] .AcquireConnection（交易）为的DbConnection;
// SqlConnection的连接=（SqlConnection的）连接[_connectionName] .AcquireConnection（交易）;
的DbCommand命令= NULL;
//使用（SqlCommand的命令=新的SqlCommand（））
如果（连接的SqlConnection）
命令=新的SqlCommand（）;
否则，如果（连接的OleDbConnection）
命令=新的OleDbCommand（）;
{
command.CommandText =的CommandText;
command.CommandType = CommandType.Text;
command.Connection =连接;
command.Parameters.Add（新的SqlParameter（@事件类型，_eventType））;
command.Parameters.Add（新的SqlParameter（@软件包名，_packageName））;
command.Parameters.Add（新的SqlParameter（@ TASKNAME，_taskName））;
command.Parameters.Add（新的SqlParameter（@事件code，_error code 18的String.Empty））;
command.Parameters.Add（新的SqlParameter（@ EventDescription，_errorDescription ??的String.Empty））;
command.Parameters.Add（新的SqlParameter（@ PackageDuration，_packageDuration））;
command.Parameters.Add（新的SqlParameter（@主机，_MachineName））;
command.Parameters.Add（新的SqlParameter（@流程id，_executionid））;
command.Parameters.Add（新的SqlParameter（@ handlerdatetime，_handlerdatetime））;
command.Parameters.Add（新的SqlParameter（@流体，_uid））;
command.ExecuteNonQuery（）;
}
的Connection.close（）;
返回DTSExecResult.Success;
}
赶上（例外EXC）
{
componentEvents.FireError（0，CustomLoggingTask-执行，任务误码：+ exc.ToString（），，-1）;
返回DTSExecResult.Failure;
}
}
私人无效ReadVariables（VariableDispenser variableDispenser）
{
variableDispenser.LockForRead（系统::开始时间）;
variableDispenser.LockForRead（系统：软件包名）;
variableDispenser.LockForRead（系统:: SOURCENAME）;
variableDispenser.LockForRead（系统：计算机名）;
variableDispenser.LockForRead（系统:: ExecutionInstanceGUID）;
variableDispenser.LockForRead（系统:: EventHandlerStartTime）;
variableDispenser.LockForRead（用户:: UID）;
布尔includesError = variableDispenser.Contains（系统::错误code）;
如果（includesError）
{
variableDispenser.LockForRead（系统::错误code）;
variableDispenser.LockForRead（系统：ErrorDescription中）;
}
变量瓦尔= NULL;
variableDispenser.GetVariables（REF瓦尔）;
日期时间的startTime =（DateTime的）瓦尔[系统::开始时间]值。
_packageDuration = DateTime.Now.Subtract（startTime时）.TotalSeconds;
。_packageName =瓦尔[系统：软件包名] Value.ToString（）;
。_taskName =瓦尔[系统:: SOURCENAME] Value.ToString（）;
。_MachineName =瓦尔[系统：计算机名] Value.ToString（）;
_executionid =瓦尔[系统:: ExecutionInstanceGUID] Value.ToString（）。
_handlerdatetime =（DateTime的）瓦尔[系统:: EventHandlerStartTime]值。
_uid =瓦尔[用户:: UID] Value.ToString（）。
如果（includesError）
{
_error code =瓦尔[系统::错误code] Value.ToString（）。
_errorDescription =瓦尔[系统：ErrorDescription中] Value.ToString（）。
}
//释放该变量的锁。
vars.Unlock（）;
//重新设置饮水机
variableDispenser.Reset（）;
}
}
}
解决方案
通过您发布的code，我终于想通了，知道你想完成的任务。
这些事情是我对我所做的得到它的工作：
A）的编译错误您收到：
错误1类型或命名空间名称'连接'没有命名空间存在'AOC.SqlServer.Dts（是否缺少程序集引用？）
仅仅是因为你想获得的方式的 OLEDB 的连接管理器：
的ConnectionManager厘米= Dts.Connections [OLEDB];
在 DTS 对象是在脚本任务组件提供的一种设施。您应该替换该行有：
的ConnectionManager厘米=连接[OLEDB];
我在你的code见过你访问一个连接管理器以这种方式，也许这是你刚才留下的东西。
B）为了验证，如果连接管理器可以是一个ADO.NET连接管理器或OLEDB之一，改变了这部分的code：
的DbConnection连接=连接[_connectionName] .AcquireConnection（空）作为的DbConnection;
的ConnectionManager厘米= Dts.Connections [OLEDB];
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject为Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
OleDbConnection的康恩= cmParams.GetConnectionForSchema（）作为OleDbConnection的;
如果（连接== NULL）
{
componentEvents.FireError（0，METHOD_NAME，连接不是有效的ADO.NET连接，，-1）;
返回DTSExecResult.Failure;
}
首先，在类的级别增加了一个私有变量来存储连接：
私人的DbConnection _connection;
然后，修改了验证，以检查连接的ADO.NET，并且如果它不是，检查它是否是OLEDB：
_connection =连接[_connectionName] .AcquireConnection（空）作为的DbConnection;
如果（_connection == NULL）
{
的ConnectionManager厘米=连接[_connectionName]
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject为Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
_connection = cmParams.GetConnectionForSchema（）作为OleDbConnection的;
如果（_connection == NULL）
{
componentEvents.FireError（0，METHOD_NAME的连接不是有效的ADO.NET或OLEDB连接，，-1）;
返回DTSExecResult.Failure;
}
}
请注意，我更换了硬盘codeD OLEDB与 _connectionName 变量，并且还修改错误字符串时， _connection 在这两种情况下空。
C）要使用OLEDB提供程序执行的命令是necesary以下变化：
- 使用新的
_connection变量，至极持有previously检索连接。 - 添加到命令的参数必须是
OleDbParameter而不是的SqlParameter。 - 的 OLE DB .NET提供不支持命名参数。因此，
值在插入语句命令部分需要修改，以使用？。
D）这是完整的工作code：
使用系统;
使用System.Data这;
使用System.Data.Common;
使用System.Data.OleDb;
使用System.Data.SqlClient的;
使用Microsoft.SqlServer.Dts.Runtime;
//使用System.Data.OleDb;
命名空间AOC.SqlServer.Dts.Tasks
{
[DtsTask（
显示名称=自定义日志记录任务，
说明=将日志信息到表）]
公共类CustomLoggingTask：任务
{
私人字符串_packageName;
私人字符串_taskName;
私人字符串_error code;
私人字符串_errorDescription;
私人字符串_MachineName;
私人双人_packageDuration;
私人字符串_connectionName;
私人字符串_eventType;
私人字符串_executionid;
私营的DateTime _handlerdatetime;
私人字符串_uid;
公共字符串了ConnectionName
{
集合{_connectionName =价值; }
{返回_connectionName; }
}
公共字符串事件
{
集合{_eventType =价值; }
{返回_eventType; }
}
私人的DbConnection _connection;
公众覆盖DTSExecResult验证（连接连接，VariableDispenser variableDispenser，IDTSComponentEvents componentEvents，IDTSLogging日志）
{
常量字符串METHOD_NAME =CustomLoggingTask - 验证;
尝试
{
如果（string.IsNullOrEmpty（_eventType））
{
componentEvents.FireError（0，METHOD_NAME，事件属性必须指定，，-1）;
返回DTSExecResult.Failure;
}
如果（string.IsNullOrEmpty（_connectionName））
{
componentEvents.FireError（0，METHOD_NAME，没有连接已指定，，-1）;
返回DTSExecResult.Failure;
}
// SqlConnection的连接=连接[_connectionName] .AcquireConnection（空）为SqlConnection的;
_connection =连接[_connectionName] .AcquireConnection（空）作为的DbConnection;
如果（_connection == NULL）
{
的ConnectionManager厘米=连接[_connectionName]
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100 cmParams = cm.InnerObject为Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100;
_connection = cmParams.GetConnectionForSchema（）作为OleDbConnection的;
如果（_connection == NULL）
{
componentEvents.FireError（0，METHOD_NAME的连接不是有效的ADO.NET或OLEDB连接，，-1）;
返回DTSExecResult.Failure;
}
}
如果（！variableDispenser.Contains（系统::的SourceID））
{
componentEvents.FireError（0，METHOD_NAME，没有系统::的SourceID变量可用此任务只能在事件处理程序使用，，-1）;
返回DTSExecResult.Failure;
}
返回DTSExecResult.Success;
}
赶上（例外EXC）
{
componentEvents.FireError（0，METHOD_NAME，验证失败：+ exc.ToString（），，-1）;
返回DTSExecResult.Failure;
}
}
公众覆盖DTSExecResult执行（连接连接，VariableDispenser variableDispenser，IDTSComponentEvents componentEvents，IDTSLogging日志，对象交易）
{
尝试
{
字符串的CommandText = NULL;
ReadVariables（variableDispenser）;
//的DbConnection连接=连接[_connectionName] .AcquireConnection（交易）为的DbConnection;
// SqlConnection的连接=（SqlConnection的）连接[_connectionName] .AcquireConnection（交易）;
的DbCommand命令= NULL;
//使用（SqlCommand的命令=新的SqlCommand（））
如果（_connection是的SqlConnection）
{
的CommandText = @INSERT INTO SSISLog（事件类型，软件包名，TASKNAME，事件code，EventDescription，PackageDuration，主机，流程id，EventHandlerDateTime，UID）
VALUES（@EventType，@PackageName，@TaskName，@事件code，@EventDescription，@PackageDuration，@host，@Executionid，@ handlerdatetime，@ UID）;
命令=新的SqlCommand（）;
command.Parameters.Add（新的SqlParameter（@事件类型，_eventType））;
command.Parameters.Add（新的SqlParameter（@软件包名，_packageName））;
command.Parameters.Add（新的SqlParameter（@ TASKNAME，_taskName））;
command.Parameters.Add（新的SqlParameter（@事件code，_error code 18的String.Empty））;
command.Parameters.Add（新的SqlParameter（@ EventDescription，_errorDescription ??的String.Empty））;
command.Parameters.Add（新的SqlParameter（@ PackageDuration，_packageDuration））;
command.Parameters.Add（新的SqlParameter（@主机，_MachineName））;
command.Parameters.Add（新的SqlParameter（@流程id，_executionid））;
command.Parameters.Add（新的SqlParameter（@ handlerdatetime，_handlerdatetime））;
command.Parameters.Add（新的SqlParameter（@流体，_uid））;
}
否则，如果（_connection是的OleDbConnection）
{
的CommandText = @INSERT INTO SSISLog (EventType,PackageName,TaskName,Event$c$c,EventDescription,PackageDuration,Host,ExecutionID,EventHandlerDateTime,UID)
值（，，，，，，，，，？？？？？？？？？？）;
命令=新的OleDbCommand（）;
command.Parameters.Add（新OleDbParameter（@事件类型，_eventType））;
command.Parameters.Add（新OleDbParameter（@软件包名，_packageName））;
command.Parameters.Add（新OleDbParameter（@ TASKNAME，_taskName））;
command.Parameters.Add（新OleDbParameter（@事件code，_error code 18的String.Empty））;
command.Parameters.Add（新OleDbParameter（@ EventDescription，_errorDescription ??的String.Empty））;
command.Parameters.Add（新OleDbParameter（@ PackageDuration，_packageDuration））;
command.Parameters.Add（新OleDbParameter（@主机，_MachineName））;
command.Parameters.Add（新OleDbParameter（@流程id，_executionid））;
command.Parameters.Add（新OleDbParameter（@ handlerdatetime，_handlerdatetime））;
command.Parameters.Add（新OleDbParameter（@流体，_uid））;
}
command.CommandText =的CommandText;
command.CommandType = CommandType.Text;
command.Connection = _connection;
command.ExecuteNonQuery（）;
_connection.Close（）;
返回DTSExecResult.Success;
}
赶上（例外EXC）
{
componentEvents.FireError（0，CustomLoggingTask-执行，任务误码：+ exc.ToString（），，-1）;
返回DTSExecResult.Failure;
}
}
私人无效ReadVariables（VariableDispenser variableDispenser）
{
variableDispenser.LockForRead（系统::开始时间）;
variableDispenser.LockForRead（系统：软件包名）;
variableDispenser.LockForRead（系统:: SOURCENAME）;
variableDispenser.LockForRead（系统：计算机名）;
variableDispenser.LockForRead（系统:: ExecutionInstanceGUID）;
variableDispenser.LockForRead（系统:: EventHandlerStartTime）;
variableDispenser.LockForRead（用户:: UID）;
布尔includesError = variableDispenser.Contains（系统::错误code）;
如果（includesError）
{
variableDispenser.LockForRead（系统::错误code）;
variableDispenser.LockForRead（系统：ErrorDescription中）;
}
变量瓦尔= NULL;
variableDispenser.GetVariables（REF瓦尔）;
日期时间的startTime =（DateTime的）瓦尔[系统::开始时间]值。
_packageDuration = DateTime.Now.Subtract（startTime时）.TotalSeconds;
。_packageName =瓦尔[系统：软件包名] Value.ToString（）;
。_taskName =瓦尔[系统:: SOURCENAME] Value.ToString（）;
。_MachineName =瓦尔[系统：计算机名] Value.ToString（）;
_executionid =瓦尔[系统:: ExecutionInstanceGUID] Value.ToString（）。
_handlerdatetime =（DateTime的）瓦尔[系统:: EventHandlerStartTime]值。
_uid =瓦尔[用户:: UID] Value.ToString（）。
如果（includesError）
{
_error code =瓦尔[系统::错误code] Value.ToString（）。
_errorDescription =瓦尔[系统：ErrorDescription中] Value.ToString（）。
}
//释放该变量的锁。
vars.Unlock（）;
//重新设置饮水机
variableDispenser.Reset（）;
}
}
}
只是为了记录在案，我离开你，我已经找到了发展有用的一些链接，部署和调试自定义组件（但也许你已经经历过他们！）：
<一个href="http://bennyaustin.word$p$pss.com/2009/06/30/steps-to-build-and-deploy-custom-ssis-components/" rel="nofollow">http://bennyaustin.word$p$pss.com/2009/06/30/steps-to-build-and-deploy-custom-ssis-components/
http://msdn.microsoft。 COM / EN-US /库/ ms403356％28V = SQL.105％29.aspx
的http://toddmcdermid.blogspot.com.ar/2009/06/converting-your-script-task-into-custom_22.html
干杯。
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) 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
_connectionvariable, wich holds the previously retrieved connection. - The parameters added to the command must be
OleDbParameterinstead ofSqlParameter. - The OLE DB .NET Provider does not support named parameters. So, the
VALUESpart in theINSERTstatement 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("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
Cheers.
这篇关于缺少库引用OLEDB连接类型的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
