SSIS Programming reference

Generating SSIS using meta data is a powerful technique when you want to move large number of table from source to target.  In this post I will highlight the programming references to create the SSIS dynamically.

• Microsoft.SqlServer.Dts.Runtime.Package is the highlevel object which hold all the objects of the package.  You can create New Package object to start building you object mode.
• Connections collection holds connections. 
  Example: PackageObj.Connections.Add("OLEDB");
  Refer MSDN for list of connection types
• You can change the custom property of the connection by accessing the Property collection of the connection
  Example: ConnectionObj.Properties["Format"].SetValue(csvFile, "Delimited");
• Executable: By accessing the Executable collection you can add DFT, SQL Task or any other task to Package.
  Example: Package.Executables.Add(“STOCK:SEQUENCE”);
  For list of moniker, refer the SQLIS blog
• When you are dealing with Data Flow Task (DFT), it is complex due to the fact DFT are COM and we have a CManagedComponentWrapper to access them.
• The hierarchy of object structure is
• Executable
• TaskHost
• MainPipe
• IDTSComponentMetaData100

Package p = new Package();
Executable e = p.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

• In DFT, you can add pipeline items by using ComponentMetaDataCollection of MainPipe object

IDTSComponentMetaData100 pipeLineItem = MainPipeObj.ComponentMetaDataCollection.New();
pipeLineItem.ComponentClassID = "DTSAdapter.OleDbSource.2";

• When you are using the Source object in PipeLine, make sure it get initiated using IDTSComponentMetaData100.Instantiate & CManagedComponentWrapper.ProvideComponentProperties methods.  These methods are explicit interface implementation so make sure necessary type cast done when you call the methods.

CManagedComponentWrapper InstanceSource = IDTSComponentMetaData100Obj.Instantiate();
InstanceSource.ProvideComponentProperties();

• For Source, Destination tasks make sure the connection are associated.

public static void SetConnection(
IDTSComponentMetaData100 obj, ConnectionManager con)
{
obj.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(con);
obj.RuntimeConnectionCollection[0].ConnectionManagerID = con.ID;
}

• After the connection is set, it needs to be refreshed to get the meta data

CManagedComponentWrapperObj.AcquireConnections(null);
CManagedComponentWrapperObj.ReinitializeMetaData();
CManagedComponentWrapperObj.ReleaseConnections();

• You can attach multiple item to pipeline. 
• To link the pipeline objects, you can use Attach Path method

IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], target.InputCollection[0]);

• To map the columns between pipeline items, you need to use the column collections

public void MapColumns(
IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType)
{
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;

if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in  vInput.VirtualInputColumnCollection)
{
  InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}

foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
                    }
                }
        }

• For connecting executable in Control Tasks, you can use PrecedenceConstraint class to associate the Task for sequential execution.

PrecedenceConstraint prePC = DFT.ParentContainer.PrecedenceConstraints.Add(preExec, postExec);