Programatically invoking multiple instances of SSIS package to execute them in parallel

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.IO;
using System.Data.OleDb;
using System.Threading;
using System.Collections.Generic;

 public void Main()
        {
           
            var totalInstances = Convert.ToInt32(Dts.Variables["User::ParallelExecutionCount"].Value);
            var RowIteratorPkg = Dts.Variables["User::RowIteratorFilePath"].Value.ToString();

            try
            {
                if (File.Exists(RowIteratorPkg))
                {
                    var taskList = new System.Threading.Tasks.Task[totalInstances];

                    for (var i = 0; i < totalInstances; i++)
                    {
                        int runningInstance = i;
                        taskList[i] = System.Threading.Tasks.Task.Factory.StartNew(() => TriggerNewPackageInstance(RowIteratorPkg, runningInstance, totalInstances));
                    }

                    System.Threading.Tasks.Task.WaitAll(taskList);
                Dts.TaskResult = (int)ScriptResults.Success;
            }
                else
                {
                    logException(999, "RowIterator package does not exist in the configured location: " + RowIteratorPkg);
                    Dts.TaskResult = (int)ScriptResults.Failure;
                }
            }
            catch (Exception ex)
            {
                logException(999, "Exception in executing the RowIterator package: " + ex.Message);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }
        }

        private void TriggerNewPackageInstance(string RowIteratorPkg, Int32 runningInstance, Int32 totalInstances)
        {
            try
            {
            var App = new Application();
            var pkg = new Package();
            pkg = App.LoadPackage(RowIteratorPkg, null);
                pkg.Variables["PackageInstanceNumber"].Value = (Int32)runningInstance;
                pkg.Variables["ParallelInstances"].Value = (Int32)totalInstances;
                pkg.Variables["DBConString"].Value = DbConnString;
            pkg.Execute();
        }
            catch (Exception ex)
            {
                logException(999, "Exception in executing the package: " + ex.Message + " Running Instance: " + runningInstance.ToString() + "totalInstance : " + totalInstances.ToString());
            }
        }

Possible issues for a script task to get stuck in In-progress status

Possible Issues:

1. For Ex: EmpID is declared as an Integer variable in the SSIS Package.

While writing value to this variable with in Script Task, do make sure to explicitly type case the input like below

Dts.Variables["User::EmpID"].Value =  (int) 0;

more content .. in soon

How to handle Null assignment to a column in the Output buffer in SSIS script component when used as Source or Transformation

ErrorOutputBuffer.AddRow();

ErrorOutputBuffer.FNAME= fName;
       
        if (string.IsNullOrEmpty(SSN))
        {
            ErrorOutputBuffer.SSN_IsNull = true;
        }
        else
        {
            ErrorOutputBuffer.SSN = SSN;
        }

How do handle delimited string with data qualifier in C#

string[] columns = Utility.ToArrayUsingDelimiterAndQualifier(currentRecord, delimiter, qualifier, true);

public static class Utility
    {
 public static string[] ToArrayUsingDelimiterAndQualifier(this string text, char delimiter, char qualifier, bool stripQualifierFromResult)
        {
            var pattern = RegExPattern(delimiter, qualifier);
            return stripQualifierFromResult ? Regex.Split(text, pattern).Select(s => s.Trim().Trim(qualifier).Replace(delimiter.ToString(CultureInfo.InvariantCulture), ",")).ToArray() : Regex.Split(text, pattern);
        }

        /// <summary>
        /// Skip delimiter Regural Expression
        /// </summary>
        /// <param name="delimiter">Delimiter Value</param>
        /// <param name="qualifier">Qualifier to use to skip the delimiter inside that</param>
        /// <returns></returns>
        private static string RegExPattern(char delimiter, char qualifier)
        {
            return string.Format(@"{0}(?=(?:[^{1}]*{1}[^{1}]*{1})*(?![^{1}]*{1}))", Regex.Escape(delimiter.ToString(CultureInfo.InvariantCulture)), Regex.Escape(qualifier.ToString(CultureInfo.InvariantCulture)));
        }
}

Data Validations using C#

Validation Functions:

To check if the input field :

1. Contains any numbers:  Regex.IsMatch(inputFNAME, "[0-9]")
2. Is a numeric or not : if (!Int32.TryParse(input, out _X))
3. Is having non-Alphabetic Characters :!Regex.IsMatch(inputSTATE, @"^[a-zA-Z]+$")

Decrypting the Encrypted Connection Strings

