sql server dts: Hi SSIS Guru's,

I'm trying to design a console app that uses the SSIS object model to
move data between 2 tables that have the exact same metadata
definitions. I pasted the code below:

When I run the code I get the following error:

"component "OLE DB Destination" (2)" failed validation and returned
validation s
tatus "VS_NEEDSNEWMETADATA".

Not really sure what I'm doing wrong - perhaps someone can see some
fatal problem with what I'm trying to accomplish here.

Thanks!

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using dtrw = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;


namespace TransferTable
{
class Program
{

static void Main(string[] args)
{

Package pkg = new Package();
MainPipe dataFlow =
((TaskHost)pkg.Executables.Add("DTS.Pipeline")).InnerObject as
MainPipe;

// add oledb connection
ConnectionManager srcCM = pkg.Connections.Add("OLEDB");
srcCM.Name = "OLEDB ConnectionManager";
srcCM.ConnectionString = "Data Source=localhost;User
ID=;Initial Catalog=SB03;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;";



// add oledb connection
ConnectionManager dstCM = pkg.Connections.Add("OLEDB");
dstCM.Name = "OLEDB ConnectionManager2";
dstCM.ConnectionString = "Data Source=localhost;User
ID=;Initial Catalog=SB03;Provider=SQLNCLI.1;Integrated
Security=SSPI;Auto Translate=False;";

// add a source component to the datafow
IDTSComponentMetaData90 srcComponent =
dataFlow.ComponentMetaDataCollection.New();
srcComponent.Name = "OLEDBSource";
srcComponent.ComponentClassID = "DTSAdapter.OleDbSource.1";

CManagedComponentWrapper srcComponentInstance =
srcComponent.Instantiate();

// add a source component to the datafow
IDTSComponentMetaData90 dstComponent =
dataFlow.ComponentMetaDataCollection.New();
dstComponent.Name = "OLEDBDestination";
dstComponent.ComponentClassID =
"DTSAdapter.OLEDBDestination.1";

CManagedComponentWrapper dstComponentInstance =
dstComponent.Instantiate();


// initialize the component
srcComponentInstance.ProvideComponentProperties();
dstComponentInstance.ProvideComponentProperties();


// specify the connection manager
if (srcComponent.RuntimeConnectionCollection.Count > 0)
{

srcComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
srcCM.ID;

srcComponent.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(pkg.Connections[0]);
}

// specify the connection manager
if (dstComponent.RuntimeConnectionCollection.Count > 0)
{

dstComponent.RuntimeConnectionCollection[0].ConnectionManagerID =
dstCM.ID;

dstComponent.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.ToConnectionManager90(pkg.Connections[1]);
}

// set the custom properties
srcComponentInstance.SetComponentProperty("CommandTimeout",
0);
srcComponentInstance.SetComponentProperty("OpenRowset",
"cendec_areas");

srcComponentInstance.SetComponentProperty("AlwaysUseDefaultCodePage",
true);
srcComponentInstance.SetComponentProperty("AccessMode", 0);

// reinitialize the metadata
srcComponentInstance.AcquireConnections(null);
srcComponentInstance.ReinitializeMetaData();
srcComponentInstance.ReleaseConnections();

// set the custom properties
dstComponentInstance.SetComponentProperty("CommandTimeout",
0);
dstComponentInstance.SetComponentProperty("OpenRowset",
"cendec_areas2");

dstComponentInstance.SetComponentProperty("AlwaysUseDefaultCodePage",
true);
dstComponentInstance.SetComponentProperty("AccessMode", 0);

// reinitialize the metadata
dstComponentInstance.AcquireConnections(null);
dstComponentInstance.ReinitializeMetaData();
dstComponentInstance.ReleaseConnections();


// create the path
IDTSPath90 path = dataFlow.PathCollection.New();

path.AttachPathAndPropagateNotifications(srcComponent.OutputCollection[0],
dstComponent.InputCollection[0]);

// Iterate through the inputs of the component.
foreach (IDTSInput90 input in dstComponent.InputCollection)
{
// Get the virtual input column collection for the
input.
IDTSVirtualInput90 vInput = input.GetVirtualInput();
// Iterate through the virtual column collection.
foreach (IDTSVirtualInputColumn90 vColumn in
vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the design time
instance of the component.
dstComponentInstance.SetUsageType(input.ID, vInput,
vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}

Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
app = new Microsoft.SqlServer.Dts.Runtime.Application();

pkgResults = pkg.Execute();

DTSExecResult result = pkg.Execute();

foreach (DtsError pkgerror in pkg.Errors)
{
string err = pkgerror.Description;
Console.WriteLine(err);
}
Console.WriteLine(pkgResults.ToString());


Console.ReadLine();

}
}
}

Hello jimdandy@shaw.ca,

So VS_NEEDSNEWMETADATA is returned in Validate() generally when the metadata
gets out of sync so maybe you have told the pipeline it looks a certain way
and then you change the underlying data structure.


Why do you need to build the pipeline?

Do you intend to rebuild this every time? Will this not be expensive?

Why can you not build a package to do this once and then use configurations
to change the parts that need changing?

What makes it so that we need to rebuild everytime?



Allan Mitchell
www.SQLDTS.com
www.SQLIS.com
www.Konesans.com

[quoted text, click to view]

Basically what I'm trying to accomplish here is to pass in 2 parameters
from the command line for the src and dst tables and programatically
script a ssis package to move the data.

This way I don't have to create a whole bunch of packages to move 100
tables from the source system into a pre-staging area of a
datawarehouse.

In DTS you could do this quite easily, but in SSIS it appears to be
really difficult.

Does MS recommend creating 100 packages if you want to move 100 tables?
What do you suggest for a best practice for this type of situation?

Thanks for your input!

Jim