June 26, 2013

Dynamic Fixed Width Connection Part IV



After a constant change and continue the development of this new process
here is the base line used to convert comp3 fields.
The link to the component is below:
http://www.microsoft.com/en-us/download/details.aspx?id=20397
however I did add the changes to the component according to what was missing and upgraded the code to MS SQL 2012 :-)



#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.UnpackDecimalComponent;
using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using ScriptTask = Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using Microsoft.CSharp.RuntimeBinder;

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();
        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"); 
                //Get variable collector
                Dts.VariableDispenser.GetVariables(ref vars);

                BDELoaderId = (int)vars["User::intBDELoaderID"].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);

                
                
                //Truncate table Command Variable
                StringBuilder TruncateCommand = new StringBuilder();
                TruncateCommand.Append(vars["User::strSqlCmd"].Value.ToString() + " ");
                TruncateCommand.Append("[Load]." + vars["User::strTableName"].Value.ToString());

                // Set Package Name 
                myPackage.Name = "SssisLoad2Master" + vars["User::strTabName"].Value.ToString();
                String LoadDirectory = vars["User::strLoadDirectory"].Value.ToString();
                SaveDirectory = vars["User::strSaveDirectory"].Value.ToString();
                String LoadTableName = "[Load]." + vars["User::strTableName"].Value.ToString();
                String FileName = vars["User::strFileName"].Value.ToString();
                String FlatFileConnectionString = LoadDirectory + "\\" + FileName;
                String TabName = vars["User::strTabName"].Value.ToString();
                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 SourceToCount;
                PipelineWrapper.IDTSPath100 CountToDecimal;
                PipelineWrapper.IDTSPath100 DecimalToDestination;
             


                // 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(), FlatFileConnectionString);
                GenerateInsert(MasterColumnInfo);
                String MasterToOracle = Master2Oracle.ToString();

                //Create OLEDB Connection
                ConnectionManager ConMgr = AddOLEDBConnection("destination", Dts.Connections["host"].ConnectionString.ToString());
                ConnectionManager HOST = AddOLEDBConnection("host", Dts.Connections["host"].ConnectionString.ToString());
                ConnectionManager MSDB = AddOLEDBConnection("msdb", Dts.Connections["msdb"].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
                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);
                //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 Unpack Decimal Custom Component
                PipelineWrapper.IDTSComponentMetaData100 UnpackDecimal = AddUnpackDecimal(DataFlow);
                UnpackDecimal.Name = "UnPackComp3Fields";
                PipelineWrapper.CManagedComponentWrapper cmwUnpackDecimal = UnpackDecimal.Instantiate();
                cmwUnpackDecimal.ProvideComponentProperties();


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

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

                //Join Source To UnpackDecimal Component

                SourceToCount = DataFlow.PathCollection.New();
                SourceToCount.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
                CountToDecimal = DataFlow.PathCollection.New();
                CountToDecimal.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], UnpackDecimal.InputCollection[0]);


                //Set Properties for Bynary Fields
                SetFlatFileProperty(FileSource);
                SelectDecimalColumns(UnpackDecimal);
                SetUnpackDecimalScale(UnpackDecimal, ColumnInfo);
                //Join Unpack Decimal to Destination
                DecimalToDestination = DataFlow.PathCollection.New();
                DecimalToDestination.AttachPathAndPropagateNotifications(UnpackDecimal.OutputCollection[0], icmDestination.InputCollection[0]);
                MappColumns(icmDestination, mcwDestination);


                // myPackage.Execute();

                vars["User::intRecordCount"].Value = intFileRowCount.Value;
                if ((myPackage.ExecutionResult == 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());
                    // TODO: Add your code here

                    vars.Unlock();
                    //ColumnInfo = null;
                    Dts.TaskResult = (int)ScriptResults.Success;
                }
            }
            catch
            {
                myPackage.Name = "Error" + myPackage.Name.ToString();
                SaveSSIS(SaveDirectory, myPackage.Name.ToString());
                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"];
                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.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;
                                }

                            }


                        }
                    }

                }

                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;
                    col.Name = "pkg" + col.Name.ToString();


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

            return dt;

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

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

            return ConMgr;

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

            return dataFlowTask;

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

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

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

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


        }
        //Generate Insert From Load to Master

        /*
         * Create Fixed Width Column Connection Manager
         */
        private ConnectionManager AddFixedWith(DataTable dtColumns, String ConnectionName, String ConnectionString)
        {
            //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;
            flatConn.ConnectionString = ConnectionString;
            flatConn.Properties["Format"].SetValue(flatConn, "FixedWidth");
            flatConn.Properties["CodePage"].SetValue(flatConn, "37");
            flatConn.Properties["DataRowsToSkip"].SetValue(flatConn, 1);
            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)
            {
                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"];
                switch (FieldLocation)
                {
                    case 1:
                        LoadMasterCmd.Append("[BDELoaderId],[" + (String)row["FieldLevelName"] + "]");
                        Load2MasterCmd.Append(Environment.NewLine);
                        break;
                    default:
                        LoadMasterCmd.Append(",[" + (String)row["FieldLevelName"] + "]");
                        Load2MasterCmd.Append(Environment.NewLine);
                        break;
                }


                int Scale = (int)row["Scale"];
                switch (Scale)
                {
                    case -1:
                        FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_WSTR;
                        ColName.Name = (String)row["FieldLevelName"];
                        break;
                    default:
                        FlatFileColumn100.DataType = RuntimeWrapper.DataType.DT_BYTES;
                        ColName.Name = (String)row["FieldLevelName"];
                        break;
                }




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

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

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

            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)
        {
            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 (PipelineWrapper.IDTSVirtualInputColumn100 virtualInputColumn in vInputColumns)
            {
                if (virtualInputColumn.DataType == RuntimeWrapper.DataType.DT_BYTES)
                {
                    // 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

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


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

                    // map external column

                }

            }


        }

       


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

}
}

No comments:

Post a Comment

Contact Form

Name

Email *

Message *