SSIS – Case IN Sensitive Match by Lookup Transform

Introduction

Data source is not perfect. Sometime the simple scenario makes more completed that we imagine. Here in this article we are demonstrating the Lookup match in case insensitive way. Hope you find it interesting and it put some value in your professional career.

The Scenario

We have two table in our database one is [tbl_EmployeeDetails] which contains the employee name.

EmpName

joydeep

rAjesh

DEEpasree

If we look at the data carefully we find that the Employee name case is sometimes Upper or Lower or both.

We have a Lookup table with the name [tbl_SelectedEmployee] which contains selected employee name.

EmpName

JOYDEEP

As we all know very well that the Lookup is Case sensitive and when we match both it not works. So the question is how we solve it

The Control Flow Tab

Run with Normal Scenario

It is unable to match.

How to Solve it

Solution -1 [ Using UPPER clause ]

We can put the UPPER clause in Select Statement of OELDB Source

SQL Command

SELECT UPPER(EmpName)AS EmpName

FROM   [dbo].[tbl_EmployeeDetails];

SQL Command

SELECT UPPER(EmpName)AS EmpName

FROM   [dbo].[tbl_SelectedEmployee];

Works fine when we run it.

It is NOT a GOOD solution as per my opinion. It is just unnatural use of component.

Solution – 2 [ Just change the Cache Mode of Lookup ]

If we Choose the Full Cache it is Case Sensitive and if we choose the Partial Cache is not Case sensitive.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Error Handling With ForEach Loop Container

Introduction

Every times my friend circle provides me some interesting story to solve. It is brain storming but interesting too.  Thanks to them to provide me such wonderful information.

Here in this article I am providing another interesting story related to error handling with ForEach Loop containers. It’s easy but has some trick to play with it. Hope it will be interesting and put some value in your professional fields.

What the Scenario is

Every SSIS article has an interesting scenario and the first thing to understand the scenario. Well understanding the scenario is 90% work done and rest is simple implementation.

We have a ForEach loop Containers. It provides us a serial number like 1 to 7. Within these containers there is Execute SQL task. It a simple task with SQL statement like

SELECT 100/?

ForEach Loop containers provide the value for(?) by a variable.

Think about a situation like this. The ForEach containers provide values like this 1,0,3,4,5,6 and 7. Now when the value of 100 is divided by Zero (0) it gives us an Error. And the Execute SQL task Failed.

With Execute SQL task the ForEach Loop containers is also failed.

We want it to continue the ForEach loop containers to next number and redirect the output of failure to another Execute SQL task.

Here we use two other Execute SQL task depends on Precedence constraint of first Execute SQL task. If the first Execute SQL task fails then it move the flow in another Execute SQL task named Failure and if not then Success.

Hope you understand the scenario

How to Do that

Step – 1 [ The Control Flow ]

 Step – 2 [ Create the table which store the Output ]

CREATE TABLE [dbo].[tbl_FailResultValues]

  (

    ResultValues  DECIMAL(18,2)

  );

GO

CREATE TABLE [dbo].[tbl_PassResultValues]

  (

    ResultValues  DECIMAL(18,2)

  );

GO

Step – 3 [ Execute SQL Task named Truncate Table ]

Here we just Truncate the Destination Table

TRUNCATE TABLE [dbo].[tbl_FailResultValues];

TRUNCATE TABLE [dbo].[tbl_PassResultValues];

Step – 4 [ ForEach Loop Containers ]

Step – 5 [ Execute SQL task ]

It’s a simple task which has SQL statement.

Result Set                   : Single Row
SQL Statement           : SELECT CONVERT(DECIMAL(18,2), 100/?);
Parameter mapping   : [User::v_LoopValue]
Result Set                   :[ User::v_Output]

Step – 6 [ Precedence Constraint ]

Step – 7 [ The Execute SQL Task names Failure ]

SQL Statement

INSERT INTO [dbo].[tbl_FailResultValues]

VALUES(?);

Step -8 [ The Execute SQL Task named Success ]

Same as step-7 but changes on SQL statement.

SQL Statement

INSERT INTO [dbo].[tbl_PassResultValues]

VALUES(?);

Step – 9 [ Run on NORMAL Condition without Changing any Property ]

