Programming Lookup Transformation

Home > SSIS, SSIS programming > Programming Lookup Transformation

Programming Lookup Transformation

May 19th, 2009 Arun Mallick Leave a comment Go to comments

Lookup Transformation is used to lookup data by joining data in input columns with columns of a reference table. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL statement. The lookup is used to access additional information in a related table that is based on values in common columns.

For Example:
Let us have one employee table and one transaction table. The employee table contains information about all the employees. And the transaction table keeps record of the type of transaction done by any employee.

Details about Employee_Dim table:

Column_name	Type
employee_Sl_No	int
Department	varchar
emp_name	varchar
emp_id	int
emp_DOB	datetime

Data:

employee_Sl_No	Department	emp_name	emp_id	emp_DOB
1	sales	Arun	101	16-Mar-84
2	sales	Akash	102	31-Jan-82
3	marketing	Ram	103	3-Mar-86
4	marketing	Rasi	104	25-Apr-86
5	manufacture	viswa	106	22-Apr-82
6	finance	Kishore	107	26-Aug-80
7	sales	jaswant	105	22-Apr-80

Details about Transaction_T table:

Column_name	Type
Emp_ID	int
Transaction_ID	int
Transaction_Type	varchar

Data:

Emp_ID	Transaction_ID	Transaction_Type
101	1	Payment
102	2	Sale
103	3	Debit
104	4	Credit
105	5	Void

Our requirement is to get all the information about the employees with their transactions. Our resultant set would look like this.

Emp_ID	Transaction_ID	Transaction_Type	employee_Sl_No	Department	emp_name	emp_DOB
101	1	Payment	1	sales	Arun	16-Mar-84
102	2	Sale	2	sales	Akash	31-Jan-82
103	3	Debit	3	marketing	Ram	3-Mar-86
104	4	Credit	4	marketing	Rasi	25-Apr-86
105	5	Void	7	sales	jaswant	22-Apr-80

The dataflow component to achieve the above logic with Lookup transformation is:

Below is given how to set the joining columns in the lookup transformation editor.

Note: To avoid error messages in SSIS, be sure to have a reference table containing all the matching values for lookup. Otherwise, try to redirect the unmatched rows in some other location.

Now let’s try the same in Integration Service programming. The coding can be done in C# .net or VB .net. First the package will be created (empty one). Then in the Control Flow, the package tasks (i.e. Execute SQL task for logging and data flow task for transformation), precedence constraints and package variables will be created. And inside the data flow task, the various components like data source, data destination, and lookup transformation will be created.

Note: For detail about the other components of the package, please refer to my earlier post named “SSIS Programming Basic”

The creation name for Lookup transformation is “DTSTransform.Lookup.1” and the component class ID is “{0FB4AABB-C027-4440-809A-1198049BF117}“. (For SQL Server 2005)

The code below shows the above package programmatically and the code itself is self-descriptive. The code for SQL server 2005 and SQL Server 2008 are a bit different; both the approaches are given below.

Add the below References before developing the code.

Microsoft.SqlServer.Dts.Design
Microsoft.SqlServer.DTSPipelineWrap
Microsoft.SQLServer.DTSRuntimeWrap
Microsoft.SQLServer.ManagedDTS

Programming Integration Service in SQL Server 2005

C# .Net Expand

[ Copy to Clipboard ] | [ View Source ]

 
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
 
 
//The below code will create a SSIS package with Look Up TRANSFORMATION for SQL Server 2005
 
namespace CreatePackageSample
{
    class Program
    {
        static void Main(string[] args)
        {
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package (Look Up)";
            package.PackageType = DTSPackageType.DTSDesigner90;
            package.VersionBuild = 1;
 
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("DTS.Pipeline.1");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            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;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData90 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.1";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Transaction_T (OLTP)";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_T]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
            //Add an Lookup Transformation
            IDTSComponentMetaData90 lookupComponent = dataFlowTask.ComponentMetaDataCollection.New();
            lookupComponent.ComponentClassID = "DTSTransform.Lookup.1";
            lookupComponent.Description = "Looks up values in a reference dataset by using exact matching.";
            lookupComponent.Name = "Lookup";
            CManagedComponentWrapper lookupDesignTime = lookupComponent.Instantiate();
            lookupDesignTime.ProvideComponentProperties();
 
            // Create the path from source to Lookup Transformation.
            // It also helps in mapping input columns in lookup transformation
            IDTSPath90 pathSource_Lookup = dataFlowTask.PathCollection.New();
            pathSource_Lookup.AttachPathAndPropagateNotifications(source.OutputCollection[0], lookupComponent.InputCollection[0]);
 
            // Assign the connection manager.
            lookupComponent.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            lookupComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr);
            // Set the custom properties.
            // For CacheType: Full = 0, Partial = 1, None = 2
            lookupDesignTime.SetComponentProperty("CacheType",0);
            lookupDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[Employee_Dim]");
 