var key = Dts.Variables["SymmetricKey"].Value.ToString();
                var keyIV = Dts.Variables["SymmetricKeyIV"].Value.ToString();              

                // DB Decryption
                var encryptedDBConString = Dts.Variables["EncryptedDBConString"].Value.ToString();
                var decryptedDBConString = (new SecurityUtility(key, keyIV)).Decrypt(encryptedDBConString);
                Dts.Variables["DecryptedDBConString"].Value = decryptedDBConString;
                using (var Conn = new OleDbConnection(decryptedDBConString))
                {
                    Conn.Open();
                }

    /// <summary>
    /// This class provides helper methods for security E.G. encryption/decryption.
    /// </summary>
    internal class SecurityUtility
    {
        #region - Properties and Fields -
        /// <summary>
        /// Gets the Byte Array for the Key
        /// </summary>
        private byte[] KeyArray
        {
            get
            {
                return Convert.FromBase64String(_symmetricKey);
            }
        }

        /// <summary>
        /// Gets the Byte Array for the Key IV
        /// </summary>
        private byte[] KeyIVArray
        {
            get
            {
                return Convert.FromBase64String(_symmetricKeyIV);
            }
        }

        private string _symmetricKey;

        private string _symmetricKeyIV;
        #endregion

        #region - Methods -
        internal SecurityUtility(string symmetricKey, string symmetricKeyIV)
        {
            _symmetricKey = symmetricKey;
            _symmetricKeyIV = symmetricKeyIV;
        }

        /// <summary>
        /// Encrypts the specified plain data.
        /// </summary>
        /// <param name="plainData">The plain data.</param>
        /// <returns>The encrypted data.</returns>
        internal string Encrypt(string plainData)
        {
            // Convert the passed string to a byte array.
            byte[] toEncrypt = new ASCIIEncoding().GetBytes(plainData);
            byte[] inArray = Encrypt(toEncrypt);
            return inArray == null ? string.Empty : Convert.ToBase64String(inArray);
        }

        /// <summary>
        /// Decrypts the specified encrypted data.
        /// </summary>
        /// <param name="encryptedData">The encrypted data.</param>
        /// <returns>The decrypted data.</returns>
        internal string Decrypt(string encryptedData)
        {
            byte[] toDecrypt = Convert.FromBase64String(encryptedData);
            byte[] bytes = Decrypt(toDecrypt);
            return bytes == null ? string.Empty : Encoding.ASCII.GetString(bytes);
        }

        /// <summary>
        /// Encrypts the specified data.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns>The encrypted data.</returns>
        private byte[] Encrypt(byte[] data)
        {
            using (MemoryStream ms = new MemoryStream(data.Length))
            {
                using (var tripleDES = new TripleDESCryptoServiceProvider())
                {
                    using (CryptoStream cs = new CryptoStream(ms, tripleDES.CreateEncryptor(KeyArray, KeyIVArray), CryptoStreamMode.Write))
                    {
                        cs.Write(data, 0, data.Length);
                        cs.FlushFinalBlock();
                        return ms.ToArray();
                    }
                }
            }
        }

        /// <summary>
        /// Decrypts the specified data.
        /// </summary>
        /// <param name="data">The data.</param>
        /// <returns>The decrypted data.</returns>
        private byte[] Decrypt(byte[] data)
        {
            using (MemoryStream ms = new MemoryStream(data.Length))
            {
                using (var tripleDES = new TripleDESCryptoServiceProvider())
                {
                    using (CryptoStream cs = new CryptoStream(ms, tripleDES.CreateDecryptor(KeyArray, KeyIVArray), CryptoStreamMode.Read))
                    {
                        ms.Write(data, 0, data.Length);
                        ms.Position = 0L;
                        string s = new StreamReader(cs).ReadToEnd();
                        return Encoding.ASCII.GetBytes(s);
                    }
                }
            }
        }
        #endregion
    }

Populating a List using LINQ

List<string> PayFrequency = new List<string>();

using (var con = new OleDbConnection(Connections.XYZDB.ConnectionString.ToString()))
            {
con.Open();
using (var cmdPF = new OleDbCommand(getPFQuery, con))
                {
                    using (var rdrPF = cmdPF.ExecuteReader())
                    {
                        PayFrequency = (from IDataRecord rPF in rdrPF
                                        select (string)rPF["ImportFrequencyCode"]
                                         ).ToList();
                    }
                }
}

Querying the List of class objects using LINQ

public string GetExceptionMessage(Int16 messageID, List<ExceptionMessage> exMsg)
        {

            var exceptionMessage = (from em in exMsg
                                    where em.ExceptionMessageID == messageID
                                    select em.ExceptionMsg).FirstOrDefault();

            return string.IsNullOrWhiteSpace(exceptionMessage) ? string.Empty : exceptionMessage;
        }

C# code to validate Date format

        public static bool IsValidDate(string inputDate, out DateTime parsedDate)
        {
            try
            {
                string changedDate = string.Empty;
                string[] DatePattern = {
                                           "yyyy.mm.dd", "mm/dd/yyyy", "yyyymmdd","mmddyyyy", "mm-dd-yyyy", "yyyy/mm/dd", "yyyy-mm-dd",
                                           "yyyy.m.dd", "m/dd/yyyy", "m-dd-yyyy", "yyyy/m/dd", "yyyy-m-dd",
                                           "yyyy.mm.d", "mm/d/yyyy","mm-d-yyyy", "yyyy/mm/d", "yyyy-mm-d",
                                           "yyyy.m.d", "m/d/yyyy", "m-d-yyyy", "yyyy/m/d", "yyyy-m-d"
                                       };

                if (DateTime.TryParseExact(inputDate, DatePattern, null, DateTimeStyles.None, out parsedDate))
                {                  
                    if (inputDate.IndexOf("-") > 0 || inputDate.IndexOf("/") > 0 || inputDate.IndexOf(".") > 0)
                    {
                        // Edge case scenario's like 09/31/2015 or 02/30/2015
                        DateTime.TryParse(inputDate, null, DateTimeStyles.None, out parsedDate);
                        if (!DateTime.MinValue.ToShortDateString().Equals(parsedDate.ToShortDateString()))
                        {
                            return true;
                        }
                    }
                    else
                    {
                        if (Convert.ToInt32(inputDate.Substring(0, 2)) <= 12)
                        {
                            changedDate = inputDate.Substring(0, 2) + "/" + inputDate.Substring(2, 2) + "/" + inputDate.Substring(4);                        
                        }
                        else
                        {                        
                            changedDate = inputDate.Substring(4, 2) + "/" + inputDate.Substring(6) + "/" + inputDate.Substring(0,4);                          
                        }
                        return DateTime.TryParse(changedDate, out parsedDate);
                    }
                }
                return false;
            }
            catch
            {
                parsedDate = DateTime.MinValue;
                return false;
            }
        }