user3747199 user3747199 - 1 year ago 70
SQL Question

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

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;

#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; }

#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

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

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

//Start Transfer

//Release Server variable
server = null;

and on Main function I am calling this


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

////Set Destination SQL Server Name Here

//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
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.

Answer Source

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

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

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.