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

}
}

June 19, 2013

Dynamic Fixed Width Connection Part III Add Custom component



/*

   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 RunTimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Pipeline;
using Comp3 = CustomComponents.UnpackDecimalComponent;
using PipeLineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;


namespace ST_394d7e0c32ff40af81e953483ef3c2cf.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.
*/
        Package _pgk = new Package();
        Application _app = new Application();
        String SavePath = "";
        public void Main()
        {
            Variables vars = null;
            Dts.VariableDispenser.LockForRead("User::strTabListCmd");
            Dts.VariableDispenser.LockForRead("User::strSqlCmdFilter");
            Dts.VariableDispenser.LockForRead("User::strColumnCmd");
            Dts.VariableDispenser.LockForRead("User::strSavePath");
            Dts.VariableDispenser.LockForRead("User::strFileDirectory");
            Dts.VariableDispenser.GetVariables(ref vars);

            try
            {

                String CmdTabList = vars["User::strTabListCmd"].Value.ToString();
                String CmdFilter = vars["User::strSqlCmdFilter"].Value.ToString();
                String CmdColumns = vars["User::strColumnCmd"].Value.ToString();
                String FileDirectory = vars["User::strFileDirectory"].Value.ToString();
                 SavePath = vars["User::strSavePath"].Value.ToString();

                String ServerName = @"JNOVO";
                String DatabaseName = @"B";
                String ConnectionString = "Server=" + ServerName + ";Database=" + DatabaseName + ";Trusted_Connection=True;";
                
                DataTable _DataTable = ExecuteCommand(ConnectionString, CmdTabList);
                int CntRows = _DataTable.Rows.Count;
                foreach (DataRow row in _DataTable.Rows)
                {
                    String FileName = (String)row["FileName"];
                    String TabName = (String)row["TabName"];
                    String Filter = CmdFilter;
                    String Fiter2 = Filter.Replace("<<Filter>>", TabName);
                    StringBuilder NewCmd = new StringBuilder();
                    PipeLineWrapper.IDTSPath100 SourceToDecimal;
                    NewCmd.Append(CmdColumns);
                    NewCmd.Append(Fiter2);
                   
                    DataTable connColumns = ExecuteCommand(ConnectionString, NewCmd.ToString());
                    ConnectionManager _ConnMgr = AddFixedWith(connColumns, TabName, FileDirectory + "\\" + FileName + ".DAT");
                    PipeLineWrapper.MainPipe dataflow = AddWorkFlowComponent(TabName);
                    PipeLineWrapper.IDTSComponentMetaData100 FileSource = AddFileSourceComponent(dataflow, _ConnMgr, TabName);
                    PipeLineWrapper.IDTSComponentMetaData100 UnpackDec =  AddUnpackDecimal(dataflow);
                    SourceToDecimal = dataflow.PathCollection.New();
                    SourceToDecimal.AttachPathAndPropagateNotifications(FileSource.OutputCollection[0], UnpackDec.InputCollection[0]);
                    SetFlatFileProperty(FileSource);
                    MappDecimalColumns(UnpackDec);

                   

                   // MappDecimals(UnpackDec);

                    connColumns = null;
                    NewCmd = null;
                    Filter = null;
                    TabName = null;
                    Fiter2 = null;
                  
                }

                _app.SaveToXml(SavePath + "test04.dtsx", _pgk, null);
                //Dispose of table 
                // TODO: Add your code here
                vars.Unlock();
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (Exception e)
            {
                
                vars.Unlock();
                Console.WriteLine(e.ToString());
                _app.SaveToXml(SavePath + "test04.dtsx", _pgk, null);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }
        }
       

        private void MappDecimalColumns(PipeLineWrapper.IDTSComponentMetaData100 PackedDecimal)
        {
            PipeLineWrapper.IDTSInputCollection100 inputCollection = PackedDecimal.InputCollection;
            PipeLineWrapper.IDTSInput100 input = inputCollection[0];
            PipeLineWrapper.IDTSVirtualInput100 vinput = input.GetVirtualInput();
            PipeLineWrapper.IDTSVirtualInputColumnCollection100 vInputColumns = vinput.VirtualInputColumnCollection;
            PipeLineWrapper.CManagedComponentWrapper InstanceUnpack = PackedDecimal.Instantiate();
            //InstanceUnpack.ProvideComponentProperties();
            //InstanceUnpack.ReleaseConnections();

            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);
                    inputColumn.CustomPropertyCollection["PackedScale"].Value = 2;
                    PipeLineWrapper.IDTSOutputCollection100 outputCollection = PackedDecimal.OutputCollection;
                    PipeLineWrapper.IDTSOutput100 output = outputCollection[0];
                    PipeLineWrapper.IDTSOutputColumnCollection100 outputcolumncollection = output.OutputColumnCollection;
                    PipeLineWrapper.IDTSOutputColumn100 outcol = output.OutputColumnCollection[inputColumn.Name];
                }
            }


        }

        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;
                    //PipeLineWrapper.IDTSInput100 decInput = inputCollection.New();
                    //PipeLineWrapper.IDTSInputColumn100 inputCol = decInput.InputColumnCollection.New();
                    //inputCol.Name = col.Name;
                    //inputCol.ExternalMetadataColumnID = col.ExternalMetadataColumnID;
                    //inputCol.LineageID = col.LineageID;
                    
                    //inputCol.CustomPropertyCollection["PackedScale"].Value = "0";
                   
                    
                                                 
                }
            }
        }
       
        private PipeLineWrapper.IDTSComponentMetaData100 AddUnpackDecimal(PipeLineWrapper.MainPipe dataFlowTask)
        {
            //Comp3 unpk = new Comp3();
            //PipeLineWrapper.IDTSComponentMetaData100 mpk = unpk.ComponentMetaData;
            PipeLineWrapper.IDTSComponentMetaData100 mpk = dataFlowTask.ComponentMetaDataCollection.New();

            mpk.ComponentClassID = typeof(CustomComponents.UnpackDecimalComponent).AssemblyQualifiedName;
            //mpk.ComponentClassID = typeof(Comp3).AssemblyQualifiedName;
            mpk.Name = "UnpackDecimalTest";
            PipeLineWrapper.CManagedComponentWrapper instanceFileSource = mpk.Instantiate();
            instanceFileSource.ProvideComponentProperties();

            return mpk;

        }
        private void Reinitiaze(PipeLineWrapper.CManagedComponentWrapper InstanceSource)
        {
            //Reinitialize Flat File source metadata, 
            InstanceSource.AcquireConnections(null);
            InstanceSource.ReinitializeMetaData();
            InstanceSource.ReleaseConnections();
        }
        private DataTable ExecuteCommand(String ConnectionString, String qrty)
        {
            SqlConnection BDEConnection = new SqlConnection(ConnectionString);
            DataTable rtData = new DataTable();
            BDEConnection.Open();
            SqlCommand  SqlCmd = new SqlCommand(qrty,BDEConnection);
            SqlDataReader DtReader = SqlCmd.ExecuteReader();
            rtData.Load(DtReader);
            BDEConnection.Close();
            return rtData;
        }
      
        private ConnectionManager AddFixedWith(DataTable dtColumns, String ConnectionName, String ConnectionString)
        {
            ConnectionManager flatConn = _pgk.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"];
                int Scale = (int)row["Scale"];
                switch (Scale)
                {
                    case -1:
                    FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_WSTR;
                    break;
                    default:
                      FlatFileColumn100.DataType = RunTimeWrapper.DataType.DT_BYTES;
                    break;
                    


                }
                          
                RunTimeWrapper.IDTSName100 ColName = (RunTimeWrapper.IDTSName100)FlatFileColumn100;
                ColName.Name = (String) row["FieldLevelName"];
               
            }
            return flatConn;
        }
        private PipeLineWrapper.MainPipe AddWorkFlowComponent(String ComponentName)
        {
            _pgk.Executables.Add("STOCK:PipelineTask");
            TaskHost _TaskHost = (TaskHost)_pgk.Executables[0];
            PipeLineWrapper.MainPipe dataFlowTask = (PipeLineWrapper.MainPipe)_TaskHost.InnerObject;
            _TaskHost.Name = ComponentName;
            _TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");

            return dataFlowTask;

        }
        
        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.2";
            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;
        }
     

    }
}

Contact Form

Name

Email *

Message *