如何将连接管理器嵌入到 SSIS 脚本组件的 C# 代码中?

发布于 2024-09-13 03:34:57 字数 394 浏览 8 评论 0原文

当我打开脚本组件时,我可以从下拉列表中选择连接管理器:

在此处输入图像描述

这个连接管理器拥有一切,如果我将它作为 C# 代码中的对象,我就不需要不再编写硬编码的连接字符串。

我尝试使用 OLEDB 提供程序和 SQL 但失败了。

问题

  • 我应该如何在 SSIS 数据流任务的脚本组件中使用 OLE DB 连接管理器?
  • 或者,如果无法使用 OLE DB 提供程序,如何将连接管理器嵌入到 SSIS 脚本组件中?

When I open a Script component, I can choose a Connection Manager from a dropdown list:

enter image description here

This Connection Manager has it all, if I had it as an object in the C# code, I would not need to write a hardcoded connection string anymore.

I tried it with an OLEDB provider and SQL but failed.

Question(s)

  • How should I use an OLE DB Connection Manager in a Script Component of an SSIS Data Flow Task?
  • Or if an OLE DB provider cannot be used, how can I embed the Connection Manager in an SSIS Script Component instead?

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(4

月下伊人醉 2024-09-20 03:34:57

脚本任务和脚本组件之间的语法不同。查看这篇文章,了解更多并排比较:

http://msdn.microsoft.com/en-us/library/ms136031.aspx" rel="nofollow">http:// /msdn.microsoft.com/en-us/library/ms136031.aspx

The syntax is different between a Script Task and a Script Component. Check out this article for more than a couple side-by-side comparisons:

http://msdn.microsoft.com/en-us/library/ms136031.aspx

白日梦 2024-09-20 03:34:57

MSDN 上有详细记录,涵盖 VB 和 C# 类型的脚本: http: //msdn.microsoft.com/en-us/library/ms136018.aspx

This is well documented on MSDN, covering both VB and C# type of scripts: http://msdn.microsoft.com/en-us/library/ms136018.aspx

秋风の叶未落 2024-09-20 03:34:57
    IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component

    SqlConnection myADONETConnection = new SqlConnection();

    myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));


    //Read data from table or view to data table
    string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
   // string query = "Select  * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
    SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);

DataTable dtExcelData = new DataTable();
    adapter.Fill(dtExcelData);
    myADONETConnection.Close();
    IDTSConnectionManager100 connMgr = this.Connections.ADONetAppStaging ; //this we need to give name in connection manager in script component

    SqlConnection myADONETConnection = new SqlConnection();

    myADONETConnection = (SqlConnection)(connMgr.AcquireConnection(null));


    //Read data from table or view to data table
    string query = "Select top 10 * From ##AP_Stagging_Temp_ExportWODuplicates Order by 1,2,3 asc ";
   // string query = "Select  * From ##AP_Stagging_Temp_For_JLL_ExportWODuplicates order by 1,2,3 asc ";
    SqlDataAdapter adapter = new SqlDataAdapter(query, myADONETConnection);

DataTable dtExcelData = new DataTable();
    adapter.Fill(dtExcelData);
    myADONETConnection.Close();
旧城空念 2024-09-20 03:34:57

有时你必须重新发明轮子

我让它工作,但既没有借助这里的答案,也没有借助 可以通过脚本组件使用OleDbConnections吗?,并且一开始加载到VS中的“main.cs”的默认注释具有误导性,请参阅下文。

但这是值得的:现在我不再需要在 C# 代码中写下任何连接字符串,而只需加载所选连接管理器的连接字符串,而无需在代码中编写其名称。

代码

