Using the SqlBulkCopy Class with a user-defined CLR Assembly

I'm attempting to use the SqlBulkCopy class to transfer data.  One of the tables I am transferring has a column with a User-Defined Type.  When SqlBulkCopy tries to use the WriteToServer method on this column, I get a "FileNotFoundException":

System.IO.FileNotFoundException occurred
Message="Could not load file or assembly 'UserInformation, Version=1.0.2288.25371, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The system cannot find the file specified."

I checked the destination database, and the Assembly exists and the User-Defined Type exists.  The Assembly's Permission Set is "External Access".  If a write a Transact-SQL statement to insert data into this table, it works.  However, the data for  the column must be in binary format.

Any idea why the Assembly is not accessible by the SqlBulkCopy class?
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;

namespace DBScripter
{
    class BulkCopyCommand:IDBScripterCommand        
    {
        #region IDBScripterCommand Members

        public void Execute(ArgumentParser ap)
        {

            string srcDBName = ap["srcDBName"];

            if (string.IsNullOrEmpty(srcDBName))
            {
                  Console.WriteLine("You must enter a valid database name");
                  return;
            }

            string destDBName = ap["destDBName"];

            if (string.IsNullOrEmpty(destDBName))
            {
                Console.WriteLine("You must enter a valid database name");
                return;
            }

            string srcSrvName = ap["srcSrvName"];

            if (string.IsNullOrEmpty(srcSrvName))
            {
                Console.WriteLine("No server name entered.  Will use default value of localhost.");
                srcSrvName = "(local)";
            }

            string destSrvName = ap["destSrvName"];

            if (string.IsNullOrEmpty(destSrvName))
            {
                Console.WriteLine("No server name entered.  Will use default value of localhost.");
                destSrvName = "(local)";
            }

            string connSrc = string.Format("Persist Security Info=False;Integrated Security=true;Initial Catalog={0};server={1}" , srcDBName , srcSrvName);

            string connDest = string.Format("Persist Security Info=False;Integrated Security=true;Initial Catalog={0};server={1}", destDBName, destSrvName);

            // Get Source Database
            SqlConnection source = new SqlConnection(connSrc);

            // Get Destination Database
            SqlConnection dest = new SqlConnection(connDest);

            Server destSrv = new Server(destSrvName);
            Database destDB = new Database(destSrv, destDBName);

            Server srcSrv = new Server(srcSrvName);
            Database srcDB = srcSrv.Databases[srcDBName];

            destDB.Trustworthy = true;
            destDB.Create();

            source.Open();
            dest.Open();

            SqlCommand cmdChangeDBOwner = new SqlCommand("EXEC [dbo].[sp_changedbowner] @loginame = 'sa', @map = 'true'", dest);
            cmdChangeDBOwner.ExecuteNonQuery();

            Transfer objTransfer = default(Transfer);

            objTransfer = new Transfer(srcDB); // The specified database is the source of the transfer operation            
            objTransfer.DestinationServer = destSrvName;
            objTransfer.DestinationLoginSecure = true; // Destination Database is using Windows Authentication
            objTransfer.DestinationDatabase = destDBName;
            objTransfer.DropDestinationObjectsFirst = true;

            objTransfer.CopyAllSchemas = true; // Default is false

            // Don't want to copy all tables.  Some are excluded and others are empty.
            objTransfer.CopyAllTables = false; // Default is false

            objTransfer.CopyAllObjects = false;
            objTransfer.CopyData = false;
            objTransfer.CopyAllUserDefinedDataTypes = true;
            objTransfer.CopyAllUserDefinedTypes = true;
            objTransfer.CopyAllSqlAssemblies = true;
            objTransfer.CopyAllXmlSchemaCollections = true;

            // Deployment should fail if data cannot be scripted for any table
            objTransfer.Options.ContinueScriptingOnError = false;

            // These three options are evaluated as a combination
            objTransfer.Options.ScriptSchema = true; // Default is true
            objTransfer.Options.ScriptData = false; // Default is false
            objTransfer.Options.ScriptDrops = false; // Default is false
            
            objTransfer.Options.ScriptOwner = true; // Default is false
            
            // "DRI" stands for "Declarative Referential Integrity" (Examples of DRI Objects are keys and constraints)
            objTransfer.Options.DriAll = false; // Default is false
            
            objTransfer.Options.ExtendedProperties = false; // Default is false
            objTransfer.Options.FullTextCatalogs = false; // Default is false
            objTransfer.Options.FullTextIndexes = false; // Default is false
            objTransfer.Options.FullTextStopLists = false; // Default is false
            objTransfer.Options.Indexes = false; // Default is false
            objTransfer.Options.NoIdentities = true; // Default is false
            objTransfer.Options.PrimaryObject = false; // Default is false
            objTransfer.Options.Statistics = false; // Default is false
            objTransfer.Options.WithDependencies = false; // Default is false

            objTransfer.TransferData();

            // Get list of tables in hierarchical order
            List<TableLevelInfo> tableListSchema = DatabaseHierarchyGenerator.GetFKHierarchy(connSrc);

            try
            {

                foreach (TableLevelInfo tliSchemaList in tableListSchema)
                {

                    Table objTable = new Table(destDB, tliSchemaList.tableName, tliSchemaList.Schema);
                    Schema objSchema = destDB.Schemas[tliSchemaList.Schema];

                    if (objSchema == null)
                    {
                        objSchema = new Schema(destDB, tliSchemaList.Schema);
                        objSchema.Create();
                    }
                    
                    // System.Data cannot handle schema names that contain a "." character
                    // This code creates a new schema without the "." character
                    if (objSchema.Name.Contains("."))
                    {

                        // Create String variables to hold the schema names
                        String schemaName = objSchema.Name;
                        String schemaNameNew = "";

                        // Execute a Replace operation on schema name
                        schemaNameNew = schemaName.Replace(".", "");

                        // Create a new schema with the new name
                        Schema objNewSchema = new Schema (destDB, schemaNameNew);

                        // You may be loading multiple tables from this schema
                        // Check to see if the schema already exists
                        
                        // Schema exists, move the Table object to the new schema
                        if (destDB.Schemas.Contains(objNewSchema.Name))
                        {
                            objTable.ChangeSchema(objNewSchema.Name);
                        }
                        // Schema does NOT exist.  Create the new schema and then
                        // move the Table object to the new schema
                        else
                        {
                            objNewSchema.Create();
                            objTable.ChangeSchema(objNewSchema.Name);
                        }
                        
                    }

                    objTable.AnsiNullsStatus = true;
                    ColumnCollection srcColumns = srcDB.Tables[tliSchemaList.tableName, tliSchemaList.Schema].Columns;

                    foreach (Column objColumn in srcColumns)
                    {
                        Column c = new Column(objTable, objColumn.Name, objColumn.DataType);
                        c.Nullable = objColumn.Nullable;

                        if (objColumn.Name == "UserAudit")
                        {
                            DataType UserAudit = new DataType(SqlDataType.VarBinary, 118);
                            c.DataType = UserAudit;
                        }
                        else
                        {
                            c.DataType = objColumn.DataType;
                        }

                        objTable.Columns.Add(c);
                    }

                    objTable.Create();

                    SqlCommand cmdCopyData = new SqlCommand(@"SELECT * FROM [" + tliSchemaList.Schema + "].[" + tliSchemaList.tableName + "]", source);
                    cmdCopyData.CommandTimeout = 6000;

                    // using SqlDataReader to copy the rows:
                    using (SqlDataReader dr = cmdCopyData.ExecuteReader())
                    {

                        DateTime start = DateTime.Now;

                        SqlBulkCopy sbc = new SqlBulkCopy(dest);
                        sbc.BulkCopyTimeout = 6000;
                        sbc.BatchSize = 10000;
                        sbc.DestinationTableName = "[" + objTable.Schema + "].[" + objTable.Name + "]";

                        //if (objSchema.Name == "Authorize")
                        //{
                        //    SqlBulkCopyColumnMappingCollection colUserAuditColumns;

                        //    colUserAuditColumns.Add(
                        //}

                        Console.WriteLine("Beginning Copy ...");
                        sbc.WriteToServer(dr);
                        Console.WriteLine("Copy completed in {0} seconds.", DateTime.Now.Subtract(start).Seconds);
                        //Console.ReadLine();
                        sbc.Close();

                    }

                }

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                //destDB.Drop();
            }

            // close connections:
            source.Close();
            dest.Close();
            Console.WriteLine("Transfer completed");
            
        }

        #endregion
    
    }

}

Open in new window

WizardOfYonkers2008Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WizardOfYonkers2008Author Commented:
Thanks Sri.  Question, do I need to make a change to the Assembly code?  I tried updating my connection strings, but I get the same error.
Here are the updated strings:

string connSrc = string.Format("Persist Security Info=False;Integrated Security=true;Initial Catalog={0};server={1};Context Connection=false" , srcDBName , srcSrvName);
 
string connDest = string.Format("Persist Security Info=False;Integrated Security=true;Initial Catalog={0};server={1};Context Connection=false", destDBName, destSrvName);
0
WizardOfYonkers2008Author Commented:
The Context Connection issue was only part of the problem.  I also needed to add the DLL for the CLR Data Type as a reference to my project, and then I had to make sure that the database had the same version of the DLL as my project.
0
WizardOfYonkers2008Author Commented:
It was only part of the solution.  The DLL had not been added to my project, which was part of the reason the CLR Data Type was not available at run time.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.