Creating SSIS package using EzApi

Question

I am using EzApi for creating a SSIS package. However I am unable to create a package having multiple sources and single destination. For example two OLEDB sources and a single OLEDB destination. What exactly I want to know is how to add merge transformation using c# code. Please help

Answer 1

In EzApi there is a component called EzMerge If you need to create a custom dataflow - you can use the base class called EzDataFlowPackage:

class MyPackage: EzDataFlowPackage
{
        public EzOleDbSource src1;
        public EzOleDbSource src2;
        public EzMerge merge;
        public EzOleDbDest dest;
        public EzOleDbConnectionManager srcConnMgr1;
        public EzOleDbConnectionManager srcConnMgr2;
        public EzOleDbConnectionManager destConnMgr;


        public EzMyPackage() : base() 
        {
            srcConnMgr1 = new EzOleDbConnectionManager(this);
            srcConnMgr2 = new EzOleDbConnectionManager(this);
            src1 = new EzOleDbSource(DataFlow);
            src2 = new EzOleDbSource(DataFlow);
            dest mew EzOleDbDest(DataFlow);
            src1.Connection = srcConnMgr1;
            src2.Connection = srcConnMgr2;
            dest.Connection = destConnMgr;
            merge = new EzMerge(DataFlow);
            src1.AttachTo(merge);
            src2.AttachTo(merge);
            merge.AttachTo(dest);
        }

        public EzMyPackage(Package p) : base(p) { }

        public static implicit operator EzMyPackage(Package p) { return new EzMyPackage(p); }
}

I just typed this code in - so it may contain errors. By after all this your package layout is ready. And you can simply set component properties.

Answer 2

I've not used EzApi, however in BIDS (Visual Studio) you need to have two data sources, one data destination, and a 'merge' component to connect it:

Like so:

EDIT

With regard to crating is progamatically. Check out the following link:

http://thinkerkk.blogspot.com/2007/08/programmatically-creating-dataflow-with.html

Look for the code after the comment //create the Merge Transformation

//create the Merge Transformation
IDTSComponentMetaData90 merge = dataflow.ComponentMetaDataCollection.New();
merge.ComponentClassID = "DTSTransform.MergeJoin";
CManagedComponentWrapper mergeDesigntime = merge.Instantiate();
mergeDesigntime.ProvideComponentProperties();
merge.Name = "Merge Source1 and source2";
Console.WriteLine("merge created ");
merge.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
merge.InputCollection[0].HasSideEffects = false;
merge.InputCollection[1].ExternalMetadataColumnCollection.IsUsed = false;
merge.InputCollection[1].HasSideEffects = false;

//create path from source1 to merge
/*More code - see article*/

//create path from source2 to merge
/*More code - see article*/

The following links may also be useful for creating SSIS packages programatically:

http://msdn.microsoft.com/en-us/library/ms135946.aspx

http://blogs.msdn.com/b/mattm/archive/2008/12/30/samples-for-creating-ssis-packages-programmatically.aspx

Answer 3

Using EzApi for SQL Server there is EzMerge which is the same control like Merge while designing for DataTools. Find SQL Server EzApi for different versions of SQL Servers:

SQLServer2008 https://github.com/koureasstavros/SQLServer2008EzApi https://www.nuget.org/packages/EzApi2008
SQLServer2012 https://github.com/koureasstavros/SQLServer2012EzApi https://www.nuget.org/packages/EzApi2012
SQLServer2014 https://github.com/koureasstavros/SQLServer2014EzApi https://www.nuget.org/packages/EzApi2014
SQLServer2016 https://github.com/koureasstavros/SQLServer2016EzApi https://www.nuget.org/packages/EzApi2016
SQLServer2017 https://github.com/koureasstavros/SQLServer2017EzApi https://www.nuget.org/packages/EzApi2017
SQLServer2019 https://github.com/koureasstavros/SQLServer2019EzApi https://www.nuget.org/packages/EzApi2019
SQLServer2022 https://github.com/koureasstavros/SQLServer2022EzApi https://www.nuget.org/packages/EzApi2022