下面是占用脚本组件的连接管理器的代码,这样您就不再需要对连接字符串进行硬编码:

    public override void PreExecute()
    {
        base.PreExecute();
        string connectionString = Connections.Connection.ConnectionString;
        conn = new SqlConnection() { ConnectionString = connectionString };
        conn.Open();
        // create temp table, make sure it matches your input
        using (SqlCommand cmd = new SqlCommand(@"CREATE TABLE ##tmpTable(
[my_column1][int] NULL,
[my_column2][int] NULL,
...

技巧 1

其他答案已经告诉您的一个技巧是建立 ADO.NET 连接而不是建立 ADO.NET 连接。 OLE DB 连接。

技巧 2

不要听默认文件中的 ,这是 Visual Studio 中“VstaProjects”中默认“main.cs”C# 文件中的主要帮助,如果您单击“编辑”,则会打开该文件第一次脚本”:

在此处输入图像描述

#region 帮助:使用 Integration Services 连接管理器
/* 某些类型的连接管理器可以在此脚本组件中使用。请参阅帮助主题
 *“以编程方式使用连接管理器”了解详细信息。
 *
 * 要在此脚本中使用连接管理器,首先确保连接管理器具有
 * 已添加到连接管理器页面上的连接管理器列表中
 * 脚本组件编辑器。要添加连接管理器,请保存此脚本,关闭此实例
 * Visual Studio,并将连接管理器添加到列表中。
 *
 * 如果组件需要在处理行时保持连接打开,请覆盖
 * AcquireConnections 和 ReleaseConnections 方法。
 * 
 * 使用 ADO.Net 连接管理器获取 SqlConnection 的示例:
 * 对象 rawConnection = Connections.SalesDB.AcquireConnection(transaction);
 * SqlConnection salesDBConn = (SqlConnection)rawConnection;
 *
 * 使用文件连接管理器获取文件路径的示例:
 * 对象 rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
 * 字符串文件路径 = (字符串)rawConnection;
 *
 * 释放连接管理器的示例:
 * Connections.SalesDB.ReleaseConnection(rawConnection);
 */

您不需要在任何地方写下连接管理器的名称,如果我没有弄错的话,您无法通过在任何地方写下它来使代码正常工作,除非您自己对连接字符串进行硬编码。相反,您在菜单中选择的连接管理器会加载到 Connections 对象中。由于我无法在该对象的属性中找到默认注释中所述的名称,因此我实时调试了 Connections 对象,发现在加载 Connections 时>,完整的连接字符串已经是 Connections 对象的动态视图的一个属性(注意:此连接字符串是从菜单加载的,没有任何硬编码):

在此处输入图像描述

奇怪的是,您必须一次性从 Connections 对象获取连接字符串:您不能一开始就像 rawConnection 那样加载 Connections 对象您可以请求属性的对象,正如默认注释告诉您的那样。这就是为什么我将 rawConnection = Connections 替换为 string connectionString = Connections.Connection.ConnectionString;。我想这是需要的,因为它处于“动态视图”中,因此必须立即获取它,但这只是一个猜测,欢迎评论。以下是调试器如何介入以查看对象如何构建的实时情况:

在此处输入图像描述

Sometimes you have to reinvent the wheel

I got it to work, but neither with the help of the answers here nor with It's possible to use OleDbConnections with the Script Component?, and the default remarks of the "main.cs" that loads in VS at the beginning were misleading, see further below.

But it was worth the time: now I do not need to write down any connection string anymore in the C# code but just load the chosen connection manager's connection string without needing to write its name in the code.

Code

Here is the code that takes up the connection manager of the script component so that you do not need to hardcode the connection string anymore:

    public override void PreExecute()
    {
        base.PreExecute();
        string connectionString = Connections.Connection.ConnectionString;
        conn = new SqlConnection() { ConnectionString = connectionString };
        conn.Open();
        // create temp table, make sure it matches your input
        using (SqlCommand cmd = new SqlCommand(@"CREATE TABLE ##tmpTable(
[my_column1][int] NULL,
[my_column2][int] NULL,
...

Trick 1

The one trick that the other answers already tell is to make an ADO.NET connection instead of an OLE DB connection.

enter image description here

Trick 2

Do not listen to the remarks in the default file, the main help inside the default "main.cs" C# file in "VstaProjects" in Visual Studio that opens up if you click on "Edit script" the first time:

enter image description here

#region Help:  Using Integration Services Connection Managers
/* Some types of connection managers can be used in this script component.  See the help topic
 * "Working with Connection Managers Programatically" for details.
 *
 * To use a connection manager in this script, first ensure that the connection manager has
 * been added to either the list of connection managers on the Connection Managers page of the
 * script component editor.  To add the connection manager, save this script, close this instance of
 * Visual Studio, and add the Connection Manager to the list.
 *
 * If the component needs to hold a connection open while processing rows, override the
 * AcquireConnections and ReleaseConnections methods.
 * 
 * Example of using an ADO.Net connection manager to acquire a SqlConnection:
 *  object rawConnection = Connections.SalesDB.AcquireConnection(transaction);
 *  SqlConnection salesDBConn = (SqlConnection)rawConnection;
 *
 * Example of using a File connection manager to acquire a file path:
 *  object rawConnection = Connections.Prices_zip.AcquireConnection(transaction);
 *  string filePath = (string)rawConnection;
 *
 * Example of releasing a connection manager:
 *  Connections.SalesDB.ReleaseConnection(rawConnection);
 */

You do not need to write the name of the connection manager anywhere, and if I am not mistaken, you cannot get the code to work by writing it down anywhere unless you hardcode the connection string yourself. Instead, the connection manager that you choose in the menu is the one that is loaded into the Connections object. Since I could not find the name in the attributes of this object as it is said in the default remarks, I debugged the Connections object live and found that at the point of loading Connections, the full connection string is already an attribute of the Dynamic View of the Connections object (and mind: this connection string is loaded from the menu without any hardcoding):

enter image description here

Strangely, you have to get the connection string from the Connections object in one go: you cannot at first just load the Connections object like a rawConnection object that you can ask for attributes, as the default remarks tells you. That is why I replaced the rawConnection = Connections with string connectionString = Connections.Connection.ConnectionString;. I guess that this is needed since it is in a "Dynamic View" so that it must be fetched right away, but this is just a guess, remarks are welcome. Here is live how the debugger steps in to see how the object gets built:

enter image description here

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文