            lookupDesignTime.AcquireConnections(null);
            lookupDesignTime.ReinitializeMetaData();
            lookupDesignTime.ReleaseConnections();
 
 
            // Mark the columns we are joining on
            //Input: Contains the collection of columns that represents the data provided to a component 
            IDTSInput90 lookupInput = lookupComponent.InputCollection[0];
            //Virtual Input: Represents the columns available to a component from the upstream component.
            IDTSVirtualInput90 lookupVirtualInput = lookupInput.GetVirtualInput();
 
            //Here the lookup will be done by joining [Transaction_Info].Emp_ID column with [Employee_Dim].emp_id column
            //SetUsageType: used to select the columns from the upstream component in the data flow that are used by the component. 
            IDTSInputColumn90 lookupInputColumn = lookupDesignTime.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection["Emp_ID"].LineageID, DTSUsageType.UT_READONLY);
            //JoinToReferenceColumn = The name of the column in the reference table to which a source column joins.
            //SetInputColumnProperty = Sets the custom property of the input column "Emp_ID" and the custom property is "JoinToReferenceColumn" with value "emp_id"
            lookupDesignTime.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "emp_id");
 
            //Note: The above code can be iterated in a loop for more than one joining columns.
            //To replace the existing input column with the reference column, use "CopyFromReferenceColumn" property in the above code.
            //For replacement of existing column, the input column should be set as DTSUsageType.UT_READWRITE
 
 
            IDTSOutput90 lookupOutput = lookupComponent.OutputCollection[0];
            // insert columns from refrence table
            IDTSOutputColumn90 lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "employee_Sl_No", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "employee_Sl_No");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "Department", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "Department");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_DOB", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_DOB");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_name", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_name");
 
 
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData90 destinationTrInfo = dataFlowTask.ComponentMetaDataCollection.New();
            destinationTrInfo.ComponentClassID = "DTSAdapter.OLEDBDestination.1";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destinationTrInfo.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destinationTrInfo.Name = "Transaciton_Info (OLAP)";
            // Assign the connection manager.
            destinationTrInfo.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationTrInfo.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_Info]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
 
            // Create the path from Condtional Split transformation to destination.
            IDTSPath90 pathLookup_DestSales = dataFlowTask.PathCollection.New();
            pathLookup_DestSales.Name = "Sales Dept";
            pathLookup_DestSales.AttachPathAndPropagateNotifications(lookupComponent.OutputCollection[0], destinationTrInfo.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput90 inputTrInfo = destinationTrInfo.InputCollection[0];
            IDTSVirtualInput90 vInputTrInfo = inputTrInfo.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn90 vColumn in vInputTrInfo.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                // to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   inputTrInfo.ID, vInputTrInfo, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
            //map external metadata to the inputcolumn
             foreach (IDTSInputColumn90 inputColumn in inputTrInfo.InputColumnCollection)
            {
 
                IDTSExternalMetadataColumn90 exMetaColumn = (IDTSExternalMetadataColumn90)inputTrInfo.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
 
'The below code will create a SSIS package with Look Up TRANSFORMATION for SQL Server 2005
 
Module Module1
    Sub Main(ByVal args As String())
        'To Create a package named [Sample Package]
        Dim package As New Package()
        package.Name = "Sample Package (LookUp)"
        package.PackageType = DTSPackageType.DTSDesigner90
        package.VersionBuild = 1
 
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("DTS.Pipeline.1"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.1"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Transaction_T (OLTP)"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_T]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
        'Add an Lookup Transformation
        Dim lookupComponent As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        lookupComponent.ComponentClassID = "DTSTransform.Lookup.1"
        lookupComponent.Description = "Looks up values in a reference dataset by using exact matching."
        lookupComponent.Name = "Lookup"
        Dim lookupDesignTime As CManagedComponentWrapper = lookupComponent.Instantiate()
        lookupDesignTime.ProvideComponentProperties()
 
        ' Create the path from source to Lookup Transformation.
        ' It also helps in mapping input columns in lookup transformation
        Dim pathSource_Lookup As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathSource_Lookup.AttachPathAndPropagateNotifications(source.OutputCollection(0), lookupComponent.InputCollection(0))
 
        ' Assign the connection manager.
        lookupComponent.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        lookupComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(SconMgr)
        ' Set the custom properties.
        ' For CacheType: Full = 0, Partial = 1, None = 2
        lookupDesignTime.SetComponentProperty("CacheType", 0)
        lookupDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[Employee_Dim]")
 
        lookupDesignTime.AcquireConnections(Nothing)
        lookupDesignTime.ReinitializeMetaData()
        lookupDesignTime.ReleaseConnections()
 
 
        ' Mark the columns we are joining on
        'Input: Contains the collection of columns that represents the data provided to a component 
        Dim lookupInput As IDTSInput90 = lookupComponent.InputCollection(0)
        'Virtual Input: Represents the columns available to a component from the upstream component.
        Dim lookupVirtualInput As IDTSVirtualInput90 = lookupInput.GetVirtualInput()
 
        'Here the lookup will be done by joining [Transaction_Info].Emp_ID column with [Employee_Dim].emp_id column
        'SetUsageType: used to select the columns from the upstream component in the data flow that are used by the component. 
        Dim lookupInputColumn As IDTSInputColumn90 = lookupDesignTime.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection("Emp_ID").LineageID, DTSUsageType.UT_READONLY)
        'JoinToReferenceColumn = The name of the column in the reference table to which a source column joins.
        'SetInputColumnProperty = Sets the custom property of the input column "Emp_ID" and the custom property is "JoinToReferenceColumn" with value "emp_id"
        lookupDesignTime.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "emp_id")
 
        'Note: The above code can be iterated in a loop for more than one joining columns.
        'To replace the existing input column with the reference column, use "CopyFromReferenceColumn" property in the above code.
        'For replacement of existing column, the input column should be set as DTSUsageType.UT_READWRITE
 
 
        Dim lookupOutput As IDTSOutput90 = lookupComponent.OutputCollection(0)
        ' insert columns from refrence table
        Dim lookupOutputColumn As IDTSOutputColumn90 = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "employee_Sl_No", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "employee_Sl_No")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "Department", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "Department")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_DOB", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_DOB")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_name", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_name")
 
 
 
        ' Create and configure an OLE DB destination component.
        Dim destinationTrInfo As IDTSComponentMetaData90 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationTrInfo.ComponentClassID = "DTSAdapter.OLEDBDestination.1"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destinationTrInfo.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destinationTrInfo.Name = "Transaciton_Info (OLAP)"
        ' Assign the connection manager.
        destinationTrInfo.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationTrInfo.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.ToConnectionManager90(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_Info]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
 
        ' Create the path from Condtional Split transformation to destination.
        Dim pathLookup_DestSales As IDTSPath90 = dataFlowTask.PathCollection.[New]()
        pathLookup_DestSales.Name = "Sales Dept"
        pathLookup_DestSales.AttachPathAndPropagateNotifications(lookupComponent.OutputCollection(0), destinationTrInfo.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim inputTrInfo As IDTSInput90 = destinationTrInfo.InputCollection(0)
        Dim vInputTrInfo As IDTSVirtualInput90 = inputTrInfo.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn90 In vInputTrInfo.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(inputTrInfo.ID, vInputTrInfo, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn90 In inputTrInfo.InputColumnCollection
 
            Dim exMetaColumn As IDTSExternalMetadataColumn90 = DirectCast(inputTrInfo.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn90)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackageSamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

Programming Integration Service in SQL Server 2008

C# .Net Expand

[ Copy to Clipboard ] | [ View Source ]

 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Data.SqlClient;
using System.Xml;
 
 
//The below code will create a SSIS package with Look Up TRANSFORMATION for SQL Server 2008
namespace SamplePackageCSharp2008
{
    class Program
    {
        static void Main(string[] args)
        {
            //To Create a package named [Sample Package]
            Package package = new Package();
            package.Name = "Sample Package (Look Up)";
            package.PackageType = DTSPackageType.DTSDesigner100;
            package.VersionBuild = 1;
 
 
            //To add Connection Manager to the package
            //For source database (OLTP)
            ConnectionManager OLTP = package.Connections.Add("OLEDB");
            OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.2;Integrated Security=SSPI;Auto Translate=False;";
            OLTP.Name = "LocalHost.OLTP";
            //For destination database (OLAP)
            ConnectionManager OLAP = package.Connections.Add("OLEDB");
            OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.2;Integrated Security=SSPI;Auto Translate=False;";
            OLAP.Name = "LocalHost.OLAP";
 
 
            //To add package variables
            Variable TransactionID = package.Variables.Add("TransactionID", false, "User", 0);
            TransactionID.Name = @"TransactionID";
 
            Variable RowCountVar = package.Variables.Add("RowCountVar", false, "User", 0);
            RowCountVar.Name = @"RowCountVar";
 
            //To add Package Start Indicator (Execute Sql task )
            TaskHost PackageStartIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageStartIndicatorTask.Name = @"Package Start Indicator";
            PackageStartIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageStartIndicatorTaskdoc = new XmlDocument();
            PackageStartIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageStartIndicatorTask.InnerObject).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, null);
 
            //To add Load Employee Dim to the package [Data Flow Task]
            TaskHost dataFlowTaskHost = (TaskHost)package.Executables.Add("SSIS.Pipeline.2");
            dataFlowTaskHost.Name = @"Load Employee Dim";
            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;
            // Source OLE DB connection manager to the package.
            ConnectionManager SconMgr = package.Connections["LocalHost.OLTP"];
            // Destination OLE DB connection manager to the package.
            ConnectionManager DconMgr = package.Connections["LocalHost.OLAP"];
 
            // Create and configure an OLE DB source component.  
            IDTSComponentMetaData100 source = dataFlowTask.ComponentMetaDataCollection.New();
            source.ComponentClassID = "DTSAdapter.OLEDBSource.2";
 
            // Create the design-time instance of the source.
            CManagedComponentWrapper srcDesignTime = source.Instantiate();
            // The ProvideComponentProperties method creates a default output.
            srcDesignTime.ProvideComponentProperties();
            source.Name = "Transaction_T (OLTP)";
 
            // Assign the connection manager.
            source.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr);
            // Set the custom properties of the source.
            srcDesignTime.SetComponentProperty("AccessMode", 0); // Mode 0 : OpenRowset / Table - View
            srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_T]");
            // Connect to the data source, and then update the metadata for the source.
            srcDesignTime.AcquireConnections(null);
            srcDesignTime.ReinitializeMetaData();
            srcDesignTime.ReleaseConnections();
 
            //Add an Lookup Transformation
            IDTSComponentMetaData100 lookupComponent = dataFlowTask.ComponentMetaDataCollection.New();
            lookupComponent.ComponentClassID = "DTSTransform.Lookup.2";
            lookupComponent.Description = "Looks up values in a reference dataset by using exact matching.";
            lookupComponent.Name = "Lookup";
            CManagedComponentWrapper lookupDesignTime = lookupComponent.Instantiate();
            lookupDesignTime.ProvideComponentProperties();
 
            // Create the path from source to Lookup Transformation.
            // It also helps in mapping input columns in lookup transformation
            IDTSPath100 pathSource_Lookup = dataFlowTask.PathCollection.New();
            pathSource_Lookup.AttachPathAndPropagateNotifications(source.OutputCollection[0], lookupComponent.InputCollection[0]);
 
            // Assign the connection manager.
            lookupComponent.RuntimeConnectionCollection[0].ConnectionManagerID = SconMgr.ID;
            lookupComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr);
            // Set the custom properties.
            // For CacheType: Full = 0, Partial = 1, None = 2
            lookupDesignTime.SetComponentProperty("CacheType", 0);
            lookupDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[Employee_Dim]");
 
            lookupDesignTime.AcquireConnections(null);
            lookupDesignTime.ReinitializeMetaData();
            lookupDesignTime.ReleaseConnections();
 
 
            // Mark the columns we are joining on
            //Input: Contains the collection of columns that represents the data provided to a component 
            IDTSInput100 lookupInput = lookupComponent.InputCollection[0];
            //Virtual Input: Represents the columns available to a component from the upstream component.
            IDTSVirtualInput100 lookupVirtualInput = lookupInput.GetVirtualInput();
 
            //Here the lookup will be done by joining [Transaction_Info].Emp_ID column with [Employee_Dim].emp_id column
            //SetUsageType: used to select the columns from the upstream component in the data flow that are used by the component. 
            IDTSInputColumn100 lookupInputColumn = lookupDesignTime.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection["Emp_ID"].LineageID, DTSUsageType.UT_READONLY);
            //JoinToReferenceColumn = The name of the column in the reference table to which a source column joins.
            //SetInputColumnProperty = Sets the custom property of the input column "Emp_ID" and the custom property is "JoinToReferenceColumn" with value "emp_id"
            lookupDesignTime.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "emp_id");
 
            //Note: The above code can be iterated in a loop for more than one joining columns.
            //To replace the existing input column with the reference column, use "CopyFromReferenceColumn" property in the above code.
            //For replacement of existing column, the input column should be set as DTSUsageType.UT_READWRITE
 
 
            IDTSOutput100 lookupOutput = lookupComponent.OutputCollection[0];
            // insert columns from refrence table
            IDTSOutputColumn100 lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "employee_Sl_No", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "employee_Sl_No");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "Department", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "Department");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_DOB", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_DOB");
            lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_name", "");
            lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_name");
 
 
 
            // Create and configure an OLE DB destination component.
            IDTSComponentMetaData100 destinationTrInfo = dataFlowTask.ComponentMetaDataCollection.New();
            destinationTrInfo.ComponentClassID = "DTSAdapter.OLEDBDestination.2";
            // Create the design-time instance of the destination.
            CManagedComponentWrapper destDesignTime = destinationTrInfo.Instantiate();
            // The ProvideComponentProperties method creates a default input.
            destDesignTime.ProvideComponentProperties();
            destinationTrInfo.Name = "Transaciton_Info (OLAP)";
            // Assign the connection manager.
            destinationTrInfo.RuntimeConnectionCollection[0].ConnectionManagerID = DconMgr.ID;
            destinationTrInfo.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr);
            // Set the custom properties.
            destDesignTime.SetComponentProperty("AccessMode", 3); // Mode 3 : OpenRowset Using FastLoad  / Table - View fast load
            destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_Info]");
            destDesignTime.AcquireConnections(null);
            destDesignTime.ReinitializeMetaData();
            destDesignTime.ReleaseConnections();
 
 
            // Create the path from Condtional Split transformation to destination.
            IDTSPath100 pathLookup_DestSales = dataFlowTask.PathCollection.New();
            pathLookup_DestSales.Name = "Sales Dept";
            pathLookup_DestSales.AttachPathAndPropagateNotifications(lookupComponent.OutputCollection[0], destinationTrInfo.InputCollection[0]);
 
 
            // Get the destination's default input and virtual input.
            IDTSInput100 inputTrInfo = destinationTrInfo.InputCollection[0];
            IDTSVirtualInput100 vInputTrInfo = inputTrInfo.GetVirtualInput();
            // Iterate through the virtual input column collection.
            foreach (IDTSVirtualInputColumn100 vColumn in vInputTrInfo.VirtualInputColumnCollection)
            {
                // Call the SetUsageType method of the destination
                // to add each available virtual input column as an input column.
                destDesignTime.SetUsageType(
                   inputTrInfo.ID, vInputTrInfo, vColumn.LineageID, DTSUsageType.UT_READONLY);
            }
            //map external metadata to the inputcolumn
            foreach (IDTSInputColumn100 inputColumn in inputTrInfo.InputColumnCollection)
            {
 
                IDTSExternalMetadataColumn100 exMetaColumn = (IDTSExternalMetadataColumn100)inputTrInfo.ExternalMetadataColumnCollection[inputColumn.Name];
                inputColumn.ExternalMetadataColumnID = exMetaColumn.ID;
            }
 
 
            //-----------Data Flow Inner component ends-------------------
 
            // Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
            PrecedenceConstraint PackageStartIndicatorTask_dataFlowTaskHost = package.PrecedenceConstraints.Add(package.Executables[@"Package Start Indicator"], package.Executables[@"Load Employee Dim"]);
            PackageStartIndicatorTask_dataFlowTaskHost.Name = @"Package Start Indicator" + @"Load Employee Dim";
 
 
            //To add Package End Indicator (Execute Sql task )
            TaskHost PackageEndIndicatorTask = (TaskHost)package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
            PackageEndIndicatorTask.Name = @"Package End Indicator";
            PackageEndIndicatorTask.Description = @"Execute SQL Task Description";
            XmlDocument PackageEndIndicatorTaskdoc = new XmlDocument();
            PackageEndIndicatorTaskdoc.LoadXml(@"<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>");
            ((IDTSComponentPersist)PackageEndIndicatorTask.InnerObject).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, null);
 
            // Precedence constraints from "Load Employee Dim" to "Package End Indicator"
            PrecedenceConstraint dataFlowTaskHost_PackageEndIndicatorTask = package.PrecedenceConstraints.Add(package.Executables[@"Load Employee Dim"], package.Executables[@"Package End Indicator"]);
            dataFlowTaskHost_PackageEndIndicatorTask.Name = @"Load Employee Dim" + @"Package End Indicator";
 
            //Saving the package
            Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
            app.SaveToXml(@"I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, null);
        }
    }
}

