File Compression using SSIS

Ok,
I have been a little busy lately doing a lot of learning from a financial background into retail and as part of the been a developer I have a task to compress very large files for sftp. So, after a couple of hours a wrote the below code. Hopefully it will safe someone's the pain of trying to this for sql 2008.

/*
   Microsoft SQL Server Integration Services Script Task
   Write scripts using Microsoft Visual C# 2008.
   The ScriptMain is the entry point class of the script.
*/

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.IO.Compression;
using System.IO;

namespace ST_e131a658cb92485b8392d785449083bc.csproj
{
    [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

        #region VSTA generated code
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion

        /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/
        
        public void Main()
        {
            // TODO: Add your code here

            String FullFilePath = Dts.Variables["User::strFullFilePath"].Value.ToString();
            Compress(FullFilePath); 
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        public static void Compress(string FileSelected)
        {

            int BufferSize = 2048;
            Int64 bytesRead = 0;
            byte[] buffer = new byte[BufferSize];

            FileInfo fileToCompress = new FileInfo(FileSelected);
            Int64 bytesInFile = fileToCompress.Length;
            if (fileToCompress.Exists)
            {
                using (FileStream originalFileStream = fileToCompress.OpenRead())
                {
                    if ((File.GetAttributes(fileToCompress.FullName) &
                       FileAttributes.Hidden) != FileAttributes.Hidden & fileToCompress.Extension != ".gz")
                    {
                       
                        using (FileStream compressedFileStream = File.Create(fileToCompress.FullName + ".gz"))
                        {
                            using (GZipStream compressionStream = new GZipStream(compressedFileStream,
                               CompressionMode.Compress, true))
                            {
                                do
                                {
                                    int FileBytes = originalFileStream.Read(buffer, 0, buffer.Length);
                                    if (FileBytes == 0)
                                    {
                                        break;
                                    }
                                    compressionStream.Write(buffer, 0, buffer.Length);
                                    bytesRead += FileBytes;
                                } while (bytesRead < bytesInFile);

                            }
                        }
                        //FileInfo info = new FileInfo(directoryPath + "\\" + fileToCompress.Name + ".gz");
                        //Console.WriteLine("Compressed {0} from {1} to {2} bytes.",
                        //fileToCompress.Name, fileToCompress.Length.ToString(), info.Length.ToString());
                    }

                }
            }
        }
            
           
        
        
       

    }
    
      
}

Parent Package calling a child package

As my first month at Fanatics I have to get used to the concept of parent child packages using standard ssis. Normally I write my code for the packages on the metadata and then let c# take care of the rest. However, I didn't build it, I inherit it, so, first I'm going to create a few prototypes on how to do this using code to generate the standard templates, and also start building something more usesful, it will take time but I do love a challenge.

Leaving Black Night Financial Services

Tomorrow is the end and a new beginning starts working at Fanatics, and I can't help but thinking on how great was the ride at Black Nights, we have some turns, ups and downs but everything work alright thanks to the the people that your work with and work for.
Thanks, who knows that someday instead of good bye I would be saying once again I'm back.

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

    }
}