I trying to transfer data from one table to another. And, after many attempts to make it works, I have errors:
Error 1 Validation error. {9C9FAC79-422F-4BE5-9AB4-A09CF49DFDB7}: OLE DB Destination [178]: Failure inserting into the read-only column "ID". testPackage (1).dtsx 0 0
Error 2 Validation error. {9C9FAC79-422F-4BE5-9AB4-A09CF49DFDB7}: OLE DB Destination [178]: Column metadata validation failed. testPackage (1).dtsx 0 0
I trying to move data from one table to another table with exactly same structure. What may be wrong in my code?
And another question. Actually I want to move data from one table to another, and may be differencies in columns between target and source database. How to exclude extra columns during transfer? My code is below:
private void Test()
{
try
{
//this is my code
///////////////////////////////////////////////////////////////////////
Package package = new Package();
MainPipe dataFlow = ((TaskHost)package.Executables.Add("DTS.Pipeline.1")).InnerObject as MainPipe;
//add connectionmanagers to package.
Microsoft.SqlServer.Dts.Runtime.ConnectionManager srcCM = package.Connections.Add("OLEDB");
srcCM.Name = "OLEDBSource";
srcCM.ConnectionString = "Data Source=mxpandreys\\sql2k5;Initial Catalog=Test;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";
Microsoft.SqlServer.Dts.Runtime.ConnectionManager destCM = package.Connections.Add("OLEDB");
destCM.Name = "OLEDBDestination";
destCM.ConnectionString = "Data Source=mxpandreys\\sql2k5;Initial Catalog=testCopy;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Auto Translate=False;";
//create the source component
IDTSComponentMetaData90 srcComponent = dataFlow.ComponentMetaDataCollection.New();
srcComponent.Name = "ComponetOLEDBSource";
srcComponent.ComponentClassID = "DTSAdapter.OLEDBSource.1";
// Get the design time instance of the component and initialize the component
CManagedComponentWrapper srcDesignTime = srcComponent.Instantiate();
srcDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (srcComponent.RuntimeConnectionCollection.Count > 0)
{
srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID = srcCM.ID;
srcComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[0]);
}
// Set the custom properties.
srcDesignTime.SetComponentProperty("AccessMode", 0);
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Accounts]");
// Reinitialize the metadata.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();
// Create the destination component.
IDTSComponentMetaData90 destComponent = dataFlow.ComponentMetaDataCollection.New();
destComponent.Name = "ComponetOLEDBDestination";
destComponent.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
CManagedComponentWrapper destDesignTime = destComponent.Instantiate();
destDesignTime.ProvideComponentProperties();
// Specify the connection manager.
if (destComponent.RuntimeConnectionCollection.Count > 0)
{
destComponent.RuntimeConnectionCollection[0].ConnectionManagerID = destCM.ID;
destComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(package.Connections[1]);
}
// Set the custom properties.
destDesignTime.SetComponentProperty("AccessMode", 0);
destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Accounts]");
destDesignTime.AcquireConnections(null);
destDesignTime.ReinitializeMetaData();
destDesignTime.ReleaseConnections();
// Create the path.
IDTSPath90 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0], destComponent.InputCollection[0]);
//map the columns
IDTSInput90 input = destComponent.InputCollection[0];
IDTSVirtualInput90 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn90 vColumn in vInput.VirtualInputColumnCollection)
{
IDTSInputColumn90 vCol = destDesignTime.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
destDesignTime.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}
//DataFlowEvent dataFlowEvent = new DataFlowEvent();
Application appSSIS = new Application();
appSSIS.SaveToXml("c:\\temp\\testPackage.dtsx", package, null);
/*
package.Validate(null, null, null, null);
package.Execute(null, null, null, null, null);
* */
//DataFlowEvent is derived from class DefaultEvent
}
catch (Exception e)
{
throw;
}
}
Sounds like the ID field is an identity column. You need to check the 'Keep Identity' checkbox in the OLE DB Destination editor.
Also, since you transfering data from one table to another on the same server, you could you use tsql, something like this...
set identitiy insert <tablename> on;
insert into TableB (a,b,c,d)
select
a,b,c,d
from
TableA
