在SSIS脚本的DLL文件中应用ODBC

本文关键字：应用 ODBC 文件 DLL SSIS 脚本 | 更新日期: 2023-09-27 18:04:41

我试图删除在SQL Server 2008 R2中开发的15个SSIS包的脚本任务(c#， . net Framework 2.0)中的冗余。我正试图通过创建引用的DLL程序集来删除冗余。冗余代码应用ODBC连接。所以，说到这一点，我能够使用Visual Studio 2008创建DLL文件，用强密钥设置它并将其添加到GAC中。但是，当我运行SSIS包时，应用脚本引用自定义DLL，我看到以下熟悉的错误:

"ExecuteNonQuery: Connection属性未初始化。"

只有当我尝试应用在引用程序集中设置的类中定义的方法时才会发生这种情况。根据其他类似问题，解决这个问题的方法是将连接分配给SQLCommand，分配给构造函数或属性。但是我的情况涉及到在引用的DLL中实际定义连接时发生的错误。我以前从来没有尝试过，不知道这是否可能。为了提供帮助，我首先提供了下面的DLL代码。

using System; 
using System.Data; 
using System.Data.Odbc; 
using System.Collections; 
using System.Collections.Generic; 
using System.Text; 
using Microsoft.SqlServer.Dts.Pipeline.Wrapper; 
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
namespace XXXX.SSIS.Common
{
public class SQL
{
    protected IDTSConnectionManager100 connMgr;
    protected OdbcConnection oOdbcConnection;
    protected bool bDebugMode;
    public bool LookupExistence(string sSQL)
    {
        bool bReturn;
        OdbcCommand oOdbcCommand;
        OdbcDataReader oDataReader;
        bReturn = false;
        //LogMessage("Lookup SQL", sSQL.ToString(), "", bDebugMode);
        oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
        oOdbcCommand.CommandType = CommandType.Text;
        oDataReader = oOdbcCommand.ExecuteReader();
        if (oDataReader.HasRows)
        {
            bReturn = true;
        }
        oDataReader.Close();
        return bReturn;
    }
    public void ExecuteDML(string sSQL)
    {
        OdbcCommand oOdbcCommand;
        //LogMessage("DML SQL", sSQL.ToString(), "", bDebugMode);
        oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
        oOdbcCommand.CommandType = CommandType.Text;
        oOdbcCommand.ExecuteNonQuery();
    }
}
}
应用上述DLL代码的主脚本如下所示。我在SQL区域注释了两个方法(lookupexist和ExecuteDML)，因为我正试图从引用的DLL中应用它们。如果我使用cls调用SSIS包，则仅在运行SSIS包时出现错误消息。lookupexist和cls.ExecuteDML。但是，如果我删除方法调用前的"cls"并取消下面SQL区域的注释，则SSIS包将成功运行。我的猜测是，连接属性的初始化以某种方式需要从主脚本传递到引用的DLL，但我不确定如何做到这一点。 
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.Odbc;
using System.Text;
using System.Collections;
using System.Collections.Generic;
using XXXX.SSIS.Common;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
protected IDTSConnectionManager100 connMgr;
protected OdbcConnection oOdbcConnection;
protected bool bDebugMode;
protected string sSqlLookup;
protected string sSqlInsert;
protected string sSqlUpdate;
protected string sSqlDelete;
public override void PreExecute()
{
    base.PreExecute();
    /*
      Add your code here for preprocessing or remove if not needed
    */
    bDebugMode = Variables.varDebugMode;
    sSqlLookup = Variables.varOdbcLookup;
    sSqlInsert = Variables.varOdbcInsert;
    sSqlUpdate = Variables.varOdbcUpdate;
    sSqlDelete = Variables.varOdbcDelete;
}
public override void PostExecute()
{
    base.PostExecute();
    /*
      Add your code here for postprocessing or remove if not needed
      You can set read/write variables here, for example:
      Variables.MyIntVar = 100
    */
}
public override void Input0_ProcessInput(Input0Buffer Buffer)
{
    while (Buffer.NextRow())
    {
        Input0_ProcessInputRow(Buffer);
    }
}
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    /*
      Add your code here
    */
    string sSqlDml;
    string sLookup;
    Ball.SSIS.Common.SQL cls = new Ball.SSIS.Common.SQL();
    sLookup = String.Format(sSqlLookup, Row.ABAN8.ToString());
    LogMessage("Lookup Built", sLookup, "", bDebugMode);
    if (Row.modifiedflag == "3")
    {
        sSqlDml = sSqlDelete;
    }
    else
    {
        if (cls.LookupExistence(sLookup))
        {
            //Update
            sSqlDml = sSqlUpdate;
        }
        else
        {
            //Insert
            sSqlDml = sSqlInsert;
        }
    }

    LogMessage("sSqlDml", sSqlDml, "", bDebugMode);
    sSqlDml = String.Format(sSqlDml, 
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString(),
                            Row.xxxx.ToString()
                            );
    LogMessage("sSqlDml Formatted", sSqlDml, "", bDebugMode);
    LogMessage("ExecuteDML", "Executing DML statement.", "", bDebugMode);
    cls.ExecuteDML(sSqlDml);
}
#region SQL
 //private bool LookupExistence(string sSQL)
