Adding DeriveColumn Component syntax

Yesterday I have to dig into my old repository to add a derived column into a package, eventually I was able to figured that out, but here it is just as a remainder that somethings are like zombies, don't matter how many times to shoot them if is not in the head they come back :-)

 //Add Derive Column for BatchId
                PipelineWrapper.IDTSComponentMetaData100 icmdDeriveRawColumn = AddComponentMetadata(RawDataFlow, "AddBatchIdColumn");
                icmdDeriveRawColumn.ComponentClassID = "DTSTransform.DerivedColumn";
                PipelineWrapper.CManagedComponentWrapper mcwDeriveRawColumn = icmdDeriveRawColumn.Instantiate();
                mcwDeriveRawColumn.ProvideComponentProperties();
                
                icmdDeriveRawColumn.InputCollection[0].ExternalMetadataColumnCollection.IsUsed = false;
                icmdDeriveRawColumn.InputCollection[0].HasSideEffects = false;
                
                Reinitiaze(mcwDeriveRawColumn);
                PipelineWrapper.IDTSOutputColumn100 BatchIdCol = icmdDeriveRawColumn.OutputCollection[0].OutputColumnCollection.New();
                BatchIdCol.Name = "BatchId";
                BatchIdCol.SetDataTypeProperties(RuntimeWrapper.DataType.DT_WSTR,8,0,0,0);
                BatchIdCol.ExternalMetadataColumnID = 0;
                BatchIdCol.ErrorRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                BatchIdCol.TruncationRowDisposition = PipelineWrapper.DTSRowDisposition.RD_FailComponent;
                PipelineWrapper.IDTSCustomProperty100 BatchColProp = BatchIdCol.CustomPropertyCollection.New();
                BatchColProp.Name = "Expression";
                BatchColProp.Value = "@[User::strBatchId]";
                BatchColProp = BatchIdCol.CustomPropertyCollection.New();
                BatchColProp.Name = "FriendlyExpression";
                BatchColProp.Value = "@[User::strBatchId]";

Finally upgrade that old code.

Finally I was able to update that old code from VB.Net to C#,
this first pass is just to make it work, not changes in the original logic, however I have to said I have gone a long way from my early days of coding to today. The next step is to create a most robust package but for my future presentation,but I have to remember this was done sometime back in 2005 and it finally got decommission on 2011 it got the job done for many of the data refresh for the ops dba. Our current method is far superior in quality and performer but as always this is a good example for some poor ETL developer in some dark alley scratching his head on how to transfer all those 300+ tables across multiple despair environments..

#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 Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.SqlServer.Dts.Pipeline;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using System.IO;
using System.Text;
#endregion

namespace ST_191bde99d06c46c4a731611b77518191
{
    /// <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>

