SQL Server SMO TransferData() keeps failing

Question

I am using the TransferData method of the transfer class in SQL Server SMO. I have the call running on Windows XP, running SQL Server 2008 SP1, trying to transfer a table from SQL Server 2000 on another server to the XP machine. They both use the same SQL username and password. I have tested using the Import/Export Wizard and it ran fine.

The Exception is below and the event log has an entry "Package "ShellPackage" failed" error.

The exception error shows the query property as blank and no substitution for . I am assuming some DTS piece is failing but I am not sure which and why.

Exception: "ERROR : errorCode=-1073548784 description=Executing the query \"\" failed with the following error: \"The type initializer for '' threw an exception.\". Possible failure reasons: Problems with the query, \"ResultSet\" property not set correctly, parameters not set correctly, or connection not established correctly.\r\n helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}"

My Code:

try
            {
                string MasterUser = ConfigurationSettings.AppSettings["SQLUserName"];
                string MasterPassword = ConfigurationSettings.AppSettings["SQLPassword"];


                Server server = new Server(SourceServer);
                server.ConnectionContext.LoginSecure = false;
                server.ConnectionContext.Login = MasterUser;
                server.ConnectionContext.Password = MasterPassword;

                Database databaseSource = server.Databases[SourceDatabaseName];


                Transfer transfer = new Transfer(databaseSource);
                transfer.CopyAllObjects = false;
                transfer.DropDestinationObjectsFirst = false;
                transfer.UseDestinationTransaction = true;

                if (IsBasic)
                {
                    transfer.CopyAllDefaults = false;
                    transfer.Options.Indexes = false;
                    transfer.Options.DriAll = false;
                    transfer.CopyAllDefaults = false;
                }
                else
                {
                    transfer.CopyAllDefaults = true;
                    transfer.Options.Indexes = true;
                    transfer.Options.DriAll = true;
                    transfer.CopyAllDefaults = true;
                }

                transfer.Options.AnsiFile = true;
                transfer.Options.SchemaQualify = true;
                transfer.Options.WithDependencies = false;
                transfer.CreateTargetDatabase = false;
                transfer.CopySchema = true;

                if (CopyData)
                    transfer.CopyData = true;
                else
                    transfer.CopyData = false;

                transfer.DestinationServer = DestinationServer;
                transfer.DestinationDatabase = DestinationDatabaseName;
                transfer.DestinationLoginSecure = false;
                transfer.DestinationLogin = MasterUser;
                transfer.DestinationPassword = MasterPassword;


                //find the able object
                foreach (Table table in databaseSource.Tables)
                {
                    if (table.Name == TableName)
                    {
                        tableToTransfer = table;
                        break;
                    }
                }

                transfer.Options.IncludeIfNotExists = true;
                transfer.ObjectList.Add(tableToTransfer);
                transfer.TransferData();
            }
            catch (DbException dbExp)
            {
                throw new FaultException(dbExp.Message);
            }
            catch (Exception ex)
            {
                throw new FaultException(ex.Message);
            }

Answer 1

The service hosting the WCF service (code above) needed to run under a domain user.