When we execute the Package and when the Execute SQL Task fails the ForEach Loop containers also fails.

Step – 10 [ How to Fix it ]

This can be done by two ways. For that go to the Properly of ForEach Loop Container and change the property describe bellow

ForceExecutionResult = Success
Or
MaxErrorCount = 0

Step – 11 [ Run again after changing the Property ]

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Dynamically Sequential Execution of Child Package

Introduction

Now it is the time of Executing the package. But we have several package and we want to execute it sequentially. For that we are creating a Master Package and call the other package (Child) from master package. Here we are not using any Script Task for that. We just use the Execute Package task. In this article I am not showing the process of transferring variable value from master package to child package. You can find this example from my previous blogs or any other blogs.

Most of the blog post that I see in the net to sequentially execute the package task they uses Script Task. But the specialty of this article is doing the same thing by using Execute Package Task in dynamic way. Hope it will be informative and you enjoy the session.

The Scenario

We have a Table objects named tbl_PackageInfo

idNo	PackageDtls	IsEnabled
1	E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-1.dtsx	1
2	E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-2.dtsx	1

This table contains the Package Path and isEnable columns. We want to Execute all the package mentioned in the table which have isEnabled = 1

Simple task, but need some trick to complete it. Hope you understand the Scenario.

How we Do that

We assume that the we have two Child package in the name of Child-1.dtsx and Child-2.dtsx

Step – 1 [ Table named tbl_PackageInfo and Insert the Package Information ]