        //Global variable
        Variables var = null;
        Microsoft.SqlServer.Dts.Runtime.Package pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
        public void Main()
        {
            //Read
            Dts.VariableDispenser.LockForRead("User::strPkColumn");
            Dts.VariableDispenser.LockForRead("User::strBatchId");
            Dts.VariableDispenser.LockForRead("User::strSourceArchiveTable");
            Dts.VariableDispenser.LockForRead("User::bitIsRetention");
            Dts.VariableDispenser.LockForRead("User::intArchiveExportId");
            Dts.VariableDispenser.LockForRead("User::intFastLoadBatchSize");
            Dts.VariableDispenser.LockForRead("User::intFastLoadCommit");
            Dts.VariableDispenser.LockForRead("User::bitIsFastLoad");
            Dts.VariableDispenser.LockForRead("User::bitIsSqlCmd");
            Dts.VariableDispenser.LockForRead("User::bitIsSqlOverride");
            Dts.VariableDispenser.LockForRead("User::bitIsTransferData");
            Dts.VariableDispenser.LockForRead("User::strAutoSqlCmd");
            Dts.VariableDispenser.LockForRead("User::strDestinationDatabase");
            Dts.VariableDispenser.LockForRead("User::strDestinationSchema");
            Dts.VariableDispenser.LockForRead("User::strDestinationServer");
            Dts.VariableDispenser.LockForRead("User::strDestinationTable");
            Dts.VariableDispenser.LockForRead("User::strSourceDatabase");
            Dts.VariableDispenser.LockForRead("User::strSourceSchema");
            Dts.VariableDispenser.LockForRead("User::strSourceServer");
            Dts.VariableDispenser.LockForRead("User::strSourceTable");
            Dts.VariableDispenser.LockForRead("User::strSqlCmd");
            Dts.VariableDispenser.LockForRead("User::strSqlFilter");

            //Write

            Dts.VariableDispenser.LockForWrite("User::intIsPkgFail");
            Dts.VariableDispenser.LockForWrite("User::strXmlDoc");
            Dts.VariableDispenser.LockForWrite("User::intDestCurrCnt");
            Dts.VariableDispenser.LockForWrite("User::intDestPrevCnt");
            Dts.VariableDispenser.LockForWrite("User::intSourceCnt");
             Dts.VariableDispenser.GetVariables(ref var);
            //local variables

            Microsoft.SqlServer.Dts.Runtime.Variable DestPrevCnt = pkg.Variables.Add("intDestPrevCnt", false, "User", 0);
            Microsoft.SqlServer.Dts.Runtime.Variable DestCurrCnt = pkg.Variables.Add("intDestCurrCnt", false, "User", 0);
            Microsoft.SqlServer.Dts.Runtime.Variable SourceCnt = pkg.Variables.Add("intSourceCnt", false, "User", 0);
            Microsoft.SqlServer.Dts.Runtime.Variable ArchiveId = pkg.Variables.Add("intArchiveExportId", false, "User", var["User::intArchiveExportId"].Value);
            Microsoft.SqlServer.Dts.Runtime.Variable BatchId = pkg.Variables.Add("strBatchId", false, "User", var["User::strBatchId"].Value.ToString());
            pkg.Variables.Add("ExtractDate", false, "User", DateTime.Now);

         

            String pkColumn = var["User::strPkColumn"].Value.ToString();
            String SqlCounts = "Select Count (1) From ";
            String SqlCmdForId = "Select " + pkColumn + " From ";
            String RowsPerBatch = "";
            String ExecuteSqlCmd = "";

            //Connection Information
            String SourceConStr = Dts.Connections["source"].ConnectionString;
            String DestinationConStr = Dts.Connections["destination"].ConnectionString;
            String HostConStr = Dts.Connections["host"].ConnectionString;
            String MsdbConStr = Dts.Connections["msdb"].ConnectionString;

            String SourceSqlCmd = var["User::strSqlCmd"].Value.ToString();
            String AutoSqlCmd = var["User::strAutoSqlCmd"].Value.ToString();
            String SourceSqlFilter = var["User::strSqlFilter"].Value.ToString();

            String PackageName = "SQLSaturdayArchive_";


            //Integers 
            Int32 ArchiveExportId = 0;
            Int32 FastLoadMaxCommitSize = 0;
            Int32 Failure = -999;
            //Int32 intDestPrev = 0;
            //Int32 intDestCurrCnt = 0;
            //Int32 intSourceCnt = 0;




            Microsoft.SqlServer.Dts.Runtime.TaskHost DestCnt;
            Microsoft.SqlServer.Dts.Runtime.TaskHost DestCurrt;
            Microsoft.SqlServer.Dts.Runtime.TaskHost DataFlowTaskHost;

            IDTSComponentMetaData100 SourceComponent;
            IDTSComponentMetaData100 SourceRowCount;
            IDTSComponentMetaData100 DestinationComponent;
            IDTSComponentMetaData100 MulticastComponent;

            CManagedComponentWrapper SourceInstance;
            CManagedComponentWrapper rowInstance;
            CManagedComponentWrapper DestinationInstance;
            CManagedComponentWrapper MulticastInstance;

                

            IDTSPath100 SourceCntPath;
            IDTSPath100 SourceMultiCastPath;
            //IDTSPath100 DeriveColumnMulticastPath;
            IDTSPath100 DestinationPath;

            MainPipe DataFlowTask;

            IDTSResultBinding DestcurrRB;
            IDTSResultBinding DestRB;

            ExecuteSQLTask DestCurr1;
            ExecuteSQLTask DestCnt1;
            Executable DataFlow;

            LogProvider pkgLogging;
            Executable seg;
            Executable eDestCnt;
            Executable eDestCurr;

            Boolean IsFastLoad;
            Boolean IsOverride;
            Boolean IsQuery;
            Boolean IsTransferData;
            Boolean IsRetention;

            Microsoft.SqlServer.Dts.Runtime.Sequence seg1;
            Microsoft.SqlServer.Dts.Runtime.Sequence seg2;
            Microsoft.SqlServer.Dts.Runtime.Sequence seg3;
            Microsoft.SqlServer.Dts.Runtime.Sequence seg4;

            //Source Variables
            String SourceSchema = var["User::strSourceSchema"].Value.ToString();
            String SourceTable = var["User::strSourceTable"].Value.ToString();
            String SourceSchTable = "[" + var["User::strSourceDatabase"].Value.ToString() + "]." + "[" + SourceSchema + "].[" + SourceTable + "]";
            String SourceServer = "[" + var["User::strSourceServer"].Value.ToString() + "]";

            Microsoft.SqlServer.Dts.Runtime.Variable TableName = pkg.Variables.Add("strTableName", false, "User", SourceServer.ToString() + "." + SourceSchTable.ToString());
            Microsoft.SqlServer.Dts.Runtime.Variable pkgServerName = pkg.Variables.Add("strSourceServerName", false, "User", SourceServer.ToString());
            //Destination Variables
            String DestinationSchema = var["User::strDestinationSchema"].Value.ToString();
            String DestinationTable = var["User::strDestinationTable"].Value.ToString();
            String SourceArchive = var["User::strSourceArchiveTable"].Value.ToString();
            String DestinationSchTable = "[" + DestinationSchema + "].[" + DestinationTable + "]";

            SqlCmdForId = SqlCmdForId + DestinationSchTable + " With (nolock)";

            //Variable Initialization
            FastLoadMaxCommitSize = (Int32)var["User::intFastLoadCommit"].Value;
            ArchiveExportId = (Int32)var["User::intArchiveExportId"].Value;
            RowsPerBatch = var["User::intFastLoadBatchSize"].Value.ToString();
            pkg.Name = PackageName + ArchiveExportId.ToString();
            IsFastLoad = (Boolean)var["User::bitIsFastLoad"].Value;
            IsOverride = (Boolean)var["User::bitIsSqlOverride"].Value;
            IsQuery = (Boolean)var["User::bitIsSqlCmd"].Value;
            IsTransferData = (Boolean)var["User::bitIsTransferData"].Value;
            IsRetention = (Boolean)var["User::bitIsRetention"].Value;

            //Add Connections
            ConnectionManager DestinationConMgr = AddOLEDBConnection("destination", Dts.Connections["destination"].ConnectionString.ToString());
            ConnectionManager SourceConMgr = AddOLEDBConnection("source", Dts.Connections["source"].ConnectionString.ToString());
            ConnectionManager host = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
            ConnectionManager msdb = AddOLEDBConnection("msdb", Dts.Connections["msdb"].ConnectionString.ToString());

            //Add Logging
            pkgLogging = pkg.LogProviders.Add("DTS.LogProviderSQLServer");
            pkgLogging.ConfigString = msdb.Name;
            pkgLogging.Description = "Packing logging Using Host msdb";
            pkg.LoggingOptions.SelectedLogProviders.Add(pkgLogging);
            pkg.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
            pkg.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
            pkg.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;

            //Set Isolation Level
            pkg.IsolationLevel = IsolationLevel.ReadUncommitted;

            //Set Sequences
            seg = pkg.Executables.Add("STOCK:Sequence");
            seg1 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
            seg2 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
            seg3 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;
            seg4 = (Microsoft.SqlServer.Dts.Runtime.Sequence)seg;

            seg1.Name = "ArchiveTable_Sequence1";
            seg2.Name = "ArchiveTable_Sequence2";
            seg3.Name = "ArchiveId_Sequence";
            try
            {
                eDestCnt = seg1.Executables.Add("STOCK:SQLTASK");
                DestCnt = (Microsoft.SqlServer.Dts.Runtime.TaskHost)eDestCnt;
                DestCnt.FailPackageOnFailure = true;
                DestCnt.IsolationLevel = IsolationLevel.ReadCommitted;
                DestCnt.Name = "DestinationPrevious";
                DestCnt.Properties["Connection"].SetValue(DestCnt, DestinationConMgr.ID);
                DestCnt.Properties["SqlStatementSource"].SetValue(DestCnt, SqlCounts + " " + DestinationSchTable);
                DestCnt.Properties["BypassPrepare"].SetValue(DestCnt, false);
                DestCnt.SetExpression("ResultSetType", "2"); //'2 = single row
                DestCnt.ExecValueVariable = DestPrevCnt;
                DestCnt1 = (ExecuteSQLTask)DestCnt.InnerObject;
                DestRB = DestCnt1.ResultSetBindings.Add();
                DestRB.DtsVariableName = "User::intDestPrevCnt";

                //Destination CurrentCount
                eDestCurr = seg2.Executables.Add("STOCK:SQLTASK");
                DestCurrt = (Microsoft.SqlServer.Dts.Runtime.TaskHost)eDestCurr;
                DestCurrt.FailPackageOnFailure = true;
                DestCurrt.IsolationLevel = IsolationLevel.ReadUncommitted;
                DestCurrt.Name = "DestinationCurret";
                DestCurrt.Properties["Connection"].SetValue(DestCurrt, DestinationConMgr.ID);
                DestCurrt.Properties["SqlStatementSource"].SetValue(DestCurrt, SqlCounts + " " + DestinationSchTable);
                DestCurrt.Properties["BypassPrepare"].SetValue(DestCurrt, false);
                DestCurrt.SetExpression("ResultSetType", "2");// '2 = single row
                DestCnt.ExecValueVariable = DestCurrCnt;
                DestCurr1 = (ExecuteSQLTask)DestCurrt.InnerObject;
                DestcurrRB = DestCurr1.ResultSetBindings.Add();
                DestcurrRB.DtsVariableName = "User::intDestCurrCnt";

                //Set Execution Query

                if (IsQuery & IsRetention)
                {
                    ExecuteSqlCmd = SourceSqlCmd;
                }
                else
                {
                    ExecuteSqlCmd = AutoSqlCmd;
                }

                //DataFlow task
                 DataFlow = seg3.Executables.Add("STOCK:PipelineTask");
                 DataFlowTaskHost = ( Microsoft.SqlServer.Dts.Runtime.TaskHost)DataFlow;
                 DataFlowTaskHost.Name = "ArchiveExportDataFlow";
                 DataFlowTask = (MainPipe)DataFlowTaskHost.InnerObject;

                 // Join containers
            Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint DestToWf   = seg1.PrecedenceConstraints.Add(eDestCnt, DataFlow);
            DestToWf.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;
            Microsoft.SqlServer.Dts.Runtime.PrecedenceConstraint WfToDest   = seg2.PrecedenceConstraints.Add(DataFlow, eDestCurr);
            WfToDest.Value = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success;

               //Add Source Component
            SourceComponent = DataFlowTask.ComponentMetaDataCollection.New();
            SourceComponent.Name = "SourceTable";
            SourceComponent.ComponentClassID = "DTSAdapter.OleDbSource";
            // Get Source instance 
            SourceInstance = SourceComponent.Instantiate();
            SourceInstance.ProvideComponentProperties();
            
                //Set Source Connection
            SourceComponent.RuntimeConnectionCollection[0].ConnectionManagerID = SourceConMgr.ID;
            SourceComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(SourceConMgr);

            if (IsQuery == true)
            {
                SourceInstance.SetComponentProperty("AccessMode", 2);
                SourceInstance.SetComponentProperty("SqlCommand", ExecuteSqlCmd);
            }
            else
            {
                SourceInstance.SetComponentProperty("AccessMode", 0); //0 TableOrView
            }
            SourceInstance.SetComponentProperty("OpenRowset", SourceSchTable);
            
                //Reinitialize the metadata, Refresh Columns
            SourceInstance.AcquireConnections(null);
            SourceInstance.ReinitializeMetaData();
            SourceInstance.ReleaseConnections();

            //Add Multicast 
            MulticastComponent = DataFlowTask.ComponentMetaDataCollection.New();
            MulticastComponent.ComponentClassID = "DTSTransform.Multicast";
            //Get Mulicast Instance
            MulticastInstance = MulticastComponent.Instantiate();
            MulticastInstance.ProvideComponentProperties();
            MulticastComponent.Name = "Mulitcast Source Data";

             //Add Row Count component 
            SourceRowCount = DataFlowTask.ComponentMetaDataCollection.New();
            SourceRowCount.ComponentClassID = "DTSTransform.RowCount";
            rowInstance = SourceRowCount.Instantiate();
            rowInstance.ProvideComponentProperties();
            rowInstance.SetComponentProperty("VariableName", "User::intSourceCnt");
            
            //Connect multicast and Source
            SourceMultiCastPath = DataFlowTask.PathCollection.New();
            SourceMultiCastPath.AttachPathAndPropagateNotifications(SourceComponent.OutputCollection[0], MulticastComponent.InputCollection[0]);

            //Connect the Multicast And Row count
            SourceCntPath = DataFlowTask.PathCollection.New();
            SourceCntPath.AttachPathAndPropagateNotifications(MulticastComponent.OutputCollection[0], SourceRowCount.InputCollection[0]);

            //Add Table Destination

            DestinationComponent = DataFlowTask.ComponentMetaDataCollection.New();
            DestinationComponent.ComponentClassID = "DTSAdapter.OLEDBDestination";
            DestinationComponent.ValidateExternalMetadata = true;

            //Get Destination Instance and initialize component
            DestinationInstance = DestinationComponent.Instantiate();
            DestinationInstance.ProvideComponentProperties();
            DestinationComponent.Name = "ArchiveDestination";
            DestinationComponent.Description =" SQLSat archive Sample";
            
                 //Set Source Connection
            DestinationComponent.RuntimeConnectionCollection[0].ConnectionManagerID = DestinationConMgr.ID;
            DestinationComponent.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface (DestinationConMgr);
            
            //Use straight table transfer
            if (IsFastLoad == false)
            {
                
                DestinationInstance.SetComponentProperty("AccessMode", 0);
            }
            else
            {
                DestinationInstance.SetComponentProperty("AccessMode", 3);
            }
            DestinationInstance.SetComponentProperty("OpenRowset", DestinationSchTable);

            DestinationInstance.SetComponentProperty("FastLoadKeepIdentity", true);
            DestinationInstance.SetComponentProperty("FastLoadMaxInsertCommitSize", FastLoadMaxCommitSize);
            DestinationInstance.SetComponentProperty("FastLoadKeepNulls", false);
            DestinationInstance.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = " + RowsPerBatch);
            DestinationInstance.Validate();
               
           DestinationInstance.AcquireConnections(null);
           DestinationInstance.ReinitializeMetaData();
           DestinationInstance.ReleaseConnections();

               //Add Rowcount and Archive Destination
            DestinationPath = DataFlowTask.PathCollection.New();
            DestinationPath.AttachPathAndPropagateNotifications(SourceRowCount.OutputCollection[0], DestinationComponent.InputCollection[0]);

           //mapping
              
                MappColumns(DestinationComponent,DestinationInstance);
                SavePackage("C:\\Temp");
                if (IsTransferData == true)
                {
                    pkg.Execute();
                   if(pkg.ExecutionResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure || pkg.ExecutionStatus == Microsoft.SqlServer.Dts.Runtime.DTSExecStatus.Abend)
                   {
                    pkg.Name = "Error"+pkg.Name.ToString();
                    SavePackage("C:\\Temp");
                    var["User::intIsPkgFail"].Value = (Int32)1;
                    var["User::intDestCurrCnt"].Value = Failure;
                    var["User::intDestPrevCnt"].Value = Failure;
                    var["User::intSourceCnt"].Value = Failure;

                    Dts.Events.FireError(-1, "Internal Package Error", "Internal Dynamic package Error For ArchiveExportId: " + ArchiveExportId.ToString() + " ErrorMessage:" + GetErrorMessage(pkg), "", 0);
                    }
                }
                var.Unlock();
                pkg.Dispose();

                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch
            {
                pkg.Name = "Error" + pkg.Name.ToString();
                SavePackage("C:\\Temp");

                Dts.TaskResult = (int)ScriptResults.Failure;

            }
           


           
        }

        #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
        private void SavePackage(String DirectoryPath)
        {
            String FullFilePath = DirectoryPath + "\\" + pkg.Name.ToString() + ".dtsx";
            if (File.Exists(FullFilePath))
            {

                File.Delete(FullFilePath);
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                app.SaveToXml(FullFilePath, pkg, null);

            }
            else
            {
                Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
                app.SaveToXml(FullFilePath, pkg, null);

            }
        }
        private String GetErrorMessage(Microsoft.SqlServer.Dts.Runtime.Package p  ) 
        {
            StringBuilder ErrorMessage = new StringBuilder();
            foreach(DtsError Message in p.Errors)
            {
               ErrorMessage.Append(Message.Description.ToString());
            }
            return ErrorMessage.ToString();
        }
       

       

        /*
         * Add OLEDB Connection
         */
        public ConnectionManager AddOLEDBConnection(String ConnectionName, String ConnectionStr)
        {
            ConnectionManager ConMgr = pkg.Connections.Add("OLEDB");

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

            return ConMgr;

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

            IDTSInput100 inpDestination = icmDest.InputCollection[0];
            IDTSVirtualInput100 vinpDestination = inpDestination.GetVirtualInput();
            IDTSVirtualInputColumnCollection100 vinpcDestination = (IDTSVirtualInputColumnCollection100)vinpDestination.VirtualInputColumnCollection;


            foreach (IDTSVirtualInputColumn100 vcolumn in vinpDestination.VirtualInputColumnCollection)
            {
                try
                {
                    if (vcolumn.DataType != DataType.DT_BYTES)
                    {
                        IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_READONLY);
                        // map external column
                        IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];
                        mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);
                    }
                    else
                    {
                        IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);
                    }
                }
                catch
                {
                    IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, DTSUsageType.UT_IGNORED);

                    // map external column

                }

            }

        }
    }
}

