August 08, 2014

More fun loading file(s)

#region Help:  Introduction to the script task
/* Jorge Novo 
 * 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.SqlClient;
using System.Data.Common;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using TxBDD;
using SQLTask = Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask;
using Runtime = Microsoft.SqlServer.Dts.Runtime;
using Pipeline = Microsoft.SqlServer.Dts.Pipeline;
using RuntimeWrapper = Microsoft.SqlServer.Dts.Runtime.Wrapper;
using PipelineWrapper = Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using System.Text;
using System.IO;
using System.Xml;
#endregion

namespace ST_a521b7186dae425b959c1d4e8c6625d2
{
    /// <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 Variables
        Package ssis = new Package();
        Runtime.Application app = new Runtime.Application();
        Variables vars = null;

        public void Main()
        {
            try
            {

                


                //External Parent Variables
                Dts.VariableDispenser.LockForRead("User::strArchiveDirectory");
                Dts.VariableDispenser.LockForRead("User::strImportDirectory");
                Dts.VariableDispenser.LockForRead("User::strLoadDirectory");
                Dts.VariableDispenser.LockForRead("User::strSsisName");
                Dts.VariableDispenser.LockForRead("User::strBatchId");
                Dts.VariableDispenser.LockForRead("User::strDestConnStr");
                Dts.VariableDispenser.LockForRead("User::strDestSchema");
                Dts.VariableDispenser.LockForRead("User::strDestTableName");
                Dts.VariableDispenser.LockForRead("User::strDestServerName");
                Dts.VariableDispenser.LockForRead("User::strFileCodePage");
                Dts.VariableDispenser.LockForRead("User::strFileName");
                Dts.VariableDispenser.LockForRead("User::strFilePrefix");
                Dts.VariableDispenser.LockForRead("User::strFileSubfix");
                Dts.VariableDispenser.LockForRead("User::strSymbol");
                Dts.VariableDispenser.LockForRead("User::strFileExtension");
                Dts.VariableDispenser.LockForWrite("User::bolIsLoaded");
                Dts.VariableDispenser.LockForRead("User::intLFNFileId");
                Dts.VariableDispenser.LockForWrite("User::intRecordCount");
                Dts.VariableDispenser.LockForWrite("User::strErrorMessage");
                Dts.VariableDispenser.LockForRead("User::strStoreXmlCmd");
                Dts.VariableDispenser.LockForRead("User::intETLLogId");
                Dts.VariableDispenser.GetVariables(ref vars);

                //Local Variables
                String Archive = vars["User::strArchiveDirectory"].Value.ToString();
                String Load = vars["User::strLoadDirectory"].Value.ToString();
                String Import = vars["User::strImportDirectory"].Value.ToString();
                String SsisName = vars["User::strSsisName"].Value.ToString();
                String BatchId = vars["User::strBatchId"].Value.ToString();
                String DestConnectionStr = vars["User::strDestConnStr"].Value.ToString();
                String TableSchema = vars["User::strDestSchema"].Value.ToString();
                String TableName = vars["User::strDestTableName"].Value.ToString();
                String DestServer = vars["User::strDestServerName"].Value.ToString();
                String CodePage = vars["User::strFileCodePage"].Value.ToString();
                String FileName = vars["User::strFileName"].Value.ToString();
                String Prefix = vars["User::strFilePrefix"].Value.ToString().Trim();
                String SubFix = vars["User::strFileSubfix"].Value.ToString().Trim();
                String FileDelimite = vars["User::strSymbol"].Value.ToString().Trim();
                String FileExtension = "." + vars["User::strFileExtension"].Value.ToString().Trim();
                String TruncateCmd = "Truncate Table " + TableSchema + "." + TableName;
                String FillTableName = TableSchema + "." + TableName;

                int LFNFileId =(int) vars["User::intLFNFileId"].Value;

                //Compose Variables
                StringBuilder FilePattern = new StringBuilder();


                FilePattern.Append(Prefix);
                FilePattern.Append(FileName);
                FilePattern.Append(SubFix);
                FilePattern.Append(FileExtension);
                String xFullFilePath = GetFileName(Load, FilePattern.ToString());


                //Assign relevant package name and description
                ssis.Name = SsisName;
                ssis.Description = "Load Process of initial File";

                //Package Variables
                Runtime.Variable intRowCount = ssis.Variables.Add("intRowCount", false, "User", 0);
                Runtime.Variable strBatchId = ssis.Variables.Add("strBatchId", false, "User", BatchId);
                Runtime.Variable intLFNFileId = ssis.Variables.Add("intLfnFileId", false, "User", LFNFileId);
               

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

                AddLogging(msdb.Name, true);


                //Add Sequence Container 
                Executable execSequence = AddSQLExecutable("STOCK:Sequence");
                Runtime.Sequence rtSequence = (Runtime.Sequence)execSequence;

                //Sequence Properties
                rtSequence.Name = "File2FillTableImport";
                rtSequence.LoggingMode = Runtime.DTSLoggingMode.UseParentSetting;
                rtSequence.Description = "EIM Import Prototype";

                // Add SQLTask Component 
                Executable execFillRawSqlTask = rtSequence.Executables.Add("STOCK:SQLTASk");
                //Wrap the executable in a TaskHost 
                AddSQLTask(execFillRawSqlTask, "Truncate Fill Table", destination.Name.ToString(), TruncateCmd);



                //Add DataFlow
                Executable execRawDataFlow = rtSequence.Executables.Add("STOCK:PipelineTask");
                Microsoft.SqlServer.Dts.Runtime.TaskHost thRawDataFlow = (Microsoft.SqlServer.Dts.Runtime.TaskHost)execRawDataFlow;
                thRawDataFlow.Name = "RawFile2FillTable";
                PipelineWrapper.MainPipe mDataFlow = (PipelineWrapper.MainPipe)thRawDataFlow.InnerObject;

                // Add Precedence Constraint between SQLTask and workflow
                Runtime.PrecedenceConstraint SqlTask2WorkFlow = rtSequence.PrecedenceConstraints.Add(execFillRawSqlTask, execRawDataFlow);
                SqlTask2WorkFlow.Value = Runtime.DTSExecResult.Success;



                //Insert Flat File Connection
                ConnectionManager ConnMgrFlatFile = ssis.Connections.Add("FLATFILE");
                //You can change this path depending on where you have stored the flat file
                ConnMgrFlatFile.ConnectionString = xFullFilePath;// Dts.Connections["FlatFile"].ConnectionString;
                //Assign name to the flat file connection
                ConnMgrFlatFile.Name = FileName;
                //Indicate that the flat file is delimited
                ConnMgrFlatFile.Properties["Format"].SetValue(ConnMgrFlatFile, "Delimited");
                //Indicate whether the source file has column headings or not - in this case, our sample data has column headings.
                ConnMgrFlatFile.Properties["ColumnNamesInFirstDataRow"].SetValue(ConnMgrFlatFile, Convert.ToBoolean(true));
                ConnMgrFlatFile.Properties["CodePage"].SetValue(ConnMgrFlatFile, Convert.ToInt32(CodePage));

                //Get Native Flat File Connection
                RuntimeWrapper.IDTSConnectionManagerFlatFile100 rtwConnFlatFile = ConnMgrFlatFile.InnerObject as RuntimeWrapper.IDTSConnectionManagerFlatFile100;
                AddFlatFileColumns(ref rtwConnFlatFile, xFullFilePath, FileDelimite);

                //Add FlatFile Source Component
                //Insert Flat File source component
                PipelineWrapper.IDTSComponentMetaData100 componentSource = mDataFlow.ComponentMetaDataCollection.New();
                componentSource.Name = "FlatFileSource";
                componentSource.ComponentClassID = "{D23FD76B-F51D-420F-BBCB-19CBF6AC1AB4}";
                //Insert source design-time instance and initialise component
                PipelineWrapper.CManagedComponentWrapper instanceSource = componentSource.Instantiate();
                instanceSource.ProvideComponentProperties();
                //Set source connection
                componentSource.RuntimeConnectionCollection[0].ConnectionManagerID = ConnMgrFlatFile.ID;
                componentSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(ConnMgrFlatFile);


                //Reinitialize Flat File source metadata, 
                Reinitiaze(instanceSource);

                //Add RowCount Component
                PipelineWrapper.IDTSComponentMetaData100 icmdRowCount = AddComponentMetadata(mDataFlow, "RowCountComponent");
                icmdRowCount.ComponentClassID = "DTSTransform.RowCount";
                PipelineWrapper.CManagedComponentWrapper mcwRowCount = icmdRowCount.Instantiate();
                mcwRowCount.ProvideComponentProperties();
                icmdRowCount.Name = "TotalRecordCount";
                mcwRowCount.SetComponentProperty("VariableName", "User::intRowCount");

                //Join Path 
                PipelineWrapper.IDTSPath100 SourceToCount;

                SourceToCount = mDataFlow.PathCollection.New();
                SourceToCount.AttachPathAndPropagateNotifications(componentSource.OutputCollection[0], icmdRowCount.InputCollection[0]);
                Reinitiaze(mcwRowCount);

                //Add Paralle Distributor 
                PipelineWrapper.IDTSComponentMetaData100 icmdParallel = AddComponentMetadata(mDataFlow, "ParallelDistribution");
                icmdParallel.ComponentClassID = "{F06C606C-5980-460C-B2AF-B0ECA031B7A4}";
                PipelineWrapper.CManagedComponentWrapper mcwParallel = icmdParallel.Instantiate();
                mcwParallel.ProvideComponentProperties();


                PipelineWrapper.IDTSPath100 CountToDistributor;
                CountToDistributor = mDataFlow.PathCollection.New();
                CountToDistributor.AttachPathAndPropagateNotifications(icmdRowCount.OutputCollection[0], icmdParallel.InputCollection[0]);
                Reinitiaze(mcwParallel);




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

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

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

                PipelineWrapper.CManagedComponentWrapper mcwRawDestination2 = icmRawDestination2.Instantiate();
                // tblDestination
                mcwRawDestination2.ProvideComponentProperties();
                icmRawDestination2.Name = "destination2";
                icmRawDestination2.Description = "destination2";
                icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManagerID = destination.ID;
                icmRawDestination2.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(destination);
                mcwRawDestination2.SetComponentProperty("AccessMode", 3);//FastLoad
                mcwRawDestination2.SetComponentProperty("OpenRowset", FillTableName);//tblDestination);
                mcwRawDestination2.Validate();
                mcwRawDestination2.SetComponentProperty("FastLoadKeepIdentity", true);
                mcwRawDestination2.SetComponentProperty("FastLoadMaxInsertCommitSize", 10000);//FastLoadMaxCommitSize);
                mcwRawDestination2.SetComponentProperty("FastLoadKeepNulls", false);
                mcwRawDestination2.SetComponentProperty("FastLoadOptions", "TABLOCK,CHECK_CONSTRAINTS,ROWS_PER_BATCH = 50000");// + RowsPerBatch);
                Reinitiaze(mcwRawDestination2);
                //////////////////////////////////////////////////////
                ///////////////////////////////////////////////////////
                //Add OLEDB Destination Componnent
                PipelineWrapper.IDTSComponentMetaData100 icmRawDestination3 = AddComponentMetadata(mDataFlow, "destination");
                icmRawDestination3.ComponentClassID = "DTSAdapter.OLEDBDestination";
                icmRawDestination3.ValidateExternalMetadata = true;

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

                //join Source
                //TableLoading joins

                PipelineWrapper.IDTSPath100 CountToRawTable;
                CountToRawTable = mDataFlow.PathCollection.New();
                CountToRawTable.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[0], icmRawDestination.InputCollection[0]);

                PipelineWrapper.IDTSPath100 Distributor2Table;
                Distributor2Table = mDataFlow.PathCollection.New();
                Distributor2Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[1], icmRawDestination2.InputCollection[0]);

                PipelineWrapper.IDTSPath100 Distributor3Table;
                Distributor3Table = mDataFlow.PathCollection.New();
                Distributor3Table.AttachPathAndPropagateNotifications(icmdParallel.OutputCollection[2], icmRawDestination3.InputCollection[0]);


                MappColumns(icmRawDestination, mcwRawDestination);
                MappColumns(icmRawDestination2, mcwRawDestination2);
                MappColumns(icmRawDestination3, mcwRawDestination3);

                // Execute package

                SaveSSIS(@"\\c:\temp", SsisName);
                StoreSsisPackage(host);
                ssis.Execute();
                if ((ssis.ExecutionResult == DTSExecResult.Failure) || (ssis.ExecutionStatus == DTSExecStatus.Abend))
                {
                    StringBuilder ErrorMessage = new StringBuilder();


                    // Package Failure on Error 
                    vars["User::bolIsLoaded"].Value = false;
                    //Capture Error message 
                    foreach (Microsoft.SqlServer.Dts.Runtime.DtsError Error in ssis.Errors)
                    {
                        String ErrorDesc = Error.Source + ":" + Error.Description;
                        ErrorMessage.Append(ErrorDesc);
                        ErrorDesc = null;

                    }
                    String eMessage = ErrorMessage.ToString();
                    vars["User::ErrorMessage"].Value = eMessage;
                    ErrorMessage = null;
                    throw new System.ArgumentException("Loading RawData File Error ", eMessage);




                }
                else
                {
                    vars["User::intRecordCount"].Value = intRowCount.Value;
                    //vars["User::bolIsLoaded"].Value = true;
                    // SaveSSIS(SaveDirectory, myPackage.Name.ToString());
                    vars.Unlock();
                    FilePattern = null;
                    ssis.Dispose();


                    Dts.TaskResult = (int)ScriptResults.Success;
                }

            }
            catch (ArgumentException e)
            {
                // MessageBox.Show(e.Message.ToString());

              //  StoreSsisPackage(host);
                ssis.Name = "Error" + ssis.Name.ToString();
                
                SaveSSIS(@"\\c\temp\ssis", ssis.Name.ToString());
                ssis.Dispose();
                throw e;


            }
           
           
        }
        // save XML package into errorlog table
        private void StoreSsisPackage(ConnectionManager host)
        {
            int LogId = (int)vars["User::intETLLogId"].Value;
            StringBuilder strbSQLCmd = new StringBuilder();
            XmlDocument xlmdoc = new XmlDocument();
            String ServerName = (String)host.Properties["ServerName"].GetValue(host);
            String DatabaseName = (string)host.Properties["InitialCatalog"].GetValue(host);
            String StoreProc = "Log.SsisStorage_InsUpd";

            ssis.SaveToXML(ref xlmdoc, null, null);
            strbSQLCmd.Append("Server=");
            strbSQLCmd.Append(ServerName);
            strbSQLCmd.Append(";Database=");
            strbSQLCmd.Append(DatabaseName);
            strbSQLCmd.Append(";Trusted_Connection=TRUE");

            SqlConnection sqlCon = new SqlConnection(strbSQLCmd.ToString());
            using (DbCommand Cmd = sqlCon.CreateCommand())
            {
                Cmd.CommandType =  System.Data.CommandType.StoredProcedure;
                Cmd.CommandText = StoreProc;
                Cmd.Parameters.Add(new SqlParameter("@ETLLogId", System.Data.SqlDbType.Int)
                {
                    Value = LogId
                });
                Cmd.Parameters.Add(new SqlParameter("@PackageName", System.Data.SqlDbType.VarChar)
                {
                    Value = ssis.Name.ToString()
                });

                Cmd.Parameters.Add(new SqlParameter("@pkg", System.Data.SqlDbType.Xml)
                {
                   Value = new System.Data.SqlTypes.SqlXml(new XmlTextReader(xlmdoc.InnerXml,XmlNodeType.Document,null))
                });
                sqlCon.Open();
                DbTransaction tran = sqlCon.BeginTransaction();
                Cmd.Transaction = tran;
                try
                {
                    String strCmd = Cmd.CommandText.ToString();
                    Cmd.ExecuteNonQuery();

                    tran.Commit();
                    sqlCon.Close();
                }
                catch (Exception e)
                {
                   
                   MessageBox.Show(e.ToString());
                    tran.Rollback();
                    sqlCon.Close();
                    throw;
                }
            }
                                 

        }

        /*
     *  Add Destinatin Componnet
     */
        private PipelineWrapper.IDTSComponentMetaData100 AddComponentMetadata(PipelineWrapper.MainPipe wf, String ComponentName)
        {
            PipelineWrapper.IDTSComponentMetaData100 icmd = wf.ComponentMetaDataCollection.New();
            return icmd;
        }
        private void AddFlatFileColumns(ref RuntimeWrapper.IDTSConnectionManagerFlatFile100 ConnMgrFlatFile, String FullFilePath, String FileDelimited)
        {
            String line;
            //Determine the number of columns by reading the sample Flat File - line by line.            
            using (StreamReader file = new StreamReader(FullFilePath))
            {
                try
                {

                    while ((line = file.ReadLine()) != null)
                    {
                        char[] delimiters = FileDelimited.ToCharArray();
                        string[] parts = line.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

                        for (int i = 0; i < parts.Length; i++)
                        {
                            RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol = ConnMgrFlatFile.Columns.Add() as RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100;
                            sS_AssignColumnProperties(flatFileCol, parts[i], FileDelimited);
                        }
                        //Exit file after reading the first line
                        break;
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    file.Close();
                }
            }
            //Edit the last Flat File column delimiter into NewLine instead of a Comma
            ConnMgrFlatFile.Columns[ConnMgrFlatFile.Columns.Count - 1].ColumnDelimiter = Environment.NewLine;

        }
        private static void sS_AssignColumnProperties(RuntimeWrapper.IDTSConnectionManagerFlatFileColumn100 flatFileCol, string getColName, string getDelim)
        {
            //Assign delimiter
            flatFileCol.ColumnType = "Delimited";
            flatFileCol.ColumnDelimiter = getDelim;
            

            //Indicate column data type - in this case, all the source columns will be set to String Data Type
            flatFileCol.DataType = RuntimeWrapper.DataType.DT_STR;
            //Indicate column width - in this case, width of all source columns will be set to a length of 800
            flatFileCol.ColumnWidth = 8000;
            flatFileCol.MaximumWidth = 8000;
           
            

            //Assign column name
            RuntimeWrapper.IDTSName100 columnName = flatFileCol as RuntimeWrapper.IDTSName100;
            columnName.Name = getColName.ToString();
        }
        //Save Ssis package to a location
        private void SaveSSIS(String Directory, String PackageName)
        {
            String FullFilePath = Directory + "\\" + PackageName + ".dtsx";
           app.SaveToXml(FullFilePath, ssis, null);
        }

         private void AddLogging(String ConnectionName, Boolean Enable)
        {
            LogProvider pkLogging;
            pkLogging = ssis.LogProviders.Add("DTS.LogProviderSQLServer");
            pkLogging.Name = "Log Provider For SQL SERVER";
            pkLogging.Description = "Log Provider For SQL SERVER";
            pkLogging.ConfigString = ConnectionName;
            ssis.LoggingOptions.SelectedLogProviders.Add(pkLogging);
            ssis.LoggingOptions.EventFilterKind = Microsoft.SqlServer.Dts.Runtime.DTSEventFilterKind.Inclusion;
            ssis.LoggingOptions.EventFilter = new String[] { "OnPreExecute", "OnPostExecute", "OnError" };
            switch (Enable)
            {
                case true:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Enabled;
                    break;
                case false:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;
                default:
                    ssis.LoggingMode = Microsoft.SqlServer.Dts.Runtime.DTSLoggingMode.Disabled;
                    break;

            }
        }
        /*
         * 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 = ssis.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)
        {
            ssis.Executables.Add("STOCK:PipelineTask");
            TaskHost _TaskHost = (TaskHost)ssis.Executables[0];
            PipelineWrapper.MainPipe dataFlowTask = (PipelineWrapper.MainPipe)_TaskHost.InnerObject;
            _TaskHost.Name = ComponentName;
            _TaskHost.Properties["DefaultBufferMaxRows"].SetValue(_TaskHost, "1000000");

            return dataFlowTask;

        }
     
        // Create SSIS executable
        private Executable AddSQLExecutable(String MONIKER)
        {
            Executable exec = ssis.Executables.Add(MONIKER);

            return exec;
        }

        /* need to work on this if more than one file then error or what to do*/
        private String GetFileName(String DirectoryPath, String NamePattern)
        {
            String[] Files = System.IO.Directory.GetFiles(DirectoryPath, NamePattern);
            String FullPath = "";
            foreach (String file in Files)
            {
               FullPath = System.IO.Path.GetFullPath(file);
            }
            return FullPath;
        }
        private void AddSQLTask(Executable exec, String TaskName, String Connection, String SqlCmd)
        {
            Runtime.TaskHost sqlTaskHost = (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);

            SQLTask.IDTSExecuteSQL iexecFillRawSqlTask = (SQLTask.IDTSExecuteSQL)sqlTaskHost.InnerObject;
            iexecFillRawSqlTask.ResultSetType = SQLTask.ResultSetType.ResultSetType_None;
            iexecFillRawSqlTask.SqlStatementSourceType = SQLTask.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
                {
                    PipelineWrapper.IDTSInputColumn100 inputColumn = mcwDest.SetUsageType(inpDestination.ID, vinpDestination, vcolumn.LineageID, PipelineWrapper.DTSUsageType.UT_READONLY);
                    // map external column
                    // inputColumn.Name = inputColumn.Name.Replace("[", "").Replace("]", "");
                    PipelineWrapper.IDTSExternalMetadataColumn100 extColumn = inpDestination.ExternalMetadataColumnCollection[inputColumn.Name];

                    mcwDest.MapInputColumn(inpDestination.ID, inputColumn.ID, extColumn.ID);

                }
                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

}
}

May 24, 2014

Developing Android Applications

Lately I spend a considered amount of time learning to program in Android and I have been a few applications that now I thinking about publishing in the Google Market. Also, I have to cancel my SQLSaturday in South Florida because of reasons out of my control.
Cheers.

May 20, 2014

Scary times at work

Lately there have been a lot of uncertain at work, many coworkers have been let go, one day they are here and then all you see is an empty cube without a name, is funny we spend most of our lives away from our homes in an environment surrendered by other people but there is not interaction just silos, images, dust and shadows. Friends comes and go, time waits for no one.

Contact Form

Name

Email *

Message *