September 22, 2014

Adding lookup to and second union all to comp-3 loader

#region Help:  Introduction to the script task
/* The Script Task allows you to perform virtually any operation that can be accomplished in
 * a .Net application within the context of an Integration Services control flow.
 *
 * Expand the other regions which have "Help" prefixes for examples of specific ways to use
 * Integration Services features within this script task. */
#endregion


#region Namespaces
using System;
using System.Data;
using System.Data.OleDb;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Pipeline;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Comp3 = CustomComponents.UnpackDecimal;
using Comp = CustomComponents.HexTranslator;
using TrimThatColumn = CustomComponents.TrimThatColumn;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using ScriptTask = Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using Microsoft.CSharp.RuntimeBinder;
using System.Collections.Generic;

using System.Text;

#endregion

namespace ST_8522b938b0114bac9e084e6e5c77032b
{
    /// <summary>
    /// ScriptMain is the entry point class of the script.  Do not change the name, attributes,
    /// or parent of this class.
    /// </summary>
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region Help:  Using Integration Services variables and parameters in a script
        /* To use a variable in this script, first ensure that the variable has been added to
         * either the list contained in the ReadOnlyVariables property or the list contained in
         * the ReadWriteVariables property of this script task, according to whether or not your
         * code needs to write to the variable.  To add the variable, save this script, close this instance of
         * Visual Studio, and update the ReadOnlyVariables and
         * ReadWriteVariables properties in the Script Transformation Editor window.
         * To use a parameter in this script, follow the same steps. Parameters are always read-only.
         *
         * Example of reading from a variable:
         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;
         *
         * Example of writing to a variable:
         *  Dts.Variables["User::myStringVariable"].Value = "new value";
         *
         * Example of reading from a package parameter:
         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;
         *
         * Example of reading from a project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;
         *
         * Example of reading from a sensitive project parameter:
         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();
         * */

        #endregion

        #region Help:  Firing Integration Services events from a script
        /* This script task can fire events for logging purposes.
         *
         * Example of firing an error event:
         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);
         *
         * Example of firing an information event:
         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)
         *
         * Example of firing a warning event:
         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);
         * */
        #endregion

        #region Help:  Using Integration Services connection managers in a script
        /* Some types of connection managers can be used in this script task.  See the topic
         * "Working with Connection Managers Programatically" for details.
         *
         * Example of using an ADO.Net connection manager:
         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);
         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);
         *
         * Example of using a File connection manager
         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);
         *  string filePath = (string)rawConnection;
         *  //Use the connection in some code here, then release the connection
         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);
         * */
        #endregion


