SSIS Object Model Programatically Script Dataflow Task
- From: jimdandy@xxxxxxx
- Date: 28 Feb 2006 14:20:29 -0800
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();
}
}
}
.
- Prev by Date: Re: header row in bcp
- Previous by thread: header row in bcp
- Index(es):
Relevant Pages
|
