Jul 14, 2014

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.

Data Flow Task

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

Flat File Source

Step 3: Configure Flat File source.
  • Select file name (Input.txt)
  • Set Format as "Ragged Right"
Configure Flat File Source

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

Script Component

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

Input Columns

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

Configure Output


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.

Add OLE DB Destintation

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

Configure OLE DB Destintation
Configure OLE DB Destintation

Step 10: Now let's execute the package

Package Execution

Step 11: Package is executed successfully. Now let's check for the data in the tables.
select * from Header
select * from Detail
OUTPUT
Output

Mar 20, 2014

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