SSIS: By coding Part 2

• Download SSISExampWithCondtionalSplit.zip - 12.4 KB

Introduction 

In the last article I talked about creating Source and Destination connection and passing all the data from source table to destination table using C# and SSIS library. You can find more information for it here

In this write-up, I will demonstrate data transfer between two tables on same server based on some condition (Will demonstrate use of Conditional Split using C#), creating package entirely through coding in step by step process.

Though it very easy to create SSIS package through GUI (you just need to set few properties and your package is ready to execute), here I am demonstrating coding way to create SSIS package.

You Going to Learn (Index)

You have already learned about creation of source and destination connection, DATA Flow Task in previous article. So I just give overview of them here. 

1. Create two table in Database (Source and Destination)
2. Initial Tasks
3. Create ConditionalSplit transformation, assign source output
4. Assign filter condition
5. Create destination data source and assign destination connection manager
6. Create Path between filtered output and destination
7. Input Map filtered output column to destination input column
8. Save package and execute actual code
9. Screenshots
10. Bonus: Get leftout data and insert in into different table!
11. Screenshots

In brief, we are going to create source connection, based on table data, we will filter records based on condition using Conditional Split transformation (i.e. we will get all the records where person age is less than 30) and the filtered output will be inserted into destination table.

Step by Step ;

1. Create two table in Database (Source and Destination)

 Create two dummy table by name SourceTable and DestinationTable1, with same fields like this:-

CREATE TABLE [dbo].[SourceTable](
    [ID] [int] NOT NULL,
    [Name] [varchar](20) NULL,
    [Age] [int] NULL
) ON [PRIMARY]
GO 
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[DestinationTable1](
    [ID] [int] NOT NULL,
    [Name] [varchar](20) NULL,
    [Age] [int] NULL 
) ON [PRIMARY] 

Also I have filled some dummy data into the source table

ID	Name	Age
1	Alok	30
2	Ashish	30
3	Jasdeep	30
4	Ritesh	35
5	Kamlesh	32
6	Hariharan	36

2. Initial Tasks

• Createte C# window form based project. Nothing fancy
• Add button on the form and include OnClick event handler, we will do all the programming in the event handler
• Now include supporting SSIS DotNet Assemblies into the project, for that right click on the References in Solution Explorer Browse to %Program Files%\Microsoft SQL Server\100\SDK\Assemblies Include following files
  • Microsoft.SQLServer.DTSPipelineWrap
  • Microsoft.SqlServer.DTSRuntimeWrap
  • Microsoft.SqlServer.ManagedDTS
• Close add assemblies dialog and save the solution.

Following code will create will create the package object

Package objPackage = new Package();
            objPackage.Name = "SSISExample";
            objPackage.PackageType = DTSPackageType.DTSDesigner100;
            objPackage.VersionBuild = 1;

Above, we have created SSIS Package object and given its name “SSISExample” and defined package type as DTSPackageType.DTSDesigner100, there are lot of other option too,Have a look here (here we meant to say we are creating Designer oriented package)  Before we move forward, its good to have over connection manager up and running, here I am creating two Oledb connections to same database, this connection will be used byOleDB Source and OleDB Destination.

var connectingString =
@"Data Source=localhost;Integrated Security=SSPI;Initial Catalog=TestDB;Provider=SQLNCLI10.1;Persist Security Info=True;"; 

ConnectionManager oleDBConnSrc = objPackage.Connections.Add("OLEDB"); 
oleDBConnSrc.ConnectionString = connectingString;
oleDBConnSrc.Name = "SourceConnection";
 
ConnectionManager oleDBDestination = objPackage.Connections.Add("OLEDB");
oleDBDestination.ConnectionString = connectingString; 
oleDBDestination.Name = "DestinationConnection";

Here I have provided connectingString contain the connection string of SQL Database I am connecting to. One thing you should take care, the user from which you are loggingTo Database should have write access right on it. oleDBConnSrc and oleDBDestination are two ConnectionManager object. Now we come to most important part of the package, the actual battleground where all the import/export is done. Though it backend name is Pipeline, how on designer screen it known by name “Data Flow Task”, all the coding of our source and destination will done inside this Data Flow Task, and it’s pretty simple to create.

//Create DataFlowTask in the package 
TaskHost dataFlowTaskHost = (TaskHost)objPackage.Executables.Add("SSIS.Pipeline.2"); 
dataFlowTaskHost.Name = @"SSISPipeline"; 
dataFlowTaskHost.FailPackageOnFailure = true; 
dataFlowTaskHost.FailParentOnFailure = true; 
dataFlowTaskHost.DelayValidation = false; 
dataFlowTaskHost.Description = @"Data Flow Task";

 //-----------Data Flow Inner component starts---------------- 
 MainPipe dataFlowTask = dataFlowTaskHost.InnerObject as MainPipe;

From our package object we will add Pipeline object using objPackage.Executables.Add() method and passing "SSIS.Pipeline.2" as object name to create,' and provide default properties to task object. Now we get MainPipe of created dataFlowTaskHost, and rest of the component is attached to this

MainPipe

(dataFlowTask).

// Create and configure an OLE DB source component. 
IDTSComponentMetaData100 sourceOleDB = dataFlowTask.ComponentMetaDataCollection.New();
sourceOleDB.ComponentClassID = "DTSAdapter.OLEDBSource.2"; 
CManagedComponentWrapper srcDesignTime = sourceOleDB.Instantiate(); 

// The ProvideComponentProperties method creates a default output. 
srcDesignTime.ProvideComponentProperties(); 
sourceOleDB.Name = "TestDB DATA Source";

Here, we create object in the dataFlowTask object, and assign

ComponentClassID
        = "DTSAdapter.OLEDBSource.2", 

to tell package that we are adding OLEDBSource adapter.After that we instantiate it design time component, and get’s it properties which we will assign subsequently

// Assign the connection manager. 
sourceOleDB.RuntimeConnectionCollection[0].ConnectionManagerID = oleDBConnSrc.ID;
 sourceOleDB.RuntimeConnectionCollection[0].ConnectionManager =
 DtsConvert.GetExtendedInterface(oleDBConnSrc);

Now assign Source DB adapter with source connection manager, above code do that bit, now we will set property for table to open in read mode

// Set the custom properties of the source.
srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[SourceTable]");

// Connect to the data source, and then update the metadata for the source.
srcDesignTime.AcquireConnections(null);
srcDesignTime.ReinitializeMetaData();
srcDesignTime.ReleaseConnections();

Here AccessMode=0 denote we are opening database in view mode and

OpenRowSet

property denote the table we want to open.Till now, all the instruction are blindly copied from previous article,From Step 3 we start with actual article.

3. Create ConditionalSplit transformation, assign source output

Now we already have source output column, now it time to create

ConditionalSplit

transformation.

// Add an ConditionalSplit to the data flow.
 IDTSComponentMetaData100 conditionalSplit = dataFlowTask.ComponentMetaDataCollection.New();
 conditionalSplit.Name = "conditionalSplit1";
 conditionalSplit.ComponentClassID = "DTSTransform.ConditionalSplit.2";

 CManagedComponentWrapper conditionalSplitDesignTime = conditionalSplit.Instantiate();
 conditionalSplitDesignTime.ProvideComponentProperties();
 conditionalSplit.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
 conditionalSplit.InputCollection[0].HasSideEffects = false;
 conditionalSplitDesignTime.AcquireConnections(null);
 conditionalSplitDesignTime.ReinitializeMetaData();
 conditionalSplitDesignTime.ReleaseConnections();

In above code, we are creating transformation of type “DTSTransform.ConditionalSplit.2”, which will instruct SSIS to create ConditionalSplit transformation and using

CManagedComponentWrapper

class we will instantiate its design property Now we will connect the path of Source output column with ConditionalSplit input column and setup the usage of each column.

// Create the path from Source to Split Column Transformation.
IDTSPath100 pathSourceconditionalSplit = dataFlowTask.PathCollection.New();
pathSourceconditionalSplit.AttachPathAndPropagateNotifications(sourceOleDB.OutputCollection[0],conditionalSplit.InputCollection[0]);

// Set Usagetype for Input column, so that they are available for coding
var vInput1 = conditionalSplit.InputCollection[0].GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vColumn in vInput1.VirtualInputColumnCollection)
{
    conditionalSplitDesignTime.SetUsageType(conditionalSplit.InputCollection[0].ID,
        vInput1, vColumn.LineageID, DTSUsageType.UT_READONLY);
}

