1

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
Mostafa Elmoghazi
  • 2,124
  • 1
  • 21
  • 27

1 Answers1

1

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

qwertz1123
  • 1,173
  • 10
  • 27