        /// <summary>
        /// This method is called when this script task executes in the control flow.
        /// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
        /// To open Help, press F1.
        /// </summary>
        #region GlobalVariables
        //Global Variables
        Package myPackage = new Package();
         // Application
        Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
        Variables vars = null;
        StringBuilder Load2MasterCmd = new StringBuilder();
        StringBuilder Master2Oracle = new StringBuilder();
        String SaveDirectory = "";
        int BDELoaderId = 0;
        #endregion
        public void Main()
        {
            // TODO: Add your code here
            try
            {
                //Reference Variables
                Dts.VariableDispenser.LockForRead("User::objColumnInfo");
                Dts.VariableDispenser.LockForRead("User::objMasterColumnInfo");
                Dts.VariableDispenser.LockForRead("User::strFileName");
                Dts.VariableDispenser.LockForRead("User::strTabName");
                Dts.VariableDispenser.LockForRead("User::strTableName");
                Dts.VariableDispenser.LockForRead("User::intGroupNumber");
                Dts.VariableDispenser.LockForRead("User::strDatabaseName");
                Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
                Dts.VariableDispenser.LockForRead("User::strServerName");
                Dts.VariableDispenser.LockForRead("User::strBatchId");
                Dts.VariableDispenser.LockForRead("User::strSaveDirectory");
                Dts.VariableDispenser.LockForRead("User::strSqlCmd");
                Dts.VariableDispenser.LockForRead("User::strFileHeader");
                Dts.VariableDispenser.LockForRead("User::intBDELoaderID");
                Dts.VariableDispenser.LockForWrite("User::intRecordCount");
                Dts.VariableDispenser.LockForWrite("User::strArchiveDirectory");
                Dts.VariableDispenser.LockForWrite("User::bolIsLoaded");
                Dts.VariableDispenser.LockForRead("User::bolUseDeltaByte");
                Dts.VariableDispenser.LockForWrite("User::bigDuration");
                Dts.VariableDispenser.LockForRead("User::dtSnapshotDate");
                Dts.VariableDispenser.LockForRead("User::strLookupQuery");
                //Get variable collector
                Dts.VariableDispenser.GetVariables(ref vars);

                BDELoaderId = (int)vars["User::intBDELoaderID"].Value;
             
                // SnapshotDate Variable
                DateTime snapshot = (DateTime)vars["User::dtSnapshotDate"].Value;
                //Add Count Variable to Package
                Microsoft.SqlServer.Dts.Runtime.Variable intFileRowCount = myPackage.Variables.Add("intFileRowCount", false, "User", 0);
                Microsoft.SqlServer.Dts.Runtime.Variable intBDELoaderId = myPackage.Variables.Add("intBDELoaderId", false, "User", BDELoaderId);
                Microsoft.SqlServer.Dts.Runtime.Variable SnapShot_Date = myPackage.Variables.Add("dtSnapshotDate", false, "User",snapshot);
                Microsoft.SqlServer.Dts.Runtime.Variable Load_Date = myPackage.Variables.Add("dtLoadDate", false, "User", DateTime.Now);


                var LookupJoinColumns = new String[] { "LN_NO" };

                //Truncate table Command Variable
                StringBuilder TruncateCommand = new StringBuilder();
                TruncateCommand.Append(vars["User::strSqlCmd"].Value.ToString() + " ");
                TruncateCommand.Append("[Load]." + vars["User::strTableName"].Value.ToString());
                String TabName = vars["User::strTabName"].Value.ToString().Replace(" ","");
                String LookupQuery = vars["User::strLookupQuery"].Value.ToString();

                // Set Package Name
                myPackage.Name = "ssisRawFileToLoad_" + TabName;
                String LoadDirectory = vars["User::strLoadDirectory"].Value.ToString();
                SaveDirectory = vars["User::strSaveDirectory"].Value.ToString();
                String LoadTableName = "[Load]." + vars["User::strTableName"].Value.ToString(); // Using the master
                String FileName = vars["User::strFileName"].Value.ToString();
                String FlatFileConnectionString = LoadDirectory + "\\" + FileName;
             
                String SQLCmd = vars["User::strSqlCmd"].Value.ToString();
                String Truncate = TruncateCommand.ToString();



                // Local variable for File Path
                Microsoft.SqlServer.Dts.Runtime.Variable strLoadFilePath = myPackage.Variables.Add("strFullFilePath", false, "User", FlatFileConnectionString);
                Microsoft.SqlServer.Dts.Runtime.Variable strArchiveFilePath = myPackage.Variables.Add("strArchiveDirectory", false, "User", vars["User::strArchiveDirectory"].Value.ToString());

                // Joins Variables

                PipelineWrapper.IDTSPath100 DeriveColumnToCount;
                PipelineWrapper.IDTSPath100 UnionAllToTrimmer;
                PipelineWrapper.IDTSPath100 SourceToDeriveColumn;
                PipelineWrapper.IDTSPath100 CountToDistributor;
                PipelineWrapper.IDTSPath100 UnpackToUnpackInt0;
                PipelineWrapper.IDTSPath100 UnpackToUnpackInt1;
                PipelineWrapper.IDTSPath100 UnpackToUnpackInt2;
                PipelineWrapper.IDTSPath100 UnpackToUnpackInt3;
                PipelineWrapper.IDTSPath100 UnpackToUnpackInt4;
                PipelineWrapper.IDTSPath100 DistributorToUnpack0;
                PipelineWrapper.IDTSPath100 DistributorToUnpack1;
                PipelineWrapper.IDTSPath100 DistributorToUnpack2;
                PipelineWrapper.IDTSPath100 DistributorToUnpack3;
                PipelineWrapper.IDTSPath100 DistributorToUnpack4;
                PipelineWrapper.IDTSPath100 Unpack0ToUnionAll;
                PipelineWrapper.IDTSPath100 Unpack1ToUnionAll;
                PipelineWrapper.IDTSPath100 Unpack2ToUnionAll;
                PipelineWrapper.IDTSPath100 Unpack3ToUnionAll;
                PipelineWrapper.IDTSPath100 TrimmerToLookup;
                PipelineWrapper.IDTSPath100 LookupMatchToUnionAll;
                PipelineWrapper.IDTSPath100 LookupNotMatchToUnionAll;
                PipelineWrapper.IDTSPath100 UnionAllToDestination;

                // Get Columns Metadata
                DataTable ColumnInfo = ReadColumnInfo(vars["User::objColumnInfo"].Value);

               // DataTable MasterColumnInfo = ReadColumnInfo(vars["User::objMasterColumnInfo"].Value);
               // vars["User::objMasterColumnInfo"].Value = null;

                //Create Fixed Width Connection
                ConnectionManager FixedWidth = AddFixedWith(ColumnInfo, vars["User::strTabName"].Value.ToString().Replace(" ",""), FlatFileConnectionString);
               // GenerateInsert(MasterColumnInfo);
              //  String MasterToOracle = Master2Oracle.ToString();

                //Create OLEDB Connection
                ConnectionManager Destination = AddOLEDBConnection("destination", Dts.Connections["destination"].ConnectionString.ToString());
                ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
                ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());
                ConnectionManager Source = AddOLEDBConnection("source", Dts.Connections["source"].ConnectionString.ToString());

                //Add Logging reference
                AddLogging(MSDB.Name, true);

                // Add SQLTask Component
                Executable execSqlTask = AddSQLExecutable("STOCK:SQLTASk");



                //Wrap the executable in a TaskHost
                AddSQLTask(execSqlTask, "Truncate Load Table", "destination", Truncate);




                //Add Sequence Container
                Executable execSequence = AddSQLExecutable("STOCK:Sequence");
                Microsoft.SqlServer.Dts.Runtime.Sequence seqSequence = (Microsoft.SqlServer.Dts.Runtime.Sequence)execSequence;
                //Sequence Properties
                seqSequence.Name = "UnpackDecimals Loader";
                seqSequence.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.UseParentSetting;
                seqSequence.Description = "Unpack Decimal Container";
                //Set precedence Constraint
                Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint SqlTask2Sequece = myPackage.PrecedenceConstraints.Add(execSqlTask, execSequence);
                SqlTask2Sequece.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
                //set Precedence of Load2Master

                // Add SQLStask to sequence
              //  Executable execSqlTaskMasterInfo = seqSequence.Executables.Add("STOCK:SQLTask");
                //Executable execSqlTaskUpdateBDELoader = seqSequence.Executables.Add("STOCK.SQLTask");

                // Add WorkFlow Component
                Executable execDataFlow = seqSequence.Executables.Add("STOCK:PipelineTask");
                Microsoft.SqlServer.Dts.Runtime.TaskHost thDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execDataFlow;
                thDataFlow.Name = "LoadBDE";
                PipelineWrapper.MainPipe DataFlow = (PipelineWrapper.MainPipe)thDataFlow.InnerObject;

                // SQLTASK This may need to be remove ??????????????????????????????????????????????????????????????????????????
              //  AddSQLTask(execSqlTaskMasterInfo, "Load2MasterCasting", "destination", Load2MasterCmd.ToString());
                //  AddSQLTask(execSqlTaskUpdateBDELoader, "UpDataBDELoader", "host", Load2MasterCmd.ToString());
                // set precedence of workfolw
              //  Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint WorkFlow2Load2Master = seqSequence.PrecedenceConstraints.Add(execDataFlow, execSqlTaskMasterInfo);
               // WorkFlow2Load2Master.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
                //Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint Load2Master2UpdateBDE = seqSequence.PrecedenceConstraints.Add(execSqlTaskMasterInfo, execSqlTaskUpdateBDELoader);
                //Load2Master2UpdateBDE.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;

                //Add FileSource Componnent
                PipelineWrapper.IDTSComponentMetaData100 FileSource = AddFileSourceComponent(DataFlow, FixedWidth, TabName);
                //dictionary
             
                //Add Derivide Column Componnent
             
                PipelineWrapper.IDTSComponentMetaData100 icmdDeriveColumn = AddComponentMetadata(DataFlow, "DeriveColumn");
                icmdDeriveColumn.ComponentClassID = "DTSTransform.DerivedColumn";
                PipelineWrapper.CManagedComponentWrapper mcwDeriveColumn = icmdDeriveColumn.Instantiate();
                mcwDeriveColumn.ProvideComponentProperties();

                icmdDeriveColumn.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
                icmdDeriveColumn.InputCollection[0].HasSideEffects = false;

                Reinitiaze(mcwDeriveColumn);

                PipelineWrapper.IDTSOutputColumn100 snapshotDate = icmdDeriveColumn.OutputCollection[0].OutputColumnCollection.New();
                snapshotDate.Name = "Snapshot_Date";
                snapshotDate.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DATE,0,0,0,0);
                snapshotDate.ExternalMetadataColumnID = 0;