//{
//    bool bReturn;
//    OdbcCommand oOdbcCommand;
//    OdbcDataReader oDataReader;
//    bReturn = false;
//    LogMessage("Lookup SQL", sSQL.ToString(), "", bDebugMode);
//    oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
//    oOdbcCommand.CommandType = CommandType.Text;
//    oDataReader = oOdbcCommand.ExecuteReader();
//    if (oDataReader.HasRows)
//    {
//        bReturn = true;
//    }
//    oDataReader.Close();
//    return bReturn;
//}
//private void ExecuteDML(string sSQL)
//{
//    OdbcCommand oOdbcCommand;
//    LogMessage("DML SQL", sSQL.ToString(), "", bDebugMode);
//    oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
//    oOdbcCommand.CommandType = CommandType.Text;
//    oOdbcCommand.ExecuteNonQuery();
//}    #endregion
#region CONNECTIONS
public override void AcquireConnections(object Transaction)
{
    connMgr = this.Connections.QadConnection;
    oOdbcConnection = (OdbcConnection)connMgr.AcquireConnection(null);
}
public override void ReleaseConnections()
{
    connMgr.ReleaseConnection(oOdbcConnection);
}
#endregion
#region LOGGING
private void LogMessage(string tableName, string fieldName, string fieldValue, bool bDebug)
{
    if (bDebug)
    {
        bool bTrash = false;
        int iTrash = 1;
        this.ComponentMetaData.FireInformation(iTrash, tableName, fieldName, fieldValue, iTrash, ref bTrash);
    }
}
#endregion
}

如果您需要更多的信息，请提前告诉我。

在SSIS脚本的DLL文件中应用ODBC

连接错误的主要原因是没有在DLL中打开连接。当然，这也需要关闭它。为了以更简洁的方式完成此操作，我使用了带有try-catch的"using"命令。下面列出了两个更新后的方法的代码。

public bool LookupExistence(string sSQL)
    {
        //bool bReturn;
        // Applying the "using" both opens and closes the connection.
        // There is no need to dispose of the connection with this approach.
        using (OdbcConnection oOdbcConnection = new OdbcConnection())
        {
            OdbcCommand oOdbcCommand = new OdbcCommand(); 
            //bool bReturn;
            //Open the connection and execute the insert command.
            try
            {
                bool bReturn;
                OdbcDataReader oDataReader;
                bReturn = false;
                oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
                oOdbcCommand.CommandType = CommandType.Text;
                oDataReader = oOdbcCommand.ExecuteReader();
                if (oDataReader.HasRows)
                {
                    bReturn = true;
                }
                return bReturn;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
                bool bReturn = false;
                return bReturn;
            }
        }
    }
   public void ExecuteDML(string sSQL)
    {
        // Applying the "using" both opens and closes the connection.
        // There is no need to dispose of the connection with this approach.
        using (OdbcConnection oOdbcConnection = new OdbcConnection())
        {
            OdbcCommand oOdbcCommand = new OdbcCommand();
            //Open the connection and execute the insert command.
            try
            {
                oOdbcCommand = new OdbcCommand(sSQL, oOdbcConnection);
                oOdbcCommand.CommandType = CommandType.Text;
                oOdbcCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }
    }

谢谢。

相关文章：