| title | Adding Data Flow Components Programmatically | |||
|---|---|---|---|---|
| description | Adding Data Flow Components Programmatically | |||
| author | chugugrace | |||
| ms.author | chugu | |||
| ms.date | 03/14/2017 | |||
| ms.service | sql | |||
| ms.subservice | integration-services | |||
| ms.topic | reference | |||
| helpviewer_keywords |
|
|||
| dev_langs |
|
|||
| ms.custom | sfi-ropc-nochange |
[!INCLUDEsqlserver-ssis]
When you build a data flow, you start by adding components. Then you configure those components and connect them together to establish the flow of data at run time. This section describes adding a component to the data flow task, creating the design-time instance of the component, and then configuring the component. For information about how to connect components, see Connecting Data Flow Components Programmatically.
Call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaDataCollection100.New%2A method of the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.MainPipeClass.ComponentMetaDataCollection%2A collection to create a new component and add it to the data flow task. This method returns the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 interface of the component. However, at this point, the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100 does not contain information specific to any one component. Set the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.ComponentClassID%2A property to identify the type of component. The data flow task uses the value of this property to create an instance of the component at run time.
The value specified in the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.ComponentClassID%2A property can be the CLSID, PROGID, or xref:Microsoft.SqlServer.Dts.Runtime.PipelineComponentInfo.CreationName%2A property of the component. The CLSID is normally displayed in the Properties window as the value of the component's xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.ComponentClassID%2A property. For information about obtaining this property and other properties of available components, see Discovering Data Flow Components Programmatically.
You cannot use the CLSID or PROGID to add one the managed data flow components to the data flow, because these values point to a wrapper and not to the component itself. Instead you can use the CreationName property or the AssemblyQualifiedName property as shown in the following sample.
If you intend to use the AssemblyQualifiedName property, then you must add a reference in your [!INCLUDEvsprvs] project to the assembly that contains the managed component. These assemblies are not listed on the .NET tab of the Add Reference dialog box. Normally you must browse to locate the assembly in the C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents folder.
The built-in managed data flow components include:
-
[!INCLUDEvstecado] Source
-
XML Source
-
DataReader Destination
-
[!INCLUDEssNoVersion] Compact Destination
-
Script Component
The following code sample shows both ways of adding a managed component to the data flow:
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace Microsoft.SqlServer.Dts.Samples
{
class Program
{
static void Main(string[] args)
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable e = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = (Microsoft.SqlServer.Dts.Runtime.TaskHost)e;
MainPipe dataFlowTask = (MainPipe)thMainPipe.InnerObject;
// The Application object will be used to obtain the CreationName
// of a PipelineComponentInfo from its PipelineComponentInfos collection.
Application app = new Application();
// Add a first ADO NET source to the data flow.
// The CreationName property requires an Application instance.
IDTSComponentMetaData100 component1 = dataFlowTask.ComponentMetaDataCollection.New();
component1.Name = "DataReader Source";
component1.ComponentClassID = app.PipelineComponentInfos["DataReader Source"].CreationName;
// Add a second ADO NET source to the data flow.
// The AssemblyQualifiedName property requires a reference to the assembly.
IDTSComponentMetaData100 component2 = dataFlowTask.ComponentMetaDataCollection.New();
component2.Name = "DataReader Source";
component2.ComponentClassID = typeof(Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter).AssemblyQualifiedName;
}
}
}
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Module Module1
Sub Main()
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
New Microsoft.SqlServer.Dts.Runtime.Package()
Dim e As Executable = package.Executables.Add("STOCK:PipelineTask")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' The Application object will be used to obtain the CreationName
' of a PipelineComponentInfo from its PipelineComponentInfos collection.
Dim app As New Application()
' Add a first ADO NET source to the data flow.
' The CreationName property requires an Application instance.
Dim component1 As IDTSComponentMetaData100 = _
dataFlowTask.ComponentMetaDataCollection.New()
component1.Name = "DataReader Source"
component1.ComponentClassID = app.PipelineComponentInfos("DataReader Source").CreationName
' Add a second ADO NET source to the data flow.
' The AssemblyQualifiedName property requires a reference to the assembly.
Dim component2 As IDTSComponentMetaData100 = _
dataFlowTask.ComponentMetaDataCollection.New()
component2.Name = "DataReader Source"
component2.ComponentClassID = _
GetType(Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter).AssemblyQualifiedName
End Sub
End Module Call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.Instantiate%2A method to create the design time instance of the component identified by the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.ComponentClassID%2A property. This method returns the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapper object, which is the managed wrapper for the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSDesigntimeComponent100 interface.
Whenever possible, you should modify a component by using the methods of the design-time instance instead of by modifying the component metadata directly. Although there are items in the metadata that you must set directly, such as connections, generally it is inadvisable to modify the metadata directly because you bypass the ability of the component to monitor and validate changes.
Some components, such as the OLE DB Source component, require a connection to external data and use an existing xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager object in the package for this purpose. The xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSRuntimeConnectionCollection100.Count%2A property of the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.RuntimeConnectionCollection%2A collection indicates the number of run-time xref:Microsoft.SqlServer.Dts.Runtime.ConnectionManager objects required by the component. If the count is greater than zero, the component requires a connection. Assign a connection manager from the package to the component by specifying the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSRuntimeConnection100.ConnectionManager%2A and xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSRuntimeConnection100.Name%2A properties of the first connection in the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.RuntimeConnectionCollection%2A. Note that the name of the connection manager in the run-time connection collection must match the name of the connection managerreferenced from the package.
After creating the design-time instance of the component, call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties%2A method. This method is similar to a constructor because it initializes a newly created component by creating its custom properties and its input and output objects. Do not call xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties%2A more than one time on a component, because the component may reset itself and lose any modifications previously made to its metadata.
The xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponentMetaData100.CustomPropertyCollection%2A of the component contains a collection of xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCustomProperty100 objects specific to the component. Unlike other programming models, where the properties of an object are always visible on the object, components only populate their custom property collections when you call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ProvideComponentProperties%2A method. After you call method, use the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetComponentProperty%2A method of the design-time instance of the component to assign values to its custom properties. This method accepts a name/value pair that identifies the custom property and provides its new value.
After you add a component to the task and configure it, initialize the collection of columns in the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSOutput100 of the object. This step is especially relevant for source components, but may not initialize the columns for transformation and destination components because they generally depend on the columns that they receive from upstream components.
Call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData%2A method to initialize the columns in the outputs of a source component. Because components do not automatically connect to external data sources, call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.AcquireConnections%2A method before calling xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData%2A to provide the component access to its external data source and the ability to populate its column metadata. Finally, call the xref:Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReleaseConnections%2A method to release the connection.
After adding and configuring the component, the next step is to create paths between components, which is discussed in the topic, Creating a Path Between Two Components.
The following code sample adds the OLE DB Source component to a data flow task, creates a design-time instance of the component, and configures the component's properties. This example requires an additional reference to the assembly Microsoft.SqlServer.DTSRuntimeWrap.
using System;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
namespace Microsoft.SqlServer.Dts.Samples
{
class Program
{
static void Main(string[] args)
{
Runtime.Package package = new Runtime.Package();
Executable e = package.Executables.Add("STOCK:PipelineTask");
Runtime.TaskHost thMainPipe = e as Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
// Add an OLEDB connection manager to the package.
ConnectionManager cm = package.Connections.Add("OLEDB");
cm.Name = "OLEDB ConnectionManager";
cm.ConnectionString = "Data Source=(local);" +
"Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" +
"Integrated Security=SSPI;"
// Add an OLE DB source to the data flow.
IDTSComponentMetaData100 component =
dataFlowTask.ComponentMetaDataCollection.New();
component.Name = "OLEDBSource";
component.ComponentClassID = "DTSAdapter.OleDbSource.1";
// You can also use the CLSID of the component instead of the PROGID.
//component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
// Get the design time instance of the component.
CManagedComponentWrapper instance = component.Instantiate();
// Initialize the component
instance.ProvideComponentProperties();
// Specify the connection manager.
if (component.RuntimeConnectionCollection.Count > 0)
{
component.RuntimeConnectionCollection[0].ConnectionManager =
DtsConvert.GetExtendedInterface(package.Connections[0]);
component.RuntimeConnectionCollection[0].ConnectionManagerID =
package.Connections[0].ID; }
// Set the custom properties.
instance.SetComponentProperty("AccessMode", 2);
instance.SetComponentProperty("SqlCommand",
"Select * from Production.Product");
// Reinitialize the metadata.
instance.AcquireConnections(null);
instance.ReinitializeMetaData();
instance.ReleaseConnections();
// Add other components to the data flow and connect them.
}
}
} Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.SqlServer.Dts.Pipeline
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Module Module1
Sub Main()
Dim package As Microsoft.SqlServer.Dts.Runtime.Package = _
New Microsoft.SqlServer.Dts.Runtime.Package()
Dim e As Executable = package.Executables.Add("STOCK:PipelineTask")
Dim thMainPipe As Microsoft.SqlServer.Dts.Runtime.TaskHost = _
CType(e, Microsoft.SqlServer.Dts.Runtime.TaskHost)
Dim dataFlowTask As MainPipe = CType(thMainPipe.InnerObject, MainPipe)
' Add an OLEDB connection manager to the package.
Dim cm As ConnectionManager = package.Connections.Add("OLEDB")
cm.Name = "OLEDB ConnectionManager"
cm.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=AdventureWorks;Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;"
' Add an OLE DB source to the data flow.
Dim component As IDTSComponentMetaData100 = _
dataFlowTask.ComponentMetaDataCollection.New()
component.Name = "OLEDBSource"
component.ComponentClassID = "DTSAdapter.OleDbSource.1"
' You can also use the CLSID of the component instead of the PROGID.
'component.ComponentClassID = "{2C0A8BE5-1EDC-4353-A0EF-B778599C65A0}";
' Get the design time instance of the component.
Dim instance As CManagedComponentWrapper = component.Instantiate()
' Initialize the component.
instance.ProvideComponentProperties()
' Specify the connection manager.
If component.RuntimeConnectionCollection.Count > 0 Then
component.RuntimeConnectionCollection(0).ConnectionManager = _
DtsConvert.GetExtendedInterface(package.Connections(0))
component.RuntimeConnectionCollection(0).ConnectionManagerID = _
package.Connections(0).ID
End If
' Set the custom properties.
instance.SetComponentProperty("AccessMode", 2)
instance.SetComponentProperty("SqlCommand", _
"Select * from Production.Product")
' Reinitialize the metadata.
instance.AcquireConnections(vbNull)
instance.ReinitializeMetaData()
instance.ReleaseConnections()
' Add other components to the data flow and connect them.
End Sub
End Module Blog entry, EzAPI - Updated for SQL Server 2012, on blogs.msdn.com.