4. Assign filter condition

Now we already have ConditionSplit object from step3, now we will create new output collection, so that we can assign out filter condition there. We will create output collection by name “ConditionalSplitFilerCondtion” and assign the inputcollection id to it, so that it can perform filter on incoming data

var newOutputCollection = conditionalSplit.OutputCollection.New();
newOutputCollection.Name = "ConditionalSplitFilerCondtion";
newOutputCollection.HasSideEffects = false;
newOutputCollection.ExclusionGroup = 1;
newOutputCollection.ExternalMetadataColumnCollection.IsUsed = false;
newOutputCollection.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
newOutputCollection.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
newOutputCollection.ErrorOrTruncationOperation = "Computation";
newOutputCollection.SynchronousInputID = conditionalSplit.InputCollection[0].ID;
             

Once we have output collection object, we will create custom property for “Expression”, “FriendlyExpression” and “EvalutionOrder” and assign our filter condition there ([Age] <31).

• Here Expression and FriendlyExpression are almost same,however we have assign value to both. Also, it contain the ID (column), we have assign ContainID=true
• For EvalutionOrder, we can have multiple conditions and each condition result in different output. So it’s like assigning priority.

IDTSCustomProperty100 myPropCS = newOutputCollection.CustomPropertyCollection.New();
myPropCS.ContainsID = true;
myPropCS.Name = "Expression";
myPropCS.Value = "[Age] < 31";
myPropCS.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;


