如何使用 EzAPI 在远程 SQL Server 源和目标上创建和执行 SSIS 包

发布于 2024-11-25 14:14:32 字数 2090 浏览 0 评论 0原文

我需要创建一个控制台应用程序,可以将表从一个远程 sql server 实例复制到另一个远程 sql server 实例。

我正在使用一个名为 EzAPI 的库,

连接(源和目标)和表名称都将作为参数提供给控制台应用程序。

这是我的尝试:

public class OleDBToOleDB : EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzOleDbDestination, EzSqlOleDbCM>
    {

        public OleDBToOleDB(Package p) : base(p) { }

        public static implicit operator OleDBToOleDB(Package p) { return new OleDBToOleDB(p); }                

        public OleDBToOleDB(string SrcServer, string SrcDB, string SrcTable,string SrcUser,string SrcPassword, string DstServer, string DstDB, string DstTable,string DstUser,string DstPassword)            
        {
            SrcConn.SetConnectionString(SrcServer, SrcDB);
            SrcConn.ServerName = SrcServer;
            SrcConn.InitialCatalog = SrcDB;
            SrcConn.UserName = SrcUser;
            SrcConn.Password = SrcPassword;

            Source.Table = SrcTable;

            DestConn.SetConnectionString(DstServer, DstDB);
            DestConn.ServerName = DstServer;
            DestConn.InitialCatalog = DstDB;
            DestConn.UserName = DstUser;
            DestConn.Password = DstPassword;


            Dest.Table = DstTable;                        
        }        
    }

static void Main(string[] args)
        {            
            OleDBToOleDB p = new OleDBToOleDB("localhost", "TestDB", "Address", "sa", "123", "localhost", "DestDB", "Address", "sa", "123");

            p.Execute();

            Console.Write(string.Format("Package2 executed with result {0}\n",p.ExecutionResult));
        }

这段代码的问题是:

  • 它不会在目标服务器上创建表,所以我应该 我自己手动创建它。
  • 此代码在本地主机上成功运行,但是当我尝试 将服务器名称更改为远程服务器会引发此错误:

未处理的异常:System.Runtime.InteropServices.COMException (0xC020801C):HRESULT 异常:0xC020801C

在网上搜索后我发现这个错误意味着这个错误是一个Integration services AcquireConnections异常。

那么我怎样才能让这段代码在远程 SQL Server 实例上运行,并让包在传输数据之前在目标服务器上创建表。

先感谢您。

I need to create a Console application that can copy a table from one remote sql server instance to another remote sql server instance.

I'm using a library called EzAPI

Both connections (Source and Destinations) and table name will be provided as parameters to the Console application.

Here is my try:

public class OleDBToOleDB : EzSrcDestPackage<EzOleDbSource, EzSqlOleDbCM, EzOleDbDestination, EzSqlOleDbCM>
    {

        public OleDBToOleDB(Package p) : base(p) { }

        public static implicit operator OleDBToOleDB(Package p) { return new OleDBToOleDB(p); }                

        public OleDBToOleDB(string SrcServer, string SrcDB, string SrcTable,string SrcUser,string SrcPassword, string DstServer, string DstDB, string DstTable,string DstUser,string DstPassword)            
        {
            SrcConn.SetConnectionString(SrcServer, SrcDB);
            SrcConn.ServerName = SrcServer;
            SrcConn.InitialCatalog = SrcDB;
            SrcConn.UserName = SrcUser;
            SrcConn.Password = SrcPassword;

            Source.Table = SrcTable;

            DestConn.SetConnectionString(DstServer, DstDB);
            DestConn.ServerName = DstServer;
            DestConn.InitialCatalog = DstDB;
            DestConn.UserName = DstUser;
            DestConn.Password = DstPassword;


            Dest.Table = DstTable;                        
        }        
    }

static void Main(string[] args)
        {            
            OleDBToOleDB p = new OleDBToOleDB("localhost", "TestDB", "Address", "sa", "123", "localhost", "DestDB", "Address", "sa", "123");

            p.Execute();

            Console.Write(string.Format("Package2 executed with result {0}\n",p.ExecutionResult));
        }

The problem with this code is:

  • It does not create the table on the destination server, so I should
    create it manually by myself.
  • This code runs successfully on the localhost, but when I try to
    change the server name to a remote server it raises this error:

Unhandled Exception: System.Runtime.InteropServices.COMException
(0xC020801C): Exception from HRESULT: 0xC020801C

After searching on the web I found that this error means that this error is an Integration services AcquireConnections exception.

So how can I get this code running on a remote sql server instance and have the package create the table on the destination server before transferring the data.

Thank you in advance.

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

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

发布评论

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

评论(1

樱娆 2024-12-02 14:14:32

SSIS 需要在包生成期间将两者都表化,因为它必须获取表中每一列的完整元数据。您可以将 ValidateMetadata 属性设置为 false,在这种情况下,它不会验证它,但您需要自己填写所有数据。这并不容易。

我认为你能做的最简单的事情就是:
1)使用本地连接生成包并关闭目标组件的验证
2)现在将新的连接字符串设置为源和目标组件
3)运行包

这是一个不太干净的解决方法。这通常应该通过配置来完成,但由于您不保存包,您也可以尝试这样做。

我认为在没有 SSIS 的情况下使用 SqlBuldCopy 来实现你的任务也可以更容易

SSIS needs both tabled during the package generation, since it has to get the whole metadata for each column in your tables. You can set the ValidateMetadata property to false, in this case it will not validate it, but you will need to fill all the data by yourself. This is not easy.

I think the most easy thing you can do is:
1)generate the package with local connections and turn off the validation for your destination component
2)now set the new connection strings to your source and destination compoennts
3)run the package

this is a not really clean workaround. This should be usually done with a configurations, but since you do not save your package you can try this as well.

I think it can be also easier to implement your task with SqlBuldCopy without SSIS

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