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());
            }
        }

Installing / Uninstalling a Windows Service

Steps to install a Windows Service:

1. Open the Windows Service Solution in Visual Studio.
2. Make sure the target platform was set properly according to the machine you are trying to install the service.

3. Build the Solution in Debug / Release mode.
4. Copy the files generated in the Debug / Release folder (based on the mode selected in the above step) to some new folder.
5. Open the command prompt as an administrator.
6. Type the below command:
C:\Windows\Microsoft.NET\Framework\v4.0.30319>InstallUtil.exe <Full File path of the Executable generated for your service (new folder path created in step 4)>

(Ex: C:\Pardha\NewService\SchedulerService\SampleService.exe)

7. Press Enter.
8. The below message appears at the end if the installation is successfully completed.

• "The Commit phase completed successfully."
• "The transacted install has completed."

9. Check your service in the services list. (services.msc)
10. Set the user context under which the service has to run & then Start it Manually.

To Uninstall:

C:\Windows\Microsoft.NET\Framework\v4.0.30319>InstallUtil.exe /u <Full File path of the Executable generated for your service (new folder path created in step 4)>

SSIS - How to allow the foreach loop continue its execution even if any of the task inside it fails

1. Click on Event Handlers.
2. Click on the Grid icon under variables tab.
3. Check the Show System Variables option.
4. Under the variable list search for the system variable called Propagate.
5. Change its value to False.

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]+$")