                PipelineWrapper.IDTSOutputColumn100 LoadDate = icmdDeriveColumn.OutputCollection[0].OutputColumnCollection.New();
                LoadDate.Name = "Load_Date";
                LoadDate.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DATE, 0, 0, 0, 0);
                LoadDate.ExternalMetadataColumnID = 1;

                snapshotDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                snapshotDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                PipelineWrapper.IDTSCustomProperty100 outSnapShotDate = snapshotDate.CustomPropertyCollection.New();

                LoadDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                LoadDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                PipelineWrapper.IDTSCustomProperty100 outLoadDate = LoadDate.CustomPropertyCollection.New();

                outSnapShotDate.Name = "Expression";
                outSnapShotDate.Value = "@[User::dtSnapshotDate]";
                outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
                outSnapShotDate.Name = "FriendlyExpression";
                outSnapShotDate.Value = "@[User::dtSnapshotDate]";

                outLoadDate.Name = "Expression";
                outLoadDate.Value = "@[User::dtLoadDate]";
                outLoadDate = LoadDate.CustomPropertyCollection.New();
                outLoadDate.Name = "FriendlyExpression";
                outLoadDate.Value = "@[User::dtLoadDate]";

                // Add Lookup Component
                PipelineWrapper.IDTSComponentMetaData100 icmdLookup = AddComponentMetadata(DataFlow, "SubClientId_Lookup");
                icmdLookup.ComponentClassID = "{671046B0-AA63-4C9F-90E4-C06E0B710CE3}";//"";
                PipelineWrapper.CManagedComponentWrapper mcwLookup = icmdLookup.Instantiate();
                icmdLookup.ValidateExternalMetadata = true;
                mcwLookup.ProvideComponentProperties();
                icmdLookup.Name ="SubClientID_Lookup";
           
           


                //Add RowCount Component
                PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(DataFlow, "RowCountComponent");
                icmdRowCount.ComponentClassID = "DTSTransform.RowCount";
                PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
                mcwRowCount.ProvideComponentProperties();
                mcwRowCount.SetComponentProperty("VariableName", "User::intFileRowCount");
             
               // Add Trim Component

                //Add TrimThatColumn component
                PipelineWrapper.IDTSComponentMetaData100 TrimThatColumn0 = AddTrimThatColumn(DataFlow, "TrimThatColumn");
                TrimThatColumn0.ComponentClassID = "{874F7595-FB5F-40FF-96AF-FBFF8250E3EF}";
                PipelineWrapper.CManagedComponentWrapper mcwTrimThatColumn0 = TrimThatColumn0.Instantiate();
                mcwTrimThatColumn0.ProvideComponentProperties();

                // Add Distributor Component
                PipelineWrapper.IDTSComponentMetaData100 Distributor = AddComponentMetadata(DataFlow, "dataDistributor");
                Distributor.ComponentClassID = "{F06C606C-5980-460C-B2AF-B0ECA031B7A4}";
                PipelineWrapper.CManagedComponentWrapper mcwDistributor = Distributor.Instantiate();
                mcwDistributor.ProvideComponentProperties();

           

                //Add Union All Component
                PipelineWrapper.IDTSComponentMetaData100 UnionAll = AddComponentMetadata(DataFlow, "UnionAll");
                UnionAll.ComponentClassID = "{B594E9A8-4351-4939-891C-CFE1AB93E925}";
                PipelineWrapper.CManagedComponentWrapper mcwUnialAll = UnionAll.Instantiate();
                mcwUnialAll.ProvideComponentProperties();

                // Union All For Lookup input and output
                PipelineWrapper.IDTSComponentMetaData100 LookupUnionAll = AddComponentMetadata(DataFlow, "LookupUnionAll");
                LookupUnionAll.ComponentClassID = "{B594E9A8-4351-4939-891C-CFE1AB93E925}";
                PipelineWrapper.CManagedComponentWrapper mcwLookupUnialAll = LookupUnionAll.Instantiate();
                mcwLookupUnialAll.ProvideComponentProperties();
                LookupUnionAll.Name = "LookupUnionAll";

                // Add Comp Hex translator
                PipelineWrapper.IDTSComponentMetaData100 UnpackInteger0 = AddUnpackIntegers(DataFlow, "HexToInt00");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger0 = UnpackInteger0.Instantiate();
                cmwUnpackInteger0.ProvideComponentProperties();
                UnpackInteger0.Name = "HexToInt00";

                // II
                PipelineWrapper.IDTSComponentMetaData100 UnpackInteger1 = AddUnpackIntegers(DataFlow, "HexToInt01");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger1 = UnpackInteger1.Instantiate();
                cmwUnpackInteger1.ProvideComponentProperties();
                UnpackInteger1.Name = "HexToInt01";

                //III
                PipelineWrapper.IDTSComponentMetaData100 UnpackInteger2 = AddUnpackIntegers(DataFlow, "HexToInt02");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger2 = UnpackInteger2.Instantiate();
                cmwUnpackInteger2.ProvideComponentProperties();
                UnpackInteger2.Name = "HexToInt02";

                //IV
                PipelineWrapper.IDTSComponentMetaData100 UnpackInteger3 = AddUnpackIntegers(DataFlow, "HexToInt03");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger3 = UnpackInteger3.Instantiate();
                cmwUnpackInteger3.ProvideComponentProperties();
                UnpackInteger3.Name = "HexToInt03";
                 //V
                //IV
                PipelineWrapper.IDTSComponentMetaData100 UnpackInteger4 = AddUnpackIntegers(DataFlow, "HexToInt04");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackInteger4 = UnpackInteger4.Instantiate();
                cmwUnpackInteger3.ProvideComponentProperties();
                UnpackInteger4.Name = "HexToInt04";

                // Add Unpack Decimal Custom Component

                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal0 = AddUnpackDecimal(DataFlow, "Unpack0Decimals");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal0 = UnpackDecimal0.Instantiate();
                cmwUnpackDecimal0.ProvideComponentProperties();
                UnpackDecimal0.Name = "Unpack0 Decimals";

                // II
                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal2 = AddUnpackDecimal(DataFlow, "Unpack2Decimals");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal2 = UnpackDecimal2.Instantiate();
                cmwUnpackDecimal2.ProvideComponentProperties();
                UnpackDecimal2.Name = "Unpack2 Decimals";

                //III
                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal3 = AddUnpackDecimal(DataFlow, "Unpack3Decimals");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal3 = UnpackDecimal3.Instantiate();
                cmwUnpackDecimal3.ProvideComponentProperties();
                UnpackDecimal3.Name = "Unpack3 Decimals";

                //IV
                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal1 = AddUnpackDecimal(DataFlow, "Unpack1Decimals");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal1 = UnpackDecimal1.Instantiate();
                cmwUnpackDecimal1.ProvideComponentProperties();
                UnpackDecimal1.Name = "Unpack1 Decimals";

                //V
                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal4 = AddUnpackDecimal(DataFlow, "Unpack1Decimals");
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal4 = UnpackDecimal1.Instantiate();
                cmwUnpackDecimal4.ProvideComponentProperties();
                UnpackDecimal4.Name = "Unpack4 Decimals";



                //Add OLEDB Destination Componnent
                PipelineWrapper.IDTSComponentMetaData100 icmDestination = AddComponentMetadata(DataFlow, "destination");
                icmDestination.ComponentClassID = "DTSAdapter.OLEDBDestination";
                icmDestination.ValidateExternalMetadata = true;

                PipelineWrapper.CManagedComponentWrapper mcwDestination = icmDestination.Instantiate();
                // tblDestination
                mcwDestination.ProvideComponentProperties();
                icmDestination.Name = "destination";
                icmDestination.Description = "destination";
                icmDestination.RuntimeConnectionCollection[0].ConnectionManagerID = Destination.ID;
                icmDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(Destination);
                mcwDestination.SetComponentProperty("AccessMode", 3);//FastLoad
                mcwDestination.SetComponentProperty("OpenRowset", LoadTableName);//tblDestination);
                mcwDestination.Validate();
                mcwDestination.SetComponentProperty("FastLoadKeepIdentity", false);
                mcwDestination.SetComponentProperty("FastLoadMaxInsertCommitSize",100000);//FastLoadMaxCommitSize);
                mcwDestination.SetComponentProperty("FastLoadKeepNulls", false);
                mcwDestination.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
                Reinitiaze(mcwDestination);
             

                //Join Source To UnpackDecimal Component
                SourceToDeriveColumn = DataFlow.PathCollection.New();
                // File Source To Count
                SourceToDeriveColumn.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0],icmdDeriveColumn.InputCollection[0]);
             
             
                DeriveColumnToCount = DataFlow.PathCollection.New();
                //Count to Distributor
                DeriveColumnToCount.AttachPathAndPropagateNotifications(icmdDeriveColumn.OutputCollection[0], icmdRowCount.InputCollection[0]);
                // Count to Distributor
                CountToDistributor = DataFlow.PathCollection.New();
                CountToDistributor.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], Distributor.InputCollection[0]);
                // Distributor Settings
                DistributorToUnpack0 = DataFlow.PathCollection.New();
                DistributorToUnpack0.AttachPathAndPropagateNotifications(Distributor.OutputCollection[0], UnpackInteger0.InputCollection[0]);

                DistributorToUnpack1 = DataFlow.PathCollection.New();
                DistributorToUnpack1.AttachPathAndPropagateNotifications(Distributor.OutputCollection[1], UnpackInteger1.InputCollection[0]);

                DistributorToUnpack2 = DataFlow.PathCollection.New();
                DistributorToUnpack2.AttachPathAndPropagateNotifications(Distributor.OutputCollection[2], UnpackInteger2.InputCollection[0]);

                DistributorToUnpack3 = DataFlow.PathCollection.New();
                DistributorToUnpack3.AttachPathAndPropagateNotifications(Distributor.OutputCollection[3], UnpackInteger3.InputCollection[0]);

                DistributorToUnpack4 = DataFlow.PathCollection.New();
                DistributorToUnpack4.AttachPathAndPropagateNotifications(Distributor.OutputCollection[4], UnpackInteger4.InputCollection[0]);

                //Join Unpack to unpackint
                UnpackToUnpackInt0 = DataFlow.PathCollection.New();
                UnpackToUnpackInt0.AttachPathAndPropagateNotifications(UnpackInteger0.OutputCollection[0], UnpackDecimal0.InputCollection[0]);

                UnpackToUnpackInt1 = DataFlow.PathCollection.New();
                UnpackToUnpackInt1.AttachPathAndPropagateNotifications(UnpackInteger1.OutputCollection[0], UnpackDecimal1.InputCollection[0]);

                UnpackToUnpackInt2 = DataFlow.PathCollection.New();
                UnpackToUnpackInt2.AttachPathAndPropagateNotifications(UnpackInteger2.OutputCollection[0], UnpackDecimal2.InputCollection[0]);

                UnpackToUnpackInt3 = DataFlow.PathCollection.New();
                UnpackToUnpackInt3.AttachPathAndPropagateNotifications(UnpackInteger3.OutputCollection[0], UnpackDecimal3.InputCollection[0]);

                UnpackToUnpackInt4 = DataFlow.PathCollection.New();
                UnpackToUnpackInt4.AttachPathAndPropagateNotifications(UnpackInteger4.OutputCollection[0], UnpackDecimal4.InputCollection[0]);

                //Set Properties for Bynary Fields
                SetFlatFileProperty(FileSource);
             
                // Unpack Comp Settings
                SelectDecimalColumns(UnpackInteger0, ColumnInfo, "IsComp");
                SelectDecimalColumns(UnpackInteger1, ColumnInfo, "IsComp");
                SelectDecimalColumns(UnpackInteger2, ColumnInfo, "IsComp");
                SelectDecimalColumns(UnpackInteger3, ColumnInfo, "IsComp");
                SelectDecimalColumns(UnpackInteger4, ColumnInfo, "IsComp");

                //  Unpack settings
                SelectDecimalColumns(UnpackDecimal0, ColumnInfo,"IsComp3");
                SetUnpackDecimalScale(UnpackDecimal0, ColumnInfo);

                SelectDecimalColumns(UnpackDecimal1, ColumnInfo, "IsComp3");
                SetUnpackDecimalScale(UnpackDecimal1, ColumnInfo);

                SelectDecimalColumns(UnpackDecimal2, ColumnInfo, "IsComp3");
                SetUnpackDecimalScale(UnpackDecimal2, ColumnInfo);

                SelectDecimalColumns(UnpackDecimal3, ColumnInfo, "IsComp3");
                SetUnpackDecimalScale(UnpackDecimal3, ColumnInfo);

                SelectDecimalColumns(UnpackDecimal4, ColumnInfo, "IsComp3");
                SetUnpackDecimalScale(UnpackDecimal4, ColumnInfo);

                // Join Unial All
                // Distributor Settings
                Unpack0ToUnionAll = DataFlow.PathCollection.New();
                Unpack0ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal0.OutputCollection[0], UnionAll.InputCollection[0]);

                Unpack1ToUnionAll = DataFlow.PathCollection.New();
                Unpack1ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal1.OutputCollection[0], UnionAll.InputCollection[1]);

                Unpack2ToUnionAll = DataFlow.PathCollection.New();
                Unpack2ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal2.OutputCollection[0], UnionAll.InputCollection[2]);

                Unpack3ToUnionAll = DataFlow.PathCollection.New();
                Unpack3ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal3.OutputCollection[0], UnionAll.InputCollection[3]);

                Unpack3ToUnionAll = DataFlow.PathCollection.New();
                Unpack3ToUnionAll.AttachPathAndPropagateNotifications(UnpackDecimal4.OutputCollection[0], UnionAll.InputCollection[4]);

                //Join Unpack Decimal to Destination
             
                UnionAllToTrimmer = DataFlow.PathCollection.New();
                UnionAllToTrimmer.AttachPathAndPropagateNotifications(UnionAll.OutputCollection[0], TrimThatColumn0.InputCollection[0]);
             
                TrimmerToLookup = DataFlow.PathCollection.New();
                TrimmerToLookup.AttachPathAndPropagateNotifications(TrimThatColumn0.OutputCollection[0],icmdLookup.InputCollection[0] );

                // LookUp Configuration
                icmdLookup.RuntimeConnectionCollection[0].ConnectionManagerID = Source.ID;
                icmdLookup.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(Source);

                //Lookup Cache Type - Full = 0, Partial = 1, None = 2
                mcwLookup.SetComponentProperty("CacheType", 0);//Full
                mcwLookup.SetComponentProperty("SqlCommand", LookupQuery.ToString());
                mcwLookup.SetComponentProperty("NoMatchBehavior", 1); // Redirect Not Match output
                Reinitiaze(mcwLookup);
                MappColumnsLookup(icmdLookup, mcwLookup, "LN_NO");
               /*
                // Mapping lookup LN_NO to source LN_NO query
                 //Input: Contains the collection of columns that represents the data provided to a component
            PipelineWrapper.IDTSInput100 lookupInput =  icmdLookup.InputCollection[0];
                //Virtual Input: Represents the columns available to a component from the upstream component.
            PipelineWrapper.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.
            PipelineWrapper.IDTSInputColumn100 lookupInputColumn = mcwLookup.SetUsageType(lookupInput.ID, lookupVirtualInput, lookupVirtualInput.VirtualInputColumnCollection["LN_NO"].LineageID, PipelineWrapper.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 "LN_NO" and the custom property is "JoinToReferenceColumn" with value "LN_NO"
            mcwLookup.SetInputColumnProperty(lookupInput.ID, lookupInputColumn.ID, "JoinToReferenceColumn", "ln_no");

            //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

            */
            PipelineWrapper.IDTSOutput100 lookupOutput = icmdLookup.OutputCollection[0];
            // insert columns from refrence table
            PipelineWrapper.IDTSOutputColumn100 lookupOutputColumn = mcwLookup.InsertOutputColumnAt(lookupOutput.ID, 0, "SubClient_ID", "");
            mcwLookup.SetOutputColumnProperty(lookupOutput.ID, lookupOutputColumn.ID, "CopyFromReferenceColumn", "SubClient_ID");

       

         
                LookupMatchToUnionAll = DataFlow.PathCollection.New();
                LookupMatchToUnionAll.AttachPathAndPropagateNotifications(icmdLookup.OutputCollection[0], LookupUnionAll.InputCollection[0]);

                //Lookup Not Match Mapping
                LookupNotMatchToUnionAll = DataFlow.PathCollection.New();
                LookupNotMatchToUnionAll.AttachPathAndPropagateNotifications(icmdLookup.OutputCollection[1], LookupUnionAll.InputCollection[1]);


                UnionAllToDestination = DataFlow.PathCollection.New();
                UnionAllToDestination.AttachPathAndPropagateNotifications(LookupUnionAll.OutputCollection[0], icmDestination.InputCollection[0]);

           
                MappColumns(icmDestination, mcwDestination);


           
                DTSExecResult result = myPackage.Execute();
                DTSExecStatus status = myPackage.ExecutionStatus;
                 vars["User::bigDuration"].Value = myPackage.ExecutionDuration;

             
                vars["User::intRecordCount"].Value = intFileRowCount.Value;
                if ((result == DTSExecResult.Failure) || (myPackage.ExecutionStatus == DTSExecStatus.Abend))
                {

                    // Package Failure on Error
                    vars["User::bolIsLoaded"].Value = false;

                    throw new System.InvalidCastException("Generated Package Failure");

                   // Dts.TaskResult = (int)ScriptResults.Failure;
                }
                else
                {
                    vars["User::intRecordCount"].Value = myPackage.Variables["User::intFileRowCount"].Value;
                    vars["User::bolIsLoaded"].Value = true;
                    SaveSSIS(SaveDirectory, myPackage.Name.ToString());
                    myPackage.Dispose();
                    // TODO: Add your code here

                    vars.Unlock();
                    //ColumnInfo = null;
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch (Exception e)
            {
                String packageName = myPackage.Name.ToString();
                myPackage.Name = "Error" + packageName;

                SaveSSIS(@"E:\\BDELoader\\ssis\\failure\\", myPackage.Name.ToString());
                myPackage.Dispose();
                Dts.Events.FireError(-1, "", "Ssis Failure:" + packageName + " Error:" + e.Message.ToString(), "", 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

        }
        public void AddLogging(String ConnectionName, Boolean Enable)
        {
            LogProvider pkLogging;
            pkLogging = myPackage.LogProviders.Add("DTS.LogProviderSQLServer");
            pkLogging.Name = "Log Provider For SQL SERVER";
            pkLogging.ConfigString = ConnectionName;
            pkLogging.Description = "SQL Server Logging ";
            myPackage.LoggingOptions.SelectedLogProviders.Add(pkLogging);
            myPackage.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
            myPackage.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
            switch (Enable)
            {
                case true:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
                    break;
                case false:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;
                default:
                    myPackage.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;

            }
        }
        private void SetUnpackDecimalScale(PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal, DataTable Metadata)
        {

            PipelineWrapper.IDTSInputCollection100 icolCollection = UnpackDecimal.InputCollection;
            PipelineWrapper.IDTSInput100 icol = icolCollection[0];
            PipelineWrapper.IDTSInputColumnCollection100 icolumnCollection = icol.InputColumnCollection;
            //output
            PipelineWrapper.IDTSOutputCollection100 ocolCollection = UnpackDecimal.OutputCollection;
            PipelineWrapper.IDTSOutput100 ocol = ocolCollection[0];
            PipelineWrapper.IDTSOutputColumnCollection100 ocolumnCollection = ocol.OutputColumnCollection;
            foreach (DataRow row in Metadata.Rows)
            {

                int Scale = (int)row["Scale"];
                String FieldLevelName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                if (Scale != -1)
                {
                    foreach (PipelineWrapper.IDTSInputColumn100 input in icolumnCollection)
                    {
                        String InputColumnName = input.Name.ToString().Remove(0, 3);

                        if (input.DataType == RuntimeWrapper.DataType.DT_BYTES & InputColumnName.ToLower().Equals(FieldLevelName))
                        {
                            input.CustomPropertyCollection["PackedScale"].Value = Scale;
                            foreach (PipelineWrapper.IDTSOutputColumn100 output in ocolumnCollection)
                            {
                                int InputColumnId = (int)output.CustomPropertyCollection["InputColumnID"].Value;
                                if (InputColumnId == input.ID)
                                {
                                    output.SetDataTypeProperties(RuntimeWrapper.DataType.DT_DECIMAL, 0, 0, Scale, 0);
                                    output.Name = FieldLevelName.ToLower();
                                }

                            }


                        }
                    }

                }

                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal = UnpackDecimal.Instantiate();
                //Reinitiaze(cmwUnpackDecimal);
            }

        }
        private void SetFlatFileProperty(PipelineWrapper.IDTSComponentMetaData100 FileSource)
        {


            PipelineWrapper.IDTSOutputCollection100 ocols1 = FileSource.OutputCollection;
            PipelineWrapper.IDTSOutput100 ocols = ocols1[0];


            foreach (PipelineWrapper.IDTSOutputColumn100 col in ocols.OutputColumnCollection)
            {
                RuntimeWrapper.DataType dty = col.DataType;
                if (dty == RuntimeWrapper.DataType.DT_BYTES)
                {
                    col.CustomPropertyCollection["UseBinaryFormat"].Value = true;
                 


                }
            }
        }
        /*
         *  Add Destinatin Componnet
         */
        private PipelineWrapper.IDTSComponentMetaData100 AddComponentMetadata(PipelineWrapper.MainPipe wf, String ComponentName)
        {
            PipelineWrapper.IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
            return icmd;
        }
        private void SaveSSIS(String Directory, String PackageName)
        {
            String FullFilePath = Directory + "\\" + PackageName + ".dtsx";
            myApplication.SaveToXml(FullFilePath, myPackage, null);
        }
        /* Read object Columns into a datatable*/
        private DataTable ReadColumnInfo(object ColumnInfo)
        {
            OleDbDataAdapter oleDb = new OleDbDataAdapter();
            DataTable dt = new DataTable();
            oleDb.Fill(dt, ColumnInfo);

            return dt;

        }
        /*
         * Reinitiazied
         */
        private void Reinitiaze(PipelineWrapper.CManagedComponentWrapper InstanceSource)
        {
            //Reinitialize Flat File source metadata,
            InstanceSource.AcquireConnections(null);
            InstanceSource.ReinitializeMetaData();
            InstanceSource.ReleaseConnections();
        }
        /*
         * Add OLEDB Connection
         */
        public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
        {
            ConnectionManager Destination = myPackage.Connections.Add("OLEDB");

            Destination.ConnectionString = ConnectionStr + "Packet Size=32076;";
            Destination.Name = ConnectionName;
            Destination.Description = "SQL OLEDB using " + ConnectionName;

            return Destination;

        }
        /*
         * Add WorkFlow
         *
         */
        private PipelineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
        {
            myPackage.Executables.Add("STOCK:PipelineTask");
            TaskHost _TaskHost = (TaskHost)myPackage.Executables[0];
            PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
            _TaskHost.Name = ComponentName;
            _TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");

            return dataFlowTask;

        }
        /*
         * Add File Source Componnet
         *
         */
        private PipelineWrapper.IDTSComponentMetaData100 AddFileSourceComponent(PipelineWrapper.MainPipe dataFlowTask, ConnectionManager flaFileCon, String ComponentName)
        {
            PipelineWrapper.IDTSComponentMetaData100 FileSource = dataFlowTask.ComponentMetaDataCollection.New();
            FileSource.Name = "SourceFile" + ComponentName;
            FileSource.ComponentClassID = "DTSAdapter.FlatFileSource";
            PipelineWrapper.CManagedComponentWrapper instanceFileSource = FileSource.Instantiate();
            instanceFileSource.ProvideComponentProperties();
            //Set source connection
            FileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flaFileCon.ID;
            FileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flaFileCon);

            //Reinitialize Flat File source metadata,
            Reinitiaze(instanceFileSource);
            return FileSource;
        }
        //Generate Insert command
        public void GenerateInsert(DataTable dtColumns)
        {

            Load2MasterCmd.Append(" Select " + Environment.NewLine);
            Load2MasterCmd.Append(BDELoaderId.ToString() + " As BDELoaderID,");
            Master2Oracle.Append(" Select " + Environment.NewLine);
            Master2Oracle.Append("[MasterId],");
            foreach (DataRow row in dtColumns.Rows)
            {
                int FieldLocation = (int)row["FieldLocation"];
                String ColName = (String)row["MasterColumnINfo"];
                String OraCol = (String)row["OracleColumnInfo"];
                switch (FieldLocation)
                {
                    case 1:
                        Load2MasterCmd.Append(ColName.ToString());
                        Load2MasterCmd.Append(Environment.NewLine);
                        Master2Oracle.Append(OraCol.ToString());
                        Master2Oracle.Append(Environment.NewLine);
                        break;
                    default:
                        Load2MasterCmd.Append("," + ColName.ToString());
                        Load2MasterCmd.Append(Environment.NewLine);
                        Master2Oracle.Append("," + OraCol.ToString());
                        Master2Oracle.Append(Environment.NewLine);

                        break;
                }
            }
            Load2MasterCmd.Append(" From Load." + vars["User::strTableName"].Value.ToString());
            Master2Oracle.Append(" From Master." + vars["User::strTableName"].Value.ToString());


        }
        //Generate Insert From Load to Master

        /*
         * Create Fixed Width Column Connection Manager
         */
        private ConnectionManager AddFixedWith(DataTable dtColumns, String ConnectionName, String ConnectionString)
        {
            String DeltaFileByte = "DELTA_FILE_BYTE";
            Boolean UsedDeltaByte = (Boolean)vars["User::bolUseDeltaByte"].Value;
            //Create Insert Statement From LoadToMaster
            //StringBuilder LoadMasterCmd = new StringBuilder();
            //LoadMasterCmd.Append("INSERT INTO [Master].");
            //LoadMasterCmd.Append(vars["User::strTableName"].Value.ToString() + "(" + Environment.NewLine);

            ConnectionManager flatConn = myPackage.Connections.Add("FLATFILE");
            flatConn.Name = ConnectionName.Replace(" ","");
            flatConn.ConnectionString = ConnectionString;
            flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
            flatConn.Properties["CodePage"].SetValue(flatConn, "37");
            flatConn.Properties["DataRowsToSkip"].SetValue(flatConn,0);
            flatConn.Properties["RowDelimiter"].SetValue(flatConn, "\r\n");
            flatConn.Properties["LocaleID"].SetValue(flatConn, "1033");
            //Inner object
            RuntimeWrapper.IDTSConnectionManagerFlatFile100 ConnectionFlatFile = (RuntimeWrapper.IDTSConnectionManagerFlatFile100)flatConn.InnerObject;


            foreach (DataRow row in dtColumns.Rows)
            {

                String ColumnName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                if (UsedDeltaByte)
                {
                    RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
                    FlatFileColumn100.ColumnType = "FixedWidth";
                    FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
                    FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
                    RuntimeWrapper.IDTSName100 ColName = (RuntimeWrapper.IDTSName100)FlatFileColumn100;
                    int FieldLocation = (int)row["FieldLocation"];
                    int Scale = (int)row["Scale"];
                    switch (Scale)
                    {
                        case -1:
                            FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
                            ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                            break;
                        default:
                            FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
                            ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                            break;
                    }
                }
                else
                {
                    if (!DeltaFileByte.Equals(ColumnName))
                    {
                        RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 FlatFileColumn100 = ConnectionFlatFile.Columns.Add();
                        FlatFileColumn100.ColumnType = "FixedWidth";
                        FlatFileColumn100.ColumnWidth = (int)row["FieldLength"];
                        FlatFileColumn100.MaximumWidth = (int)row["FieldLength"];
                        RuntimeWrapper.IDTSName100 ColName = (RuntimeWrapper.IDTSName100)FlatFileColumn100;
                        int FieldLocation = (int)row["FieldLocation"];
                        int Scale = (int)row["Scale"];
                        switch (Scale)
                        {
                            case -1:
                                FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
                                ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                                break;
                            default:
                                FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
                                ColName.Name = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
                                break;
                        }
                    }
                }





            }
            //LoadMasterCmd.Append(")");
            //Load2MasterCmd = LoadMasterCmd;
            //LoadMasterCmd = null;
            return flatConn;
        }
        /*
         *
         * Unpack Decimal
         */
        private PipelineWrapper.IDTSComponentMetaData100 AddUnpackDecimal(PipelineWrapper.MainPipe dataFlowTask, String Name)
        {

            PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();

            // mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
            mpk.ComponentClassID = typeof(Comp3).AssemblyQualifiedName;
            mpk.Name = Name;
            PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
            instance.ProvideComponentProperties();

            //  Reinitiaze(instance);


            return mpk;

        }
        /*
     *
     * Unpack integers
     */
        private PipelineWrapper.IDTSComponentMetaData100 AddUnpackIntegers(PipelineWrapper.MainPipe dataFlowTask, String Name)
        {

            PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();

            // mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
            mpk.ComponentClassID = typeof(Comp).AssemblyQualifiedName;
             PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
            instance.ProvideComponentProperties();
            mpk.Name = Name;

            //  Reinitiaze(instance);


            return mpk;

        }
        private PipelineWrapper.IDTSComponentMetaData100 AddTrimThatColumn(PipelineWrapper.MainPipe dataFlowTask, String Name)
        {
            PipelineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();

            // mpk.ComponentClassID = typeof(CustomComponents.TrimThatColumn).AssemblyQualifiedName;
            mpk.ComponentClassID = typeof(TrimThatColumn).AssemblyQualifiedName;
            PipelineWrapper.CManagedComponentWrapper instance = mpk.Instantiate();
            instance.ProvideComponentProperties();
            mpk.Name = Name;

            //  Reinitiaze(instance);


            return mpk;

        }
        // Create SSIS executable
        private Executable AddSQLExecutable(String MONIKER)
        {
            Executable exec = myPackage.Executables.Add(MONIKER);

            return exec;
        }
        // Select Decimals Input and Generate Output

        private void SelectDecimalColumns(PipelineWrapper.IDTSComponentMetaData100 PackedDecimal,DataTable Metadata,String CompactType)
        {
            PipelineWrapper.CManagedComponentWrapper InstanceUnpack = PackedDecimal.Instantiate();

            PipelineWrapper.IDTSInputCollection100 inputCollection = PackedDecimal.InputCollection;
            PipelineWrapper.IDTSInput100 input = inputCollection[0];
            PipelineWrapper.IDTSVirtualInput100 vinput = input.GetVirtualInput();
            PipelineWrapper.IDTSVirtualInputColumnCollection100 vInputColumns = vinput.VirtualInputColumnCollection;

            foreach (DataRow row in Metadata.Rows)
            {
                Boolean bolComp = (Boolean)row[CompactType];
              String FieldLevelName = (String)row["FieldLevelName"].ToString().Replace("[", "").Replace("]", "").ToLower();
            if(bolComp)
            {
                foreach (PipelineWrapper.IDTSVirtualInputColumn100 virtualInputColumn in vInputColumns)
                {
                    String InputColumnName = virtualInputColumn.Name.ToString().ToLower();
                    if (virtualInputColumn.DataType == RuntimeWrapper.DataType.DT_BYTES & InputColumnName.ToLower().Equals(FieldLevelName))
                    {
                        // Select column, and retain new input column

                        PipelineWrapper.IDTSInputColumn100 inputColumn = InstanceUnpack.SetUsageType(input.ID, vinput, virtualInputColumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);



                    }
                }

            }
        }


        }
        private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd)
        {
            Microsoft.SqlServer.Dts.Runtime.TaskHost sqlTaskHost = (Microsoft.SqlServer.Dts.Runtime.TaskHost)exec;
            //Add Properties
            sqlTaskHost.Properties["Name"].SetValue(sqlTaskHost, TaskName);
            sqlTaskHost.Properties["Description"].SetValue(sqlTaskHost, TaskName);
            sqlTaskHost.Properties["Connection"].SetValue(sqlTaskHost, Connection);
            sqlTaskHost.Properties["SqlStatementSource"].SetValue(sqlTaskHost, SqlCmd);

            IDTSExecuteSQL iexecSqlTask = (IDTSExecuteSQL)sqlTaskHost.InnerObject;
            iexecSqlTask.ResultSetType = ResultSetType.ResultSetType_None;
            iexecSqlTask.SqlStatementSourceType = SqlStatementSourceType.DirectInput;


        }

        private void MappColumns(PipelineWrapper.IDTSComponentMetaData100 icmDest, PipelineWrapper.CManagedComponentWrapper mcwDest)
        {
            //mappings

            Dictionary<String, int> extenalColumnLineageId = new Dictionary<string, int>(StringComparer.InvariantCultureIgnoreCase);
            PipelineWrapper.IDTSInput100 inpDestination = icmDest.InputCollection[0];
            PipelineWrapper.IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();

            PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = (PipelineWrapper.IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;

            foreach(PipelineWrapper.IDTSExternalMetadataColumn100 extcol in inpDestination.ExternalMetadataColumnCollection)
            {

           
                extenalColumnLineageId.Add(extcol.Name, extcol.ID);
            }
            foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
            {
           
                try
                {


                    if (vcolumn.DataType != RuntimeWrapper.DataType.DT_BYTES && extenalColumnLineageId.ContainsKey(vcolumn.Name))
                    {
                                             
                          int extcolId = extenalColumnLineageId[vcolumn.Name.ToString()];
                     
                         int destinationId = inpDestination.ID;
                         int lineageId = vcolumn.LineageID;
                   
                     
                        PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(destinationId, vinpDestination, lineageId, PipelineWrapper.DTSUsageType.UT_READONLY);
                        // map external column
                        // inputColumn.Name = inputColumn.Name.Replace("[", "").Replace("]", "");
                        PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = (PipelineWrapper.IDTSExternalMetadataColumn100)inpDestination.ExternalMetadataColumnCollection.FindObjectByID(extcolId);

                        mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
                    }
                    else
                    {
                        PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
                    }
                }
                catch (Exception e)
                {
                    String error = e.Message.ToString();
                    PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);

                    // map external column

                }

            }
            if (mcwDest.Validate() == PipelineWrapper.DTSValidationStatus.VS_NEEDSNEWMETADATA)
            {
                mcwDest.ReinitializeMetaData();
            }


        }
        private void MappColumnsLookup(PipelineWrapper.IDTSComponentMetaData100 icmLookup, PipelineWrapper.CManagedComponentWrapper mcwLookup, string DeltaColumns)
        {
            //mappings


            PipelineWrapper.IDTSInput100 inputLookup = icmLookup.InputCollection[0];
            PipelineWrapper.IDTSInputColumnCollection100 cinputlookup = inputLookup.InputColumnCollection;

            PipelineWrapper.IDTSVirtualInput100 vinputLookup = inputLookup.GetVirtualInput();
            PipelineWrapper.IDTSVirtualInputColumnCollection100 vinpcDestination = vinputLookup.VirtualInputColumnCollection;

            foreach (PipelineWrapper.IDTSVirtualInputColumn100 vcolumn in vinputLookup.VirtualInputColumnCollection)
            {
                try
                {
                    String ColumnName = vcolumn.Name.ToString();
                    if (String.Compare(ColumnName,DeltaColumns,true)==0)
                    {
                        PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
                        // map external column
                        mcwLookup.SetInputColumnProperty(inputLookup.ID, inputColumn.ID, "JoinToReferenceColumn", vcolumn.Name);
                    }
                    else
                    {
                        PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
                    }
                    // mcwLookup.MapInputColumn(inputLookup.ID, inputColumn.ID, extColumn.ID);
                }
                catch
                {
                    PipelineWrapper.IDTSInputColumn100 inputColumn = mcwLookup.SetUsageType(inputLookup.ID, vinputLookup, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_IGNORED);
                    throw;
                    // map external column

                }

            }


        }




        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        ///
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

    }
}

No comments:

Post a Comment

Contact Form

Name

Email *

Message *