VB .Net Expand

[ Copy to Clipboard ] | [ View Source ]

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports System.Data.SqlClient
Imports System.Xml
 
 
'The below code will create a SSIS package with Look Up TRANSFORMATION for SQL Server 2008
Module Module1
 
    Sub Main(ByVal args As String())
        'To Create a package named [Sample Package]
        Dim package As New Package()
        package.Name = "Sample Package (Look Up)"
        package.PackageType = DTSPackageType.DTSDesigner100
        package.VersionBuild = 1
 
 
        'To add Connection Manager to the package
        'For source database (OLTP)
        Dim OLTP As ConnectionManager = package.Connections.Add("OLEDB")
        OLTP.ConnectionString = "Data Source=.;Initial Catalog=OLTP;Provider=SQLNCLI.2;Integrated Security=SSPI;Auto Translate=False;"
        OLTP.Name = "LocalHost.OLTP"
        'For destination database (OLAP)
        Dim OLAP As ConnectionManager = package.Connections.Add("OLEDB")
        OLAP.ConnectionString = "Data Source=.;Initial Catalog=OLAP;Provider=SQLNCLI.2;Integrated Security=SSPI;Auto Translate=False;"
        OLAP.Name = "LocalHost.OLAP"
 
 
        'To add package variables
        Dim TransactionID As Variable = package.Variables.Add("TransactionID", False, "User", 0)
        TransactionID.Name = "TransactionID"
 
        Dim RowCountVar As Variable = package.Variables.Add("RowCountVar", False, "User", 0)
        RowCountVar.Name = "RowCountVar"
 
        'To add Package Start Indicator (Execute Sql task )
        Dim PackageStartIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageStartIndicatorTask.Name = "Package Start Indicator"
        PackageStartIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageStartIndicatorTaskdoc As New XmlDocument()
        PackageStartIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.InitTransaction"" SQLTask:ResultType=""ResultSetType_SingleRow"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ResultBinding SQLTask:ResultName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageStartIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageStartIndicatorTaskdoc.DocumentElement, Nothing)
 
        'To add Load Employee Dim to the package [Data Flow Task]
        Dim dataFlowTaskHost As TaskHost = DirectCast(package.Executables.Add("SSIS.Pipeline.2"), TaskHost)
        dataFlowTaskHost.Name = "Load Employee Dim"
        dataFlowTaskHost.FailPackageOnFailure = True
        dataFlowTaskHost.FailParentOnFailure = True
        dataFlowTaskHost.DelayValidation = False
        dataFlowTaskHost.Description = "Data Flow Task"
 
        '-----------Data Flow Inner component starts----------------
        Dim dataFlowTask As MainPipe = TryCast(dataFlowTaskHost.InnerObject, MainPipe)
        ' Source OLE DB connection manager to the package.
        Dim SconMgr As ConnectionManager = package.Connections("LocalHost.OLTP")
        ' Destination OLE DB connection manager to the package.
        Dim DconMgr As ConnectionManager = package.Connections("LocalHost.OLAP")
 
        ' Create and configure an OLE DB source component. 
        Dim source As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        source.ComponentClassID = "DTSAdapter.OLEDBSource.2"
 
        ' Create the design-time instance of the source.
        Dim srcDesignTime As CManagedComponentWrapper = source.Instantiate()
        ' The ProvideComponentProperties method creates a default output.
        srcDesignTime.ProvideComponentProperties()
        source.Name = "Transaction_T (OLTP)"
 
        ' Assign the connection manager.
        source.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        source.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
        ' Set the custom properties of the source.
        srcDesignTime.SetComponentProperty("AccessMode", 0)
        ' Mode 0 : OpenRowset / Table - View
        srcDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_T]")
        ' Connect to the data source, and then update the metadata for the source.
        srcDesignTime.AcquireConnections(Nothing)
        srcDesignTime.ReinitializeMetaData()
        srcDesignTime.ReleaseConnections()
 
        'Add an Lookup Transformation
        Dim lookupComponent As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        lookupComponent.ComponentClassID = "DTSTransform.Lookup.2"
        lookupComponent.Description = "Looks up values in a reference dataset by using exact matching."
        lookupComponent.Name = "Lookup"
        Dim lookupDesignTime As CManagedComponentWrapper = lookupComponent.Instantiate()
        lookupDesignTime.ProvideComponentProperties()
 
        ' Create the path from source to Lookup Transformation.
        ' It also helps in mapping input columns in lookup transformation
        Dim pathSource_Lookup As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathSource_Lookup.AttachPathAndPropagateNotifications(source.OutputCollection(0), lookupComponent.InputCollection(0))
 
        ' Assign the connection manager.
        lookupComponent.RuntimeConnectionCollection(0).ConnectionManagerID = SconMgr.ID
        lookupComponent.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(SconMgr)
        ' Set the custom properties.
        ' For CacheType: Full = 0, Partial = 1, None = 2
        lookupDesignTime.SetComponentProperty("CacheType", 0)
        lookupDesignTime.SetComponentProperty("SqlCommand", "SELECT * FROM [dbo].[Employee_Dim]")
 
        lookupDesignTime.AcquireConnections(Nothing)
        lookupDesignTime.ReinitializeMetaData()
        lookupDesignTime.ReleaseConnections()
 
 
        ' Mark the columns we are joining on
        'Input: Contains the collection of columns that represents the data provided to a component 
        Dim lookupInput As IDTSInput100 = lookupComponent.InputCollection(0)
        'Virtual Input: Represents the columns available to a component from the upstream component.
        Dim lookupVirtualInput As IDTSVirtualInput100 = lookupInput.GetVirtualInput()
 
        'Here the lookup will be done by joining [Transaction_Info].Emp_ID column with [Employee_Dim].emp_id column
        'SetUsageType: used to select the columns from the upstream component in the data flow that are used by the component. 
        Dim lookupInputColumn As IDTSInputColumn100 = lookupDesignTime.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection("Emp_ID").LineageID, DTSUsageType.UT_READONLY)
        'JoinToReferenceColumn = The name of the column in the reference table to which a source column joins.
        'SetInputColumnProperty = Sets the custom property of the input column "Emp_ID" and the custom property is "JoinToReferenceColumn" with value "emp_id"
        lookupDesignTime.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "emp_id")
 
        'Note: The above code can be iterated in a loop for more than one joining columns.
        'To replace the existing input column with the reference column, use "CopyFromReferenceColumn" property in the above code.
        'For replacement of existing column, the input column should be set as DTSUsageType.UT_READWRITE
 
 
        Dim lookupOutput As IDTSOutput100 = lookupComponent.OutputCollection(0)
        ' insert columns from refrence table
        Dim lookupOutputColumn As IDTSOutputColumn100 = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "employee_Sl_No", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "employee_Sl_No")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "Department", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "Department")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_DOB", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_DOB")
        lookupOutputColumn = lookupDesignTime.InsertOutputColumnAt(lookupOutput.ID, 0, "emp_name", "")
        lookupDesignTime.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "emp_name")
 
 
 
        ' Create and configure an OLE DB destination component.
        Dim destinationTrInfo As IDTSComponentMetaData100 = dataFlowTask.ComponentMetaDataCollection.[New]()
        destinationTrInfo.ComponentClassID = "DTSAdapter.OLEDBDestination.2"
        ' Create the design-time instance of the destination.
        Dim destDesignTime As CManagedComponentWrapper = destinationTrInfo.Instantiate()
        ' The ProvideComponentProperties method creates a default input.
        destDesignTime.ProvideComponentProperties()
        destinationTrInfo.Name = "Transaciton_Info (OLAP)"
        ' Assign the connection manager.
        destinationTrInfo.RuntimeConnectionCollection(0).ConnectionManagerID = DconMgr.ID
        destinationTrInfo.RuntimeConnectionCollection(0).ConnectionManager = DtsConvert.GetExtendedInterface(DconMgr)
        ' Set the custom properties.
        destDesignTime.SetComponentProperty("AccessMode", 3)
        ' Mode 3 : OpenRowset Using FastLoad / Table - View fast load
        destDesignTime.SetComponentProperty("OpenRowset", "[dbo].[Transaction_Info]")
        destDesignTime.AcquireConnections(Nothing)
        destDesignTime.ReinitializeMetaData()
        destDesignTime.ReleaseConnections()
 
 
        ' Create the path from Condtional Split transformation to destination.
        Dim pathLookup_DestSales As IDTSPath100 = dataFlowTask.PathCollection.[New]()
        pathLookup_DestSales.Name = "Sales Dept"
        pathLookup_DestSales.AttachPathAndPropagateNotifications(lookupComponent.OutputCollection(0), destinationTrInfo.InputCollection(0))
 
 
        ' Get the destination's default input and virtual input.
        Dim inputTrInfo As IDTSInput100 = destinationTrInfo.InputCollection(0)
        Dim vInputTrInfo As IDTSVirtualInput100 = inputTrInfo.GetVirtualInput()
        ' Iterate through the virtual input column collection.
        For Each vColumn As IDTSVirtualInputColumn100 In vInputTrInfo.VirtualInputColumnCollection
            ' Call the SetUsageType method of the destination
            ' to add each available virtual input column as an input column.
            destDesignTime.SetUsageType(inputTrInfo.ID, vInputTrInfo, vColumn.LineageID, DTSUsageType.UT_READONLY)
        Next
        'map external metadata to the inputcolumn
        For Each inputColumn As IDTSInputColumn100 In inputTrInfo.InputColumnCollection
 
            Dim exMetaColumn As IDTSExternalMetadataColumn100 = DirectCast(inputTrInfo.ExternalMetadataColumnCollection(inputColumn.Name), IDTSExternalMetadataColumn100)
            inputColumn.ExternalMetadataColumnID = exMetaColumn.ID
        Next
 
 
        '-----------Data Flow Inner component ends-------------------
 
        ' Precedence constraints from "Package Start Indicator" to "Load Employee Dim"
        Dim PackageStartIndicatorTask_dataFlowTaskHost As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Package Start Indicator"), package.Executables("Load Employee Dim"))
        PackageStartIndicatorTask_dataFlowTaskHost.Name = "Package Start Indicator" & "Load Employee Dim"
 
 
        'To add Package End Indicator (Execute Sql task )
        Dim PackageEndIndicatorTask As TaskHost = DirectCast(package.Executables.Add("Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask.ExecuteSQLTask, Microsoft.SqlServer.SQLTask, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"), TaskHost)
        PackageEndIndicatorTask.Name = "Package End Indicator"
        PackageEndIndicatorTask.Description = "Execute SQL Task Description"
        Dim PackageEndIndicatorTaskdoc As New XmlDocument()
        PackageEndIndicatorTaskdoc.LoadXml("<SQLTask:SqlTaskData SQLTask:Connection=""LocalHost.OLAP"" SQLTask:SqlStmtSourceType=""DirectInput"" SQLTask:SqlStatementSource=""EXEC dbo.EndTransaction ?, ?"" SQLTask:ResultType=""ResultSetType_None"" xmlns:SQLTask=""www.microsoft.com/sqlserver/dts/tasks/sqltask""><SQLTask:ParameterBinding SQLTask:ParameterName=""0"" SQLTask:DtsVariableName=""User::TransactionID"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /><SQLTask:ParameterBinding SQLTask:ParameterName=""1"" SQLTask:DtsVariableName=""User::RowCountVar"" SQLTask:ParameterDirection=""Input"" SQLTask:DataType=""3"" /></SQLTask:SqlTaskData>")
        DirectCast(PackageEndIndicatorTask.InnerObject, IDTSComponentPersist).LoadFromXML(PackageEndIndicatorTaskdoc.DocumentElement, Nothing)
 
        ' Precedence constraints from "Load Employee Dim" to "Package End Indicator"
        Dim dataFlowTaskHost_PackageEndIndicatorTask As PrecedenceConstraint = package.PrecedenceConstraints.Add(package.Executables("Load Employee Dim"), package.Executables("Package End Indicator"))
        dataFlowTaskHost_PackageEndIndicatorTask.Name = "Load Employee Dim" & "Package End Indicator"
 
        'Saving the package
        Dim app As New Microsoft.SqlServer.Dts.Runtime.Application()
        app.SaveToXml("I:Site workSamplePackage 2008SamplePackageSamplePackage.dtsx", package, Nothing)
    End Sub
End Module

To make your work more easy and effortless, SQL Lion team comes up with a unique tool named “SSIS Component Explorer” that will help you in retrieving the Creation Name, Component Class ID, etc about any component like Control Flow Tasks, Data Flow Transformations, Connections Managers, Log Providers, etc in SSIS package. It also provides code snippets for each component.
Download the free version from here.

References:

MSDN Books Online
http://technet.microsoft.com/en-us/library/ms141821.aspx

Categories: SSIS, SSIS programming

Comments (0) Trackbacks (0) Leave a comment Trackback

No comments yet.

No trackbacks yet.

GROUP BY Clause (SQL Server 2005 Vs 2008) Monitoring Tempdb in SQL Server 2005

All Posts

Faster Extraction & Loading by SSIS: Best practices and innovative ideas to make SSIS package faster, simpler and more productive.
SQL Server 2005 Vs SQL Server 2008: Briefly describes the upgraded features of SQL server 2008 over SQL server 2005.
SSIS Programming Basic: Programming Integration service by C# .Net or VB .Net.
Programming Conditional Split Transformation: Iincoming data can be split into multiple outputs by Integration Programming.
Optimizing TempDB (SQL 2005): Optimization techniques for tempdb database to boost database performance.
Expert Logger: Batch logging of all the SSIS DTS packages.
Monitoring Tempdb (SQL 2005): Provides various parameters and performance majors to optimize and monitor tempdb database.
Programming Lookup Transformation: Programming SSIS Lookup transformation using C# .Net or VB .Net in both SQL Server 2005/2008.
GROUP BY Clause: Enhanced features (SQL Server 2005 Vs 2008), including CUBE, ROLLUP, GROUPING SETS, GROUPING_ID, etc.
Programming SSIS Logging: Programming logging mechanism in SSIS by using C# .net and VB .Net.
Delete Duplicate Records: Describes various ways to handle redundancy in the database tables.
SSIS Component Explorer: Unique utility to develop SSIS package programmatically with more ease and effectiveness.
Programming Sequence Container: Sequence container in SSIS package by C# .Net and VB .Net.
Duplicate Remover: Tool to search and delete duplicate data (rows) in your SQL Server database.
Programming For Loop Container: For Loop Container in SSIS package by C# .Net and VB .Net.
Extreme Catalog Pro: Tool for maintaining and organizing collection of your CD-ROMs, audio CDs, hard disks, removable disks, etc.
Enumerating Excel Files: Usage of Foreach Loop Container with enumeration of Excel Files in SSIS
Enumerating Excel Sheets: Foreach Loop Container with Foreach ADO.NET Schema Rowset Enumerator in SSIS
TRANSACTION Isolation Levels: All Transaction Isolation levels including detail description of database locks
SQL Server Indexes (Part 1): Concept of indexes with real background operation carried on during index operations.
SQL Server Indexes (Part 2): Different types of indexes with pros and cons at different levels like Cluster index Vs. Non-Cluster index.
SQL Server Indexes (Part 3): Optimizing techniques for better usage of index in order to boost performance of the database, monitoring indexes and best practices for indexing.
SQL Driver Issues Resolver: Tool to remove discrepancy in SQL Server 2005 / 2008 like Missing foreach enumerators (File enumerator, Item enumerator), Missing OLE DB or Ado .Net Drivers.

SQL Lion

Programming Lookup Transformation

All Posts

Recent Posts

Categories

Archives

Recent Comments