通过您发布的代码，我终于弄清楚了您想要完成的任务。
为了让它发挥作用，我做了这些事情：
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"];
The 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 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在这两种情况下都为空。
C)要使用 OLEDB 提供程序执行命令，需要进行以下更改：
- 使用新的
_connection变量，保存先前检索到的连接。
- 命令中添加的参数必须是
OleDbParameter代替SqlParameter.
- The OLE DB .NET 提供程序 http://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters.aspx不支持命名参数。所以
VALUES部分于INSERT语句命令需要修改才能使用?.
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://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://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 http://toddmcdermid.blogspot.com.ar/2009/06/converting-your-script-task-into-custom_22.html
Cheers.
