September 16, 2014

A very complex package generator

#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");
                //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);



                //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(" ","");

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

                // 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 ConMgr = 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;
                snapshotDate.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                snapshotDate.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                PipelineWrapper.IDTSCustomProperty100 outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
                outSnapShotDate.Name = "Expression";
                outSnapShotDate.Value = "@[User::dtSnapshotDate]";
                outSnapShotDate = snapshotDate.CustomPropertyCollection.New();
                outSnapShotDate.Name = "FriendlyExpression";
                outSnapShotDate.Value = "@[User::dtSnapshotDate]";


                //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();

                // 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 = ConMgr.ID;
                icmDestination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConMgr);
                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]);
               
                TrimmerToDestination = DataFlow.PathCollection.New();
                TrimmerToDestination.AttachPathAndPropagateNotifications(TrimThatColumn0.OutputCollection[0],icmDestination.InputCollection[0] );
                // Mapping externalcolumns Array

                MappColumns(icmDestination, mcwDestination);


              //  SaveSSIS(@"E:\\BDELoader\\ssis\\", myPackage.Name.ToString());
                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 ConMgr = myPackage.Connections.Add("OLEDB");

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

            return ConMgr;

        }
        /*
         * 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();
            }


        }




        #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

    }
}

Codeplex 2012 Unpack decimal

//UnpackDecimal
//===========

//SSIS has no provision for converting packed decimal (comp-3) data.

//This transform takes a bytes column, and converts to decimal, using a user provided scale

//Configure in advanced editor by clicking input columns containing packed decimal values.
//Afterwards, go to the input and output tab and set the PackedScale property on the input column
//if it is to differ from 0.

//This component automatically creates an output column when the input column is selected, then
//forbids any change to that column's metadata. The only allowed user edits are on column name and
//description, and the scale. All else are rejected.

//Scale must be between 0 and 28.
//The length of the input field must be 14 bytes or fewer. This imposes a limit of 27 digits on the
//converted value. Though 28 digit numbers are supported by the decimal format, they take 15 bytes
//to store, and a 15 byte packed can hold 29 digits, overflowing the decimal at runtime.

//Left as an exercise for the student:
//a) add an error output to direct overflows or badly formatted value to.
//b) update component to allow 15 bytes in, and reject values with non-zero most significant nibble.

//Interesting Features
//====================

//This component is part of a series of components that illustrate increasingly complex
//behavior, each one exercising a greater proportion of the SSIS object model. If studying
//in order, this component follows ConfigureUnDouble, and precedes UnDoubleOut.

//This component was built to provide an introduction to the use of output columns. Also
//illustrated are:

//- Binding input to output columns with custom properties.
//- Distinguishing upstream columns from each other.
//- Operating on DT_BYTES
//- Copy and paste support
//- Use of Ondeletinginputcolumn.
//- Setusagetype gives you a virtual input. (because new columns won’t be in the input)
//- ReinitializeMetadata to clear up referring columns
//- Use the input buffer id not the output buffer id, when setting output column values.

// By James Howey
// Copyright (c) Microsoft Corporation.  All rights reserved.

using System;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts;


using System.Diagnostics;


namespace CustomComponents
{
    [DtsPipelineComponent(
                           DisplayName = "Unpack Decimal",
                           ComponentType = ComponentType.Transform,
                           IconResource = "UnpackDecimal.UnpackDecimal.ico")]

    public class UnpackDecimal : PipelineComponent
    {
        #region helper methods and objects

        private void PostError(string message)
        {
            bool cancel = false;
            this.ComponentMetaData.FireError(0, this.ComponentMetaData.Name, message, "", 0, out cancel);
        }

        private DTSValidationStatus promoteStatus(ref DTSValidationStatus currentStatus, DTSValidationStatus newStatus)
        {
            // statuses are ranked in order of increasing severity, from
            //   valid to broken to needsnewmetadata to corrupt.
            // bad status, if any, is result of programming error
            switch (currentStatus)
            {
                case DTSValidationStatus.VS_ISVALID:
                    switch (newStatus)
                    {
                        case DTSValidationStatus.VS_ISBROKEN:
                        case DTSValidationStatus.VS_ISCORRUPT:
                        case DTSValidationStatus.VS_NEEDSNEWMETADATA:
                            currentStatus = newStatus;
                            break;
                        case DTSValidationStatus.VS_ISVALID:
                            break;
                        default:
                            throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
                    }
                    break;
                case DTSValidationStatus.VS_ISBROKEN:
                    switch (newStatus)
                    {
                        case DTSValidationStatus.VS_ISCORRUPT:
                        case DTSValidationStatus.VS_NEEDSNEWMETADATA:
                            currentStatus = newStatus;
                            break;
                        case DTSValidationStatus.VS_ISVALID:
                        case DTSValidationStatus.VS_ISBROKEN:
                            break;
                        default:
                            throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
                    }
                    break;
                case DTSValidationStatus.VS_NEEDSNEWMETADATA:
                    switch (newStatus)
                    {
                        case DTSValidationStatus.VS_ISCORRUPT:
                            currentStatus = newStatus;
                            break;
                        case DTSValidationStatus.VS_ISVALID:
                        case DTSValidationStatus.VS_ISBROKEN:
                        case DTSValidationStatus.VS_NEEDSNEWMETADATA:
                            break;
                        default:
                            throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
                    }
                    break;
                case DTSValidationStatus.VS_ISCORRUPT:
                    switch (newStatus)
                    {
                        case DTSValidationStatus.VS_ISCORRUPT:
                        case DTSValidationStatus.VS_ISVALID:
                        case DTSValidationStatus.VS_ISBROKEN:
                        case DTSValidationStatus.VS_NEEDSNEWMETADATA:
                            break;
                        default:
                            throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
                    }
                    break;
                default:
                    throw new System.ApplicationException("Internal Error: A value outside the scope of the status enumeration was found.");
            }
            return currentStatus;
        }
        #endregion

        #region design time functionality

        //public override void ProvideComponentProperties()
        //{
        //    this.RemoveAllInputsOutputsAndCustomProperties();
        //    base.ProvideComponentProperties();

        //    this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
        //    // Specify that the Component has an error output.
        //    this.ComponentMetaData.UsesDispositions = true;

        //    this.ComponentMetaData.Name = "Unpack Decimal";
        //    this.ComponentMetaData.Description = "SSIS Data Flow Component to convert Comp-3 fields into standard numeric fields";
        //    this.ComponentMetaData.ContactInfo = "ETLDEVDBA@gmail.com";

        //    // Input
        //    IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
        //    input.Name = "Input";
        //    input.HasSideEffects = false;
        //    input.Description = "Compact Decimal Binary Stream inputs";
        //    input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
        //    input.ErrorOrTruncationOperation = "ValidationFailure";

        //    //Output
        //    IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
        //    output.Name = "Output";
        //    output.IsErrorOut = false;
        //    output.HasSideEffects = false;
        //    output.SynchronousInputID = input.ID;
        //    output.ExclusionGroup = 1;

         

        //}
        public override DTSValidationStatus Validate()
        {
            // if scale doesn't match output column, or extra output columns, or insufficient
            // output columns, this component is corrupt. We can say this because in our design
            // time methods, we have compelled the output columns to remain in lockstep with
            // input column selections

            // if I had numerics, I would have to worry about matching the precision of the numeric
            // to the length of the packed field, but decimal is fixed length, so all that changes
            // is scale.

            // check for:
            //  basic layout validation
            //  orphaned input columns

            //  if component is corrupt, we are permitted to return without further checks
            DTSValidationStatus status = base.Validate();
            if (status == DTSValidationStatus.VS_ISCORRUPT)
            {
                return status;
            }

            IDTSComponentMetaData100 metadata = this.ComponentMetaData;
            IDTSCustomProperty100 customProperty;
            int lineageID;


            IDTSInputCollection100 inputCollection = metadata.InputCollection;
            IDTSInput100 input = inputCollection[0];
            IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
            IDTSInputColumn100 inputColumn;

            IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
            IDTSOutput100 output = outputCollection[0];
            IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
            IDTSOutputColumn100 outputColumn;

            if (inputColumnCollection.Count != outputColumnCollection.Count)
            {
                PostError("Input and output columns don't match up.");
                return DTSValidationStatus.VS_ISCORRUPT;
            }

            for (int j = 0; j < outputColumnCollection.Count; j++)
            {
                outputColumn = outputColumnCollection[j];
                lineageID = outputColumn.LineageID;
                IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
                try
                {
                    customProperty = customPropertyCollection["InputColumnID"];
                }
                catch (Exception)
                {
                    PostError("Output column " + outputColumn.Name + " has no InputColumnID custom property.");
                    return DTSValidationStatus.VS_ISCORRUPT;
                }

                int inputColumnID = (int)customProperty.Value;
                try
                {
                    inputColumn = inputColumnCollection.FindObjectByID(inputColumnID);
                }
                catch (Exception)
                {
                    PostError("InputColumnID " + inputColumnID + " not found in selected input columns.");
                    return DTSValidationStatus.VS_ISCORRUPT;
                }
                // if input column is orphaned, we already have reinit status, and cleanup will
                // eliminate any following errors.
                if (inputColumn.IsValid)
                {
                    if (inputColumn.DataType != DataType.DT_BYTES)
                    {
                        PostError("Column " + inputColumn.Name + " is not a DT_BYTES column");
                        promoteStatus(ref status, DTSValidationStatus.VS_ISBROKEN);
                    }
                    IDTSCustomPropertyCollection100 inputColumnCustomProperties = inputColumn.CustomPropertyCollection;
                    try
                    {
                        customProperty = inputColumnCustomProperties["PackedScale"];
                    }
                    catch (Exception)
                    {
                        PostError("InputColumnID " + inputColumnID + " has no PackedScale property");
                        return DTSValidationStatus.VS_ISCORRUPT;
                    }
                    int packedScale = (int)customProperty.Value;
                    if (packedScale < 0 || packedScale > 28)
                    {
                        PostError("PackedScale must be between 0 and 28.");
                        return DTSValidationStatus.VS_ISCORRUPT;
                    }
                    if (outputColumn.DataType != DataType.DT_DECIMAL)
                    {
                        PostError("Output column data type must be decimal");
                        return DTSValidationStatus.VS_ISCORRUPT;
                    }
                    if (outputColumn.Scale != packedScale)
                    {

                        PostError("PackedScale must match output column scale.");
                        return DTSValidationStatus.VS_ISCORRUPT;
                    }
                }
            }
            return status;
        }


        public override void ReinitializeMetaData()
        {
            // This should delete orphaned columns for us, calling OnDeletingInputColumn
            base.ReinitializeMetaData();
        }

        public override IDTSInput100 InsertInput(DTSInsertPlacement insertPlacement, int inputID)
        {
            PostError("Component requires exactly one input. New input is forbidden.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDINPUT);
        }

        public override void DeleteInput(int inputID)
        {
            PostError("Component requires exactly one input. Deleted input is forbidden.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTDELETEINPUT);
        }

        public override IDTSOutput100 InsertOutput(DTSInsertPlacement insertPlacement, int outputID)
        {
            PostError("Component requires exactly one output. New output is forbidden.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDOUTPUT);
        }

        public override void DeleteOutput(int outputID)
        {
            PostError("Component requires exactly one output. Deleted output is forbidden.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTDELETEOUTPUT);
        }

        public override IDTSOutputColumn100 InsertOutputColumnAt(int outputID, int outputColumnIndex, string name, string description)
        {
            PostError("Component forbids adding output columns. Check input columnn to configure.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDCOLUMN);
        }

        public override void DeleteOutputColumn(int outputID, int outputColumnID)
        {
            PostError("Component forbids deleting output columns. Uncheck input columnn to configure.");
            throw new PipelineComponentHResultException(HResults.DTS_E_CANTADDCOLUMN);
        }

        // done
        public override void OnDeletingInputColumn(int inputID, int inputColumnID)
        {
            IDTSComponentMetaData100 metadata = this.ComponentMetaData;

            // An input column is being deleted. This may be because it no longer is
            // present upstream, or because the user has unchecked it.
            // We have to retrieve the column, then remove any
            // reference to it in the output column collection
            IDTSInputCollection100 inputCollection = metadata.InputCollection;
            IDTSInput100 input = inputCollection[0];
            // Get input column collection and retrieve interesting column
            IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
            IDTSInputColumn100 inputColumn = inputColumnCollection.FindObjectByID(inputColumnID);

            // Iterate over output columns looking for matching id in custom property.
            IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
            IDTSOutput100 output = outputCollection[0];
            IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
            for (int j = 0; j < outputColumnCollection.Count; j++)
            {
                IDTSOutputColumn100 outputColumn = outputColumnCollection[j];
                IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
                IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
                int columnID = (int)customProperty.Value;
                if (columnID == inputColumnID)
                {
                    // we just delete the output column
                    base.DeleteOutputColumn(output.ID, outputColumn.ID);
                }
            }
        }

        // done

        public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
        {
            IDTSInputColumn100 inputColumn;
            IDTSComponentMetaData100 metadata = this.ComponentMetaData;

            switch (usageType)
            {
                case DTSUsageType.UT_READONLY:
                    IDTSVirtualInputColumn100 column = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
                    if (column.DataType != DataType.DT_BYTES)
                    {
                        PostError("Component operates only on bytes input. Other types are forbidden.");
                        throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
                    }
                    else
                    {
                        if (column.Length > 14)
                        {
                            PostError("Component accepts a maximum field length of 14.");
                            throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
                        }
                        else
                        {
                            inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
                           
                            String ColumnName = inputColumn.Name.ToString();
                            String oColumnName = "";
                            if (ColumnName.Substring(0, 2).Equals("pkg"))
                            {
                                inputColumn.Name = ColumnName;
                                oColumnName = ColumnName.Replace("pkg", "");
                            }
                            else
                            {
                                inputColumn.Name = "pkg" + ColumnName;
                                oColumnName = ColumnName;

                            }
                           
                            IDTSCustomPropertyCollection100 customProperties = inputColumn.CustomPropertyCollection;
                            IDTSCustomProperty100 customProperty = customProperties.New();
                            customProperty.Name = "PackedScale"; // do not localize
                            customProperty.ContainsID = false;
                            customProperty.Value = 0; // default is zero for scale

                            IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
                            IDTSOutput100 output = outputCollection[0];
                            IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
                            // this will generate a unique name, because upstream component names can't have dots int them
                            //IDTSOutputColumn100 newColumn = base.InsertOutputColumnAt(output.ID, outputColumnCollection.Count,
                            // inputColumn.UpstreamComponentName + "." + inputColumn.Name + ".Decimal", "");
                            IDTSOutputColumn100 newColumn = base.InsertOutputColumnAt(output.ID, outputColumnCollection.Count,
                                oColumnName, "UnpackColumn");
                            newColumn.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, 0, 0);


                            customProperties = newColumn.CustomPropertyCollection;
                            customProperty = customProperties.New();
                            customProperty.Name = "InputColumnID"; // do not localize
                            // support cut and paste
                            customProperty.ContainsID = true;
                            customProperty.Value = inputColumn.ID;

                            return inputColumn;
                        }
                    }
                case DTSUsageType.UT_READWRITE:
                    PostError("Component requires that input columns be marked read only.");
                    throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
                case DTSUsageType.UT_IGNORED:
                    IDTSInputCollection100 inputCollection = metadata.InputCollection;
                    IDTSInput100 input = inputCollection[0];
                    IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
                    inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
                    this.OnDeletingInputColumn(inputID, inputColumn.ID);
                    inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
                    return inputColumn;
                default:
                    throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
            }
        }
        public override IDTSCustomProperty100 SetOutputColumnProperty(int outputID, int outputColumnID, string propertyName, object propertyValue)
        {
            if (propertyName == "Scale")
            {
                int value = (int)propertyValue;
                // scale ranges from 0 to 28
                if (value >= 0 && value <= 28)
                {
                    IDTSComponentMetaData100 metadata = this.ComponentMetaData;
                    //IDTSOutputCollection100 OutputCollection = metadata.OutputCollection;
                    //IDTSOutput100 Output = OutputCollection[0];
                    //IDTSOutputColumnCollection100 OutputColumnCollection = Output.OutputColumnCollection;
                    //IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
                    //IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
                    return base.SetOutputColumnProperty(outputID, outputColumnID, propertyName, propertyValue);
                }
                else
                {
                    PostError("PackedScale must be between 0 and 28.");
                    throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
                }
            }
            else
            {
                PostError("Unexpected property name to set.");
                throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
            }

        }

        public override IDTSCustomProperty100 SetInputColumnProperty(int inputID, int inputColumnID, string propertyName, object propertyValue)
        {
            if (propertyName == "PackedScale")
            {
                int value = (int)propertyValue;
                // scale ranges from 0 to 28
                if (value >= 0 && value <= 28)
                {
                    IDTSComponentMetaData100 metadata = this.ComponentMetaData;
                    IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
                    IDTSOutput100 output = outputCollection[0];
                    IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
                    IDTSOutputColumn100 outputColumn;
                    for (int j = 0; j < outputColumnCollection.Count; j++)
                    {
                        outputColumn = outputColumnCollection[j];
                        IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
                        IDTSCustomProperty100 customProperty = customPropertyCollection["InputColumnID"];
                        int linkedInputID = (int)customProperty.Value;
                        if (linkedInputID == inputColumnID)
                        {
                            outputColumn.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, value, 0);
                            return base.SetInputColumnProperty(inputID, inputColumnID, propertyName, propertyValue);
                        }
                    }
                    PostError("Couldn't find matching output column. Component likely corrupt.");
                    throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
                }
                else
                {
                    PostError("PackedScale must be between 0 and 28.");
                    throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
                }
            }
            else
            {
                PostError("Unexpected property name to set.");
                throw new PipelineComponentHResultException(HResults.DTS_E_FAILEDTOSETPROPERTY);
            }
        }


        #endregion

        #region private member variables

        private int[] outColumnWriteIDs;
        private int[] outColumnSourceIDs;
        private int[] outColumnScale;
        private int numOutColumnWrites = 0;

        #endregion

        #region runtime functionality
        public override void PreExecute()
        {
            IDTSCustomProperty100 customProperty;
            int lineageID;

            IDTSComponentMetaData100 metadata = this.ComponentMetaData;

            IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
            int inputBufferID = input.Buffer;
            IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
            IDTSInputColumn100 inputColumn;
            IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
            IDTSOutput100 output = outputCollection[0];
            IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
            IDTSOutputColumn100 outputColumn;

            // get output columns to write
            this.outColumnWriteIDs = new int[outputColumnCollection.Count];
            this.outColumnSourceIDs = new int[outputColumnCollection.Count];
            this.outColumnScale = new int[outputColumnCollection.Count];
            this.numOutColumnWrites = 0;
            for (int j = 0; j < outputColumnCollection.Count; j++)
            {
                outputColumn = outputColumnCollection[j];
                lineageID = outputColumn.LineageID;
                // this.numOutColumnWrites index is incremented farther below.
                this.outColumnWriteIDs[this.numOutColumnWrites] = this.BufferManager.FindColumnByLineageID(inputBufferID, lineageID);
                IDTSCustomPropertyCollection100 customPropertyCollection = outputColumn.CustomPropertyCollection;
                customProperty = customPropertyCollection["InputColumnID"];
                int inputID = (int)customProperty.Value;
                inputColumn = inputColumnCollection.FindObjectByID(inputID);
                this.outColumnSourceIDs[this.numOutColumnWrites] = this.BufferManager.FindColumnByLineageID(inputBufferID, inputColumn.LineageID);
                IDTSCustomPropertyCollection100 inputColumnCustomProperties = inputColumn.CustomPropertyCollection;
                customProperty = inputColumnCustomProperties["PackedScale"];
                this.outColumnScale[this.numOutColumnWrites++] = (int)customProperty.Value;

            }
        }

        public override void ProcessInput(int inputID, PipelineBuffer buffer)
        {
            System.Decimal result;

            byte[] source;
            if (!buffer.EndOfRowset)
            {
                while (buffer.NextRow())
                {
                    // this component nulls output columns on badly formatted input data
                    // a better implementation would provide an error output and give
                    // user control over disposition on error.
                    for (int j = 0; j < this.numOutColumnWrites; j++)
                    {
                        try
                        {
                            // GetBytes will throw if source column is null
                            source = buffer.GetBytes(this.outColumnSourceIDs[j]);
                            result = Unpack(source, this.outColumnScale[j]);
                            buffer.SetDecimal(this.outColumnWriteIDs[j], result);
                        }
                        catch (Exception)
                        {
                            buffer.SetNull(this.outColumnWriteIDs[j]);
                        }
                    }
                }
            }
        }

        private Decimal Unpack(byte[] inp, int scale)
        {
            long lo = 0;
            long mid = 0;
            long hi = 0;
            bool isNegative;

            // this nybble stores only the sign, not a digit.
            // "C" hex is positive, "D" hex is negative, and "F" hex is unsigned.
            switch (nibble(inp, 0))
            {
                case 0x0D:
                    isNegative = true;
                    break;
                case 0x0F:
                case 0x0C:
                    isNegative = false;
                    break;
                default:
                    throw new Exception("Bad sign nibble");
            }
            long intermediate;
            long carry;
            long digit;
            for (int j = inp.Length * 2 - 1; j > 0; j--)
            {
                // multiply by 10
                intermediate = lo * 10;
                lo = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                intermediate = mid * 10 + carry;
                mid = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                intermediate = hi * 10 + carry;
                hi = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                // By limiting input length to 14, we ensure overflow will never occur

                digit = nibble(inp, j);
                if (digit > 9)
                {
                    throw new Exception("Bad digit");
                }
                intermediate = lo + digit;
                lo = intermediate & 0xffffffff;
                carry = intermediate >> 32;
                if (carry > 0)
                {
                    intermediate = mid + carry;
                    mid = intermediate & 0xffffffff;
                    carry = intermediate >> 32;
                    if (carry > 0)
                    {
                        intermediate = hi + carry;
                        hi = intermediate & 0xffffffff;
                        carry = intermediate >> 32;
                        // carry should never be non-zero. Back up with validation
                    }
                }
            }
            return new Decimal((int)lo, (int)mid, (int)hi, isNegative, (byte)scale);
        }

        private int nibble(byte[] inp, int nibbleNo)
        {
            int b = inp[inp.Length - 1 - nibbleNo / 2];
            return (nibbleNo % 2 == 0) ? (b & 0x0000000F) : (b >> 4);
        }

        #endregion

    }
}

Contact Form

Name

Email *

Message *