Migrate data from multiple tables from source db to destinatin db

Hi friends,

After searching the web for couple of days i managed to do this along with one of my friend, so thought i should make it available for all the stragglers.

The scenario is- I have a source DB (i have SQL server, you may have different) and a destination DB (again SQL server). I wanna copy data from all the tables in the source to the destination DB which has identical tables.

The main issue is when we have multiple tables to be copied from source to destination and we put this in the simple data flow task under for each loop, the metadata does not get refreshed so it gives error of column mappings.

And another issue is we may not have SQL server always as source and destination.

so this has to be done programatically using script task, or if you want you may build a custom component also, but i found this approach easier.

I took a user variable to store table names and mapped it with the for each loop container.

Assigned the values to the variable (table names), you can do it with a separate task which supplies the table names from the source DB.


add a script task inside the for each loop container.


The most important part is the programming / code inside the script task.

For this you need to take reference of these

DTSRuntimeWrap DTSPipelineWrap

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO;
using System.Text;
using System.Collections.Generic;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;



namespace ST_5c5e95d7b5ce4b3c91c2a82c79477980.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion



public void Main()
{
// TODO: Add your code here
String tablename = Dts.Variables["User::TableName"].Value.ToString();

Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("SSIS.Pipeline.2")).InnerObject as MainPipe;

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "Source ConnectionManager";
cm.ConnectionString = "Data Source=SQL source;Initial Catalog=DBName;User ID=USER;Password=PWD;provider=SQLNCLI10.1;";

//Add a SQL Server connection manager that will be used later.
ConnectionManager cm1 = package.Connections.Add("OLEDB");
cm1.Name = "Destination ConnectionManager";
cm1.ConnectionString = "Data Source=SQL source;Initial Catalog=DBName;User ID=USER;Password=PWD;provider=SQLNCLI10.1;";

//Adding source component for Cache Database.
IDTSComponentMetaData100 component = dataFlow.ComponentMetaDataCollection.New();
//component.Name = "ADONETSource";
//component.ComponentClassID = "Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter, Microsoft.SqlServer.ADONETSrc, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91";
component.Name = "SQL Server Source";
component.ComponentClassID = "DTSAdapter.OLEDBSource.2";
CManagedComponentWrapper instance = component.Instantiate();
instance.ProvideComponentProperties();
if (component.RuntimeConnectionCollection.Count > 0)
{
//component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["Source ConnectionManager"]);
component.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
component.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;


}
instance.SetComponentProperty("AccessMode", 0);
instance.SetComponentProperty("OpenRowset", tablename);

// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();

// Adding destination component for SQL Server
IDTSComponentMetaData100 component1 = dataFlow.ComponentMetaDataCollection.New();
component1.Name = "SQL Server Destination";
component1.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
CManagedComponentWrapper instance1 = component1.Instantiate();
instance1.ProvideComponentProperties();
if (component1.RuntimeConnectionCollection.Count > 0)
{
//component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections["Destination ConnectionManager"]);
component1.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm1);
component1.RuntimeConnectionCollection[0].ConnectionManagerID = cm1.ID;

}

instance1.SetComponentProperty("AccessMode", 0);
instance1.SetComponentProperty("OpenRowset", tablename);

//instance1.SetComponentProperty("BulkInsertTableName","[DimAccount]");
//instance1.SetComponentProperty("BulkInsertKeepIdentity", true);
//instance1.SetComponentProperty("BulkInsertKeepNulls", true);

// Reinitialize the metadata.
instance1.AcquireConnections(null);
instance1.ReinitializeMetaData(); //Throws exception. Message: "Exception from HRESULT: 0xC0202072" . Even if I reinitialize metadata after iterating through inputs of the component, the same exception is thrown at this statement.
instance1.ReleaseConnections();

//set path between components
IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(component.OutputCollection[0], component1.InputCollection[0]); //Assuming this is correct

// Iterate through the inputs of the component.
foreach (IDTSInput100 input in component1.InputCollection)
{
// Get the virtual input column collection for the input.
IDTSVirtualInput100 vInput = input.GetVirtualInput();

// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time instance of the component.
IDTSInputColumn100 vCol = instance1.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
instance1.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);

}
}

// Save the package
string pkgPath = @"C:\My Documents\Visual Studio 2008\Projects\package.dtsx";

Microsoft.SqlServer.Dts.Runtime.Application appl = new Microsoft.SqlServer.Dts.Runtime.Application();

appl.SaveToXml(pkgPath, package, null);

package.Execute();


Dts.TaskResult = (int)ScriptResults.Success;
}
}
}