1

Since I am using SMO to copy data from one database to another. I have used my own DB helper class. I am getting exception when i transfer data.

The Integration Services component is not installed or you do not have permission to use it.

My code is as follows:

public class DBHelper
{
    #region Private Variables
    private static string sourceSQLServer;
    private static string destinationSQLServer;
    private static string sourceDatabase;
    private static string destinationDatabase;
    #endregion

    #region Properties

    /// <summary>
    /// SourceSQLServer Holds Instance Name of Source SQL Server Database Name
    /// </summary>
    public static string SourceSQLServer
    {
        get { return DBHelper.sourceSQLServer; }
        set { DBHelper.sourceSQLServer = value; }
    }

    /// <summary>
    /// DestinationSQLServer Holds Instance Name of Destination SQL Server Database Name
    /// </summary>
    public static string DestinationSQLServer
    {
        get { return DBHelper.destinationSQLServer; }
        set { DBHelper.destinationSQLServer = value; }
    }

    /// <summary>
    /// SourceDatabase Holds Source Database 
    /// </summary>
    public static string SourceDatabase
    {
        get { return DBHelper.sourceDatabase; }
        set { DBHelper.sourceDatabase = value; }
    }

    /// <summary>
    /// DestinationDatabase Holds Destination Database Name
    /// </summary>
    public static string DestinationDatabase
    {
        get { return DBHelper.destinationDatabase; }
        set { DBHelper.destinationDatabase = value; }
    }
    #endregion

    #region Static Methods
    /// <summary>
    ///  CopyDatabase Copies Database 
    /// </summary>
    /// <param name="CopyData">True if Want to Copy Data otherwise False</param>
    public static void CopyDatabase(bool bCopyData)
    {
        //Set Source SQL Server Instance Information
        Server server = new Server(DBHelper.SourceSQLServer);

        //Set Source Database Name [Database to Copy]
        Database database = server.Databases[DBHelper.SourceDatabase];

        //Set Transfer Class Source Database
        Transfer transfer = new Transfer(database);

        //Yes I want to Copy All the Database Objects
        transfer.CopyAllObjects = true;

        //In case if the Destination Database / Objects Exists Drop them First
        transfer.DropDestinationObjectsFirst = true;

        //Copy Database Schema
        transfer.CopySchema = true;

        //Copy Database Data Get Value from bCopyData Parameter
        transfer.CopyData = bCopyData;

        //Set Destination SQL Server Instance Name
        transfer.DestinationServer = DBHelper.DestinationSQLServer;

        //Create The Database in Destination Server
        transfer.CreateTargetDatabase = true;

        //Set Destination Database Name
        Database ddatabase = new Database(server, DBHelper.DestinationDatabase);

        //Create Empty Database at Destination
        ddatabase.Create();

        //Set Destination Database Name
        transfer.DestinationDatabase = DBHelper.DestinationDatabase;

        //Include If Not Exists Clause in the Script
        transfer.Options.IncludeIfNotExists = true;

        //Start Transfer
        transfer.TransferData();

        //Release Server variable
        server = null;
    }
    #endregion
}

and on Main function I am calling this

DBHelper.SourceSQLServer = @"DESKTOP-PCEOPRM\SQLEXPRESS";

//Set Your Database Name Here (To Be Copied or Scripted)
DBHelper.SourceDatabase = "MetisEmptyDB";

////Set Destination SQL Server Name Here
DBHelper.DestinationSQLServer = @"DESKTOP-PCEOPRM\SQLEXPRESS";

//Set New Database Name Here 
DBHelper.DestinationDatabase = "NewDb";

//Set True if you want to copy Data
//Set False if you want to copy Only Schema
DBHelper.CopyDatabase(true);
Console.WriteLine("Scripting Finished");

Thanks in advance. If you find any other alternate of making a new db from existing one in C# than you can please share the code too.

vivek kv
  • 406
  • 6
  • 11
CodeGenius
  • 514
  • 1
  • 6
  • 21

2 Answers2

1

For me this error went away when I changed my dependency from SQL Server 2008 Express to SQL Server 2012 Express.

I referenced the assemblies in 

C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies

Not 

C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies

If you are able to change the dependency then it works.

Chris B.
  • 93
  • 8
0

The clue is in the error. You are trying to use code for SQL Server Integration Services but that is not included in SQL Server Express.

Also these things are a lot easier using T-SQL. You can easily create new empty databases and script the objects too. Or backup and restore the database.

I should not post the code here in stackoverflow as it will be better asked at dba.stackexchange.com 

BACKUP DATABASE AdventureWorks2012   
 TO DISK = 'C:\SQLServerBackups\AdvWorksData.bak' 
 WITH FORMAT;

and to restore on the other server

RESTORE DATABASE AdventureWorks2012  
  FROM DISK = 'C:\SQLServerBackups\AdventureWorks2012.bak';

Make sure that you understand what you are doing in SQL before you try and do it via C#. You would learn more about SQL if you did all of the sql manually using SQLCMD as recommended in previous questions.

Mike
  • 1,645
  • 3
  • 13
  • 21
  • i have used that code too here at the question, but instead of helping people are doing down vote. I dont't know why people do so with a beginner. If you can help please see this question. @Mike http://stackoverflow.com/questions/39019461/run-a-script-on-existing-database-from-sql-file-in-c-sharp/39019576 – CodeGenius Aug 22 '16 at 06:55
  • I have added the simplest sql example for backing up and restoring a database. Ensure that this works in SQL before you try and run it from c# – Mike Aug 22 '16 at 08:33
  • Thank you @Mike, it was simplest. I read three of the methods, one was SMO, second was the one you proposed me and the third was the process method with SQLcmd. Hope this one would work! – CodeGenius Aug 22 '16 at 10:23