SQLSaturday 248 BI Edition Tampa 2013

I have a great time on SQLSaturday 248 Bi edition on Tampa. Jose Chinchilla knows how to be an excellent host, there was plenty of food on both the SQLSaturday Speaker dinner and in the event lunch hours.
Both of my sessions when very smoothly and the this time I have many students for FSU.
Now back to coding....Here is a function that will convert an numeric string into a Comp3 format hex representation:

CREATE FUNCTION [dbo].[udf_Comp3Format]
(
  @NumericStr varchar(50)
  
)
RETURNS varchar (50)
AS
BEGIN
-- Declare the return variable here
Declare @Pos varchar(1)
Declare @Neg varchar(1)
Declare @Unsg varchar (1)
Declare @Sign varchar(1)
Declare @IntVal numeric
declare @value varchar (1024)

Set @Pos = 'C' -- Positive
Set @Neg = 'D' -- Negative 
Set @Unsg = 'F' -- Unsign
if TRY_PARSE (@NumericStr as numeric) is not null
begin  
select @IntVal = parse (@NumericStr as numeric)

--Remove +- sign or - sign 
select @value = ltrim (rtrim (replace (replace (replace (cast (@NumericStr as varchar(50)),'+-','-'),'-',''),'.','')))

--Add Sign 
if (@IntVal < 0)
set @value = @value+@Neg
else
set @value = @value+@Pos

if (len(@value)%2= 1)
set @value = '0'+@value
end 
      else
   set @value = NULL

RETURN @value

END