myPropCS = newOutputCollection.CustomPropertyCollection.New();
myPropCS.ContainsID = true;
myPropCS.Name = "FriendlyExpression";
myPropCS.Value = "[Age] < 31";
myPropCS.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

myPropCS = newOutputCollection.CustomPropertyCollection.New();

myPropCS.Name = "EvaluationOrder";
myPropCS.Value = "0";
myPropCS.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

5. Create Destination Data source and assign Destination Connection Manager

Code is similar to that’s of Source Adapter, except we will pass

ComponentClassID

as "DTSAdapter.OleDbDestination", AccessMode = 3 and OpenRowSet Propertly contain the Destination Table.

IDTSComponentMetaData100 destinationOleDb = 
dataFlowTask.ComponentMetaDataCollection.New(); 
destinationOleDb.ComponentClassID = "DTSAdapter.OleDbDestination";

CManagedComponentWrapper destDesignTime = destinationOleDb.Instantiate(); 
destDesignTime.ProvideComponentProperties(); 

// Assign the connection manager. 
destinationOleDb.RuntimeConnectionCollection[0].ConnectionManagerID = oleDBDestination.ID; 
destinationOleDb.RuntimeConnectionCollection[0].ConnectionManager = 
DtsConvert.GetExtendedInterface(oleDBDestination); 
// Set the custom properties of the source.

destDesignTime.SetComponentProperty("AccessMode", 3); 
destDesignTime.SetComponentProperty("OpenRowset", 
"[dbo].[DestinationTable1]"); 
// Connect to the data source, and then update the metadata for the source. 
destDesignTime.AcquireConnections(null); 
destDesignTime.ReinitializeMetaData(); 
destDesignTime.ReleaseConnections();

6. Create Path between Filtered output and Destination

Now our source and destination adapter are ready. Also Filtered output column is ready, now we connect filtered output column with destination input column, so that destination become aware what comings its way. For that we will add path between them

    // Create the path from source to destination 
IDTSPath100 pathDestination = dataFlowTask.PathCollection.New(); 
pathDestination.AttachPathAndPropagateNotifications(conditionalSplit.OutputCollection["ConditionalSplitFilerCondtion"],
destinationOleDb.InputCollection[0]);

Here AttachPathAndPropagateNotifications method of initialize destination input from ConditionSplit filtered output, since we know the name of incoming collection we can used named output collection.

7. Map source output column to destination input column

// Get the destination's default input and virtual input. 
IDTSInput100 destinationinput = destinationOleDb.InputCollection[0]; 
IDTSVirtualInput100 vdestinationinput = destinationinput.GetVirtualInput(); 

// Iterate through the virtual input column collection. 
foreach (IDTSVirtualInputColumn100 vColumn in 
vdestinationinput.VirtualInputColumnCollection) 
{ 
  IDTSInputColumn100 vCol = destDesignTime.SetUsageType(destinationinput.ID, vdestinationinput, vColumn.LineageID, DTSUsageType.UT_READWRITE);
  
 // check if the column exist in the destination table 
  string cinputColumnName = vColumn.Name; 
  var columnExist = (from item in destinationinput.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>() 
                     where item.Name == cinputColumnName 
                     select item).Count();        
 // if yes map it 
 if (columnExist > 0) 
 destDesignTime.MapInputColumn(destinationinput.ID, vCol.ID, 
                               destinationinput.ExternalMetadataColumnCollection[vColumn.Name].ID); 
 }

Here we get destination input column, mark them available in design view, then search for matching column name is destination table, if we found match, we map the column using MapInputColumn method

8. Save package and Execute Package

SSIS file is actually XML file, if you rename the dtsx file to xml, you can view all the property set by us, we will save it, and view it in the Business Intelligence studio

Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application(); 
app.SaveToXml(string.Format(@"c:\workSamplePackage\SamplePackage{0}.dtsx", 
                             DateTime.Now.ToString("hhmmss")), 
                             objPackage, null);
