Import a Flat file with Header and Detail Rows in SSIS

In this post, I am going to demonstrate Step by Step Example of importing a Flat File which contains both Header and Detail Records with different formats in the SQL Server Database header & detail tables respectively using SSIS.

Prerequisite

• An Input File Input.txt with header and detail records
• Header & Detail Tables in the database

Content of Flat file (Input.txt)

H1100
D1050P1
D1050P2
H2200
D2100P3
D2100P4

Flat file Header/Detail Record Specs

Script for Header & Detail Tables

CREATE TABLE [dbo].[Header](
    [id] [varchar](50) NOT NULL,
    [amount] [int] NOT NULL
)

CREATE TABLE [dbo].[Detail](
    [id] [varchar](50) NOT NULL,
    [amount] [int] NOT NULL,
    [product] [char](100) NOT NULL
)

Step 1: Add a Data Flow Task to Control Flow.

Step 2: Add a Flat File Source to the Data Flow.

Step 3: Configure Flat File source.

• Select file name (Input.txt)
• Set Format as "Ragged Right"

Step 4: Add Script Component from Data Flow Transformations and connect Flat File Source to it

Step 5: Double click on Script component and Configure the Input Columns like below

Step 6: Configure Output under Inputs and Outputs tab like below as per Header & Details specifications.

Step 7: Click on Edit Script and write following code in the script window under Input0_ProcessInputRow method

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    if (Row.Line.Substring(0, 1) == "H")
    {
        HeaderBuffer.AddRow();       
        HeaderBuffer.id = Row.Line.Substring(1, 1);
        HeaderBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));
    }
    else if (Row.Line.Substring(0, 1) == "D")
    {
        DetailBuffer.AddRow();
        DetailBuffer.id = Row.Line.Substring(1, 1);
        DetailBuffer.amount = Convert.ToInt16(Row.Line.Substring(2, 3));
        DetailBuffer.product = Row.Line.Substring(5, 2);
    }
}

Step 8: Add two OLE DB destination for Header and Detail and connect Header and Detail output of Script Component to each destination respectively.

Step 9: Configure OLE DB destination for Header & Detail like below

Step 10: Now let's execute the package

Step 11: Package is executed successfully. Now let's check for the data in the tables.

select * from Header
select * from Detail

OUTPUT

Mutex Multithreading in C#

In this post we will learn how to implement Mutex Multi-Threading in C# Console Application.

Let's take a case study to understand how and where to implement mutex and multi-threading at the same time.

Scenario

• Schedule an application which would run very frequently (say 1 min).
• Application has to execute two methods which take 2.5 mins and 0.5 min respectively.
• Both methods are independent of each other.

Normal code without mutex and multithreading

using System;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            Method1();
            Method2();
        }

        static void Method1()
        {
            for (int i = 0; i < 10; i++)
            {
                Thread.Sleep(1000);
                Console.WriteLine("Method 1 : " + i.ToString());
            }
        }

        static void Method2()
        {
            for (int i = 0; i < 20; i++)
            {
                Thread.Sleep(1000);
                Console.WriteLine("Method 2 : " + i.ToString());
            }
        }
    }
}

OUTPUT

First Problem: Method-2 takes only 0.5 min to execute but it is called only once Method-1 is executed i.e. after 2.5 mins
Solution: Implement multi-threading so that methods will be executed parallely

Code with multi-threading

static void Main(string[] args)
{
    Thread t1 = new Thread(new ThreadStart(Method1));
    t1.Start();
    Thread t2 = new Thread(new ThreadStart(Method2));
    t2.Start();
}

OUTPUT

Method-2 getting executed parallely without waiting for the first one to complete, thus, solving our first problem.

Next Problem: Application is taking 3 mins (2.5 + 0.5) to execute, but it is scheduled to run after every one minute, this might cause multiple instances of the application at the same time.

Solution: To prevent running multiple instances we can do either of the following

• While scheduling the application, select the option "Do not start a new instance"
• Implement mutex in the application at application level

But only this would not solve our problem. Doing this way an application instance will run in every 3 mins even if it scheduled for 1 min causing delay in the execution of Method-2 which can be invoked every min as it takes less than a minute. So we have to implement mutex at method level so that application instance will run after 1 minute but will invoke only method(s) whose previous instance is not running.

Code with mutex and multi-threading

using System;
using System.Threading;

namespace ConsoleApplication1
{
    class Program
    {
        static Mutex M1;
        static Mutex M2;

        static void Main(string[] args)
        {
            bool b1, b2;
            M1 = new Mutex(false, "Method1", out b1);
            M2 = new Mutex(false, "Method2", out b2);
            if (b1)
            {
                Thread t1 = new Thread(new ThreadStart(Method1));
                t1.Start();
            }
            if (b2)
            {
                Thread t2 = new Thread(new ThreadStart(Method2));
                t2.Start();
            }
        }

        static void Method1()
        {
            M1.WaitOne();
            for (int i = 0; i < 150; i++)
            {
                Thread.Sleep(1000);
                Console.WriteLine("Method 1 : " + i.ToString());
            }
            M1.ReleaseMutex();
            M1.Dispose();
        }

        static void Method2()
        {
            M2.WaitOne();
            for (int i = 0; i < 30; i++)
            {
                Thread.Sleep(1000);
                Console.WriteLine("Method 2 : " + i.ToString());
            }
            M2.ReleaseMutex();
            M2.Dispose();
        }
    }
}

Let's schedule the application in Task Scheduler and check the outputs

Output on the execution of first instance after scheduling

Both methods are running parallely initially and after 30 secs only Method-1 is executing as Method-2 execution is finished

Second output on the execution of second instance (after 1 min)

First instance is still running and executing Method-1. Second instance executing only Method-2 as Method-1 is still executing in other instance as mutex prevented the execution of Method-1

Third output on the completion of second instance (after 1.5 mins)

Second Instance lasts for only 30 secs as it invoked only Method-2. First instance is still running and executing Method-1.

scrap my car for money