IF OBJECT_ID(N'[dbo].[tbl_PackageInfo]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_PackageInfo];

   END

GO

CREATE TABLE [dbo].[tbl_PackageInfo]

  (

    idNo        INT           NOT NULL IDENTITY PRIMARY KEY,

    PackageDtls VARCHAR(1000) NOT NULL,

    IsEnabled   BIT           NOT NULL

  );

GO

INSERT [dbo].[tbl_PackageInfo]

   (PackageDtls, IsEnabled)

VALUES

('E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-1.dtsx', 1),

('E:\Preparation\SSIS\SSISPractice\SSISPractice\Child-2.dtsx', 1);

Step – 2 [ The Entire Control Flow Task ]

Step – 3 [ The Variable ]

Step – 4 [ The Execute SQL Task ]

We need to execute those package which has IsEnabled is 1.

The SQL Statement

SELECT PackageDtls FROM [dbo].[tbl_PackageInfo] WHERE IsEnabled = 1;

Step – 5 [ ForEach Loop Containers ]

Step – 6 [ Execute Package Task ]

Here the trick is. In ForEach Loop containers we have to put the Execute Package Task. If we have two Package to execute, then we must put the two Execute Package task over there.

Now we configure the first Execute Package Task.

When we configure the first Execute Package Task the connection manager is automatically created. Rename it as Package Connection.

Right Click the Connection manager and select property and we have to configure the Connection property by Expression.

Now re-configure the entire Execute package task by choosing connection manager name in connection property.

Important Note:

If we take the single Execute Package task within the ForEach Loop Containers only the one child package is execute.

If we have more than one Child package, we must take Execute Package Task more then one depends on how many child package we have. Configuration of the entire child package is same. Just put the Connection manager name in the Connection property and Location property is File system.

Connection = < Name of the Connection Manger >

Location = File System

Step – 7 [ Observation ]

Summary

If you have any alternate process except Script Task, please mentioned in comments that we can share knowledge.

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS – Flat File Retrieval with Real-time Scenario Based

Introduction

We are again with flat file scenario based solution. Query are frequently came to provide the solution. Here is another interesting story with Flat File. I personally prefer when to design with SSIS take a note book and try to draw it. It gives 90% of the solution and next 10% we have Google…

The Scenario

We have a folder and folder contains some flat files. The name of the flat files is important it contains Filename + Date + Serial Number. 

For an example: EmployeeRecord20151124-01.txt, EmployeeRecord20151124-02.txt etc.

We have to retrieve only those file which have system date. I mean to say the date of package run must match with the date for File name.

In a single day there are three copy of the file is put into the folder, such as morning, Evening and Night.

The package must run each file in first time and if we re-run the package it not retrieve the data from old file of same day. It looks for new file in same day, if found then retrieve records and else not.

The new flat file of same day may be contains the old modified records also. So package must take care of that also.

Hope you understand the scenario.

The Logic behind the Package

1.    We need three tables object. One is Destination Table where the actual data store. Second table keep track the File name which is already retrieved. Third table takes all the file name and path from the source folder.

2.    First we start works with third table and put the entire file name that match the current date scenario.

3.    Then we compare it with Second table and check whether the file is retrieved before or not. If not then we retrieve the file.

4.    The data flow logic is as before using Merge transform and Conditional split

How we do that

Step – 1 [ Create the Necessary Table Objects ]

IF OBJECT_ID(N'[dbo].[tbl_EmployeeDetails]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_EmployeeDetails];

   END

GO

CREATE TABLE [dbo].[tbl_EmployeeDetails]

   (

     EmpID    INT         NOT NULL PRIMARY KEY,

     EmpName  VARCHAR(50) NOT NULL,

     EmpDept  VARCHAR(50) NOT NULL,

     EmpGrade CHAR(1)     NOT NULL

   );

GO

IF OBJECT_ID(N'[dbo].[tbl_OLDFlatFileDetails]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_OLDFlatFileDetails];

   END

GO

CREATE TABLE [dbo].[tbl_OLDFlatFileDetails]

   (

     IdNo           INT           NOT NULL IDENTITY PRIMARY KEY,

     FFileName      VARCHAR(50)   NOT NULL,

     FullPath       VARCHAR(1000) NOT NULL,

   );

GO

IF OBJECT_ID(N'[dbo].[tbl_NEWFlatFileDetails]', N'U')IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[tbl_NEWFlatFileDetails];

   END

GO

CREATE TABLE [dbo].[tbl_NEWFlatFileDetails]

   (

     IdNo           INT           NOT NULL IDENTITY PRIMARY KEY,

     FFileName      VARCHAR(50)   NOT NULL,

     FullPath       VARCHAR(1000) NOT NULL,

   );

GO

Step – 2 [ The Control Flow Task ]

Step – 3 [ Execute SQL Task named TRUNCATE tbl_NEWFlatFileDetails ]

Here we Truncate the tbl_NEWFlatfileDetails Table where we store the Flat file name and path from the source folder.

TRUNCATE TABLE [dbo].[tbl_NEWFlatFileDetails];

Step – 4 [ ForEach Loop Container ]

Step – 5 [ Expression Task named SUBSTRING File Name ]

In the Expression we used

@[User::v_FileNameSubString]= SUBSTRING( @[User::v_FileName], 15, 8 )

Here the SUBSTRING Function retrieves the DATE part from the File name like 20151124.

Step – 6 [ Precedence Constraint ]

In the Expression we used

@[User::v_FileNameSubString]== @[User::v_CurrentDateFile]

Where the variable named @[User::v_CurrentDateFile] has its own Expression

REPLACE((DT_WSTR, 10)(DT_DBDATE)GETDATE(),"-","")

Step – 7 [ Execute SQL task named Insert Data INTO tbl_NEWFlatFileDetails ]

The SQL Statement is

INSERT INTO [dbo].[tbl_NEWFlatFileDetails]

  (FFileName, FullPath)

VALUES(?, ?)

Step – 8 [ Execute SQL Task named Retrive Records Comparing Tables ]

The SQL Statement is

SELECT FullPath, FFileName

FROM   [dbo].[tbl_NEWFlatFileDetails]

WHERE  FFileName NOT IN(SELECT FFileName

                        FROM   [dbo].[tbl_OLDFlatFileDetails]);

Step – 9 [ For EachLoop Container ]

Step – 10 [ Data Flow Task named Working With tbl_EmployeeDetails ]

I am not going to describe it here. Just a pictorial diagram is given

The Flat File Connection manager need to change one property named Connection String by Expression

Step – 11 [ Execute SQL task named Insert Data Into tbl_OLDFlatFileDetails ]

The SQL Statement is

INSERT INTO [dbo].[tbl_OLDFlatFileDetails]

(FFileName, FullPath)

VALUES(?, ?)

One thing I want to mention in my SSIS article is that, I don’t want my reader just copy and paste the steps but they must understand the beauty of the package and do it by their own logic. If the reader has any confusion or query they can comment it or write me mail.

Hope you like it.

Posted by: MR. JOYDEEP DAS