objPackage.Execute(); 
objPackage.Dispose(); 
app = null;

9. ScreenShot

If we open the above created package file in Business Intelligence studio, it would look like this :-

You could clearly see the two Connection manager and ConditionalSplit, which we have added. Also source and Destination connection we have created.if you open conditional split it will look something like this:-

 now if you run this, its show something like this :-

It say 6 rows has been transferred from source database to ConditionalSplit and OnFiltering 3 rows is transferred from ConditionalSplit to Destination Table, here have look at the destination table

10.Bonus: Get leftout data and insert in into different table!

Since we already have output connection from the ConditionalSplit for leftout data, now we create another table DestinationTable2, where we put all the left out data.

CREATE TABLE [dbo].[DestinationTable2](
    [ID] [int] NOT NULL,
    [Name] [varchar](20) NULL,
    [Age] [int] NULL 
) ON [PRIMARY] 

In brief, we will create one more OleDBDestination and attach output connection from ConditionalSplit to its input collection. We will use same code for Mapping of input columns and saving the Package.

//-------------Create leftout  Destination Package
            IDTSComponentMetaData100 destinationOleDbLO =
                dataFlowTask.ComponentMetaDataCollection.New();
            destinationOleDbLO.ComponentClassID = "DTSAdapter.OleDbDestination";
            CManagedComponentWrapper destDesignTimeLO = destinationOleDbLO.Instantiate();
            destDesignTimeLO.ProvideComponentProperties();
            destinationOleDbLO.Name = "LeftOutData Destination";
            destinationOleDbLO.Description = "For Left out data";

  // Assign the connection manager.
  destinationOleDbLO.RuntimeConnectionCollection[0].ConnectionManagerID = oleDBDestination.ID;
  destinationOleDbLO.RuntimeConnectionCollection[0].ConnectionManager =
  DtsConvert.GetExtendedInterface(oleDBDestination);
  // Set the custom properties of the source.
  destDesignTimeLO.SetComponentProperty("AccessMode", 3);
  destDesignTimeLO.SetComponentProperty("OpenRowset", "[dbo].[DestinationTable2]");
  // Connect to the data source, and then update the metadata for the source.
  destDesignTimeLO.AcquireConnections(null);
  destDesignTimeLO.ReinitializeMetaData();
  destDesignTimeLO.ReleaseConnections();

Only thing different here is, that we have provided name of destination oledb, as we have already have one destination oledb connection active. Second important thing to note is that the name and description should be filled after we instantiate the ManagedComponentWrapper and ProvideComponentProperties method is already called; otherwise these changes would be reflected.

Here we have created the path between default Conditional Split and Destination. Since default occupy first position, so we can use 0 index to identify it.

// Create the path from source to destination
IDTSPath100 pathDestinationLO = dataFlowTask.PathCollection.New();
pathDestinationLO.AttachPathAndPropagateNotifications(conditionalSplit.OutputCollection[0],
            destinationOleDbLO.InputCollection[0]);

// Get the destinations default input and virtual input.
IDTSInput100 destinationinputLO = destinationOleDbLO.InputCollection[0];

IDTSVirtualInput100 vdestinationinputLO = destinationinputLO.GetVirtualInput();

// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn100 vColumn in
         vdestinationinputLO.VirtualInputColumnCollection)
{
  IDTSInputColumn100 vCol = destDesignTimeLO.SetUsageType(destinationinputLO.ID,
           vdestinationinputLO, vColumn.LineageID,
           DTSUsageType.UT_READWRITE);

string cinputColumnName = vColumn.Name;
   var columnExist =
  (from item in
  destinationinputLO.ExternalMetadataColumnCollection.Cast<IDTSExternalMetadataColumn100>() 
                     where item.Name == cinputColumnName
                     select item).Count();

if (columnExist > 0)
     destDesignTimeLO.MapInputColumn(destinationinputLO.ID, vCol.ID,
     destinationinputLO.ExternalMetadataColumnCollection[vColumn.Name].ID);
 }

you can use code mentioned in step 8 to save the package

11. ScreenShot

Now above created package file would look like this :-

You could clearly see the two Connection manager and ConditionalSplit, which we have added. Also source and two Destination connection we have created.  now if you run this, its show something like this :-

It say 6 rows has been transferred from source database to ConditionalSplit and OnFiltering 3 rows is transferred from ConditionalSplit to Destination Table and 3 rows are transffered to Leftout table, here have look at the destination table, i have truncated both DestinationTable1 and DestinationTable2 before executing the package

Point of Interest

Watch out for more article on series!