Bulk Insert Task

Introduction

As the name suggests the BULK Insert task is used to insert bulk data from source to destination. But the transformation of data is not possible over here. It straight forwards insert bulk data from source to destination. The configuration of bulk insert task is quite simple and there is no need of  data flow components over here. Just one control flow tasks is sufficient to insert data from source to destination.

In this article I am trying to illustrate the feature and configuration of Bulk Insert task of control flow.

Type of Source and Destination

The source and destination can be any of the data sources as we have such as OLEDB, excel, etc.

Configuring Bulk Insert Task

Step-1 [ The flat file source and Destination Table ]

The flat file name is "BulkInsertSource.txt"

Destination Table objects name is "tbl_BulinsertDestination"

IF OBJECT_ID(N'tbl_BulinsertDestination', N'U') IS NOT NULL

   BEGIN

     DROP TABLE tbl_BulinsertDestination;

   END

GO

CREATE TABLE tbl_BulinsertDestination

      (UNIT             VARCHAR(50)       NOT NULL,

       [YEAR]           VARCHAR(4)        NOT NULL,

       [PERCENT]        DECIMAL(20,2)     NOT NULL);

GO      

Step-2  [ Control Flow tasks ]

Step-3 [ Bulk Insert Task Editor ]

Step-4 [ Result Set ]

SELECT * FROM tbl_BulinsertDestination;

UNIT      YEAR     PERCENT

East         2010       50.00

East         2011       55.00

East         2012       60.00

East         2013       45.00

West       2010       0.00

West       2011       40.00

West       2012       56.00

West       2013       72.00

North      2010       5.00

North      2011       12.00

North      2012       16.00

North      2013       45.00                      

Hope you like it.

Posted by: MR. JOYDEEP DAS

Integer Value to Roman Number

From my collection here is a small function to convert the Integer value to roman number.  I want to share it with all my friends and readers.

Hope you like it.

/*

   This function converts the Integer numbers into Roman numbers.

   The max limit is 9999

  

   Testing

   ------------------------------------

   SELECT dbo.fnCONVTO_ROMAN(111)

   ------------------------------------

*/

IF OBJECT_ID(N'[dbo].[fnCONVTO_ROMAN]', N'FN') IS NOT NULL

   BEGIN

     DROP FUNCTION [dbo].[fnCONVTO_ROMAN];

   END

GO  

CREATE FUNCTION [dbo].[fnCONVTO_ROMAN]

      (

            @p_Number INT

      ) RETURNS VARCHAR(30)

AS

BEGIN

  IF @p_Number > 10000 RETURN '*'

 

  DECLARE @v_RomnNumber VARCHAR(30);

 

  SET @v_RomnNumber = REPLICATE('M',@p_Number/1000);

  SET @p_Number = @p_Number%1000;

  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('C',@p_Number/100);

  SET @p_Number = @p_Number%100;

  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('X',@p_Number/10);

  SET @p_Number = @p_Number%10;

  SET @v_RomnNumber = @v_RomnNumber + REPLICATE('I',@p_Number);

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',9),'CM');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',9),'XC');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',9),'IX');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',5),'D');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',5),'L');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',5),'V');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('C',4),'CD');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('X',4),'XL');

  SET @v_RomnNumber = REPLACE(@v_RomnNumber,REPLICATE('I',4),'IV');

  RETURN @v_RomnNumber;

END

GO

Posted by: MR. JOYDEEP DAS

Fuzzy Lookup Transform

Introduction

Real-world data is "dirty" because of misspellings, truncations, missing or inserted tokens, null fields, unexpected abbreviations, and other irregularities. Fuzzy lookup enable us to match input records with clean, standardize records in a reference table. To understand it properly let's take an example. Suppose we have customer information like customer name and address. During the sales transaction we take the input for customer name and address which may not be matched exactly with records in the customer reference table because of typographical or others error in the input data. Fuzzy lookup returns the best matching records from the customer reference table even if no exact match exists.

So the fizzy lookup is a very useful transform for every SSIS developer in the real-world environment.  In this article we are going to learn about it.

How we use the Fuzzy Lookup Transform

Step-1 [ Create the Fuzzy Lookup Reference Table ]

-- Fuzzy Lookup Reference table

IF OBJECT_ID(N'tbl_FUZZYREFERENCES', N'U') IS NOT NULL

   BEGIN

      DROP TABLE tbl_FUZZYREFERENCES;

   END

GO

CREATE TABLE tbl_FUZZYREFERENCES

       (FIRSTNAME VARCHAR(50)  NOT NULL,

        LASTNAME    VARCHAR(50)  NOT NULL,

        DOB         DATETIME);

GO          

--Inserting Records

INSERT INTO tbl_FUZZYREFERENCES

       (FIRSTNAME, LASTNAME, DOB)

VALUES ('Joydeep', 'Das', '12-17-1974'),

       ('Shipra', 'Roy Chowdhury', '09-22-1974'),  

       ('Deeepasree', 'Das', '01-31-2003');

GO

SELECT * FROM tbl_FUZZYREFERENCES;                  

FIRSTNAME                        LASTNAME                                         DOB

Joydeep                                  Das                                                         1974-12-17 00:00:00.000

Shipra                                     Roy Chowdhury                                      1974-09-22 00:00:00.000

Deeepasree                             Das                                                         2003-01-31 00:00:00.000

Step-2 [ The Source Flat File ]

The flat file name is "FuzzySourceRecords.txt"

Step-3 [ Data Flow ]

Step-4 [ Fuzzy Lookup Transform Editor ]

Step-5 [ Derived Column Transform Editor ]

Step-6 [ Union All Transform Editor ]

Step-7 [ OLE DB Destination Create Table ]

Step-8 [ Running the Package ]

Step-8 [ Final Destination Table ]

Hope you like it.

Posted by: MR. JOYDEEP DAS

Foreach Loop with Dynamic Flat File Connection

Introduction

One of my friends working with SSIS told me that he is using a Foreach Loop Containers to connect with a flat file from a specified directory. The directory contains more than one flat file with the name with date. That means the flat file name comes with date extension like this File2652013.txt and each day one flat file is stored within this directory. He needs to read the current dated flat file only.

To do this he use one of my preciously posted article named "SSIS Dynamic Flat file Connection" (Link: http://www.sqlknowledgebank.blogspot.in/2013/05/ssis-dynamic-flat-file-connection.html) Posted at date: 1^st May 2013.

He is able to read current date file but his problem is due to Foreach Loop containers. As the folder contains multiple flat file the loop moving according to that and read the current dated flat file multiple time and hence records are duplicated in the destination table. As he is not using any primary key in the destination table, he gets the duplicate records in the table and if he use the primary key constraint in the destination he get a duplicate key error.

How to solve this problem

To solve this problem he needs a break like statement to break the loop (in case any other programming language an IF condition and Break statement). Here in SSIS it is not possible. To solve this problem I am using a Script Task and a Package level variable. The initial value of the package level variable in "0". When it passes to the script task it check the value of the variable. If  it is "0" then the script result is Success others it failure. In this way I control the looping of the For each Loop.

So let's Start

Step-1 [ The source flat file details and Destination table objects  ]

We have 2 source flat file in the folder path "F:\Practice_SQL\SSIS\SSIS Examples\SourceFlatFile"

IF OBJECT_ID(N'tbl_PRODUCTDETAILS', N'U') IS NOT NULL

   BEGIN

     DROP TABLE tbl_PRODUCTDETAILS;

   END

GO

CREATE TABLE tbl_PRODUCTDETAILS

       (PRODID      VARCHAR(50)   NOT NULL,

        PRODNAME    VARCHAR(100)  NOT NULL,

        PRODQTY     DECIMAL(20,0) NOT NULL,

        PRODUOM     VARCHAR(7)    NOT NULL);

G0

Step-2 [ Create the Package level variable ]

Step-3 [ SSIS Control Flow and Data Flow ]

Step-4 [ Foreach Loop Containers Editors ]

Step-5 [ Script Tasks Editor ]

        public void Main()

        {

            Int32 intCount = 0;

            intCount = Convert.ToInt32(Dts.Variables["v_CountLoop"].Value);

            if (intCount == 0)

            {

                Dts.Variables["v_CountLoop"].Value = 1;

                Dts.TaskResult = (int)ScriptResults.Success;

            }

            else

            {

                Dts.TaskResult = (int)ScriptResults.Failure;

            }

        }

Step-6 [ Flat File connection String Expression ]

@[User::v_FilePath]  + "File"+(DT_STR,4,1252)DAY(getdate())+(DT_STR,4,1252)MONTH(getdate())+(DT_STR,4,1252)YEAR(getdate())+".txt"          

Step-7 [ Running the Package ]

Step-8 [ Destination Output ]

SELECT * FROM tbl_PRODUCTDETAILS;

PRODID      PRODNAME                                       PRODQTY            PRODUOM

PROD1         PWB PLYWOOD 1x4x19MM              200                         PCS

PROD2         PWB PLYWOOD 2x4x19MM              300                         PCS

PROD3         PWB PLYWOOD 3x4x19MM              300                         PCS

   

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS - CHECKSUM and Dynamic Changes

Introduction

In this article I am trying to demonstrate the SSIS CHECKSUM Transform to determine the dynamic changes in sources.

Case Study

Please understand the case study very well before drilldown to package level.

There is a text file named "DuynamicData.txt". We are just going to extract this flat file and load it in our SQL Server table objects called "tbl_DynamicData".

The source file structure is mentioned bellow

PRD1;TOOTH BRUSH;100;25

PPD2;SAVING LOTION;214;250

PRD3;SAVING CREAM;240;130

PRD1                   :  is the product number and we are taking is as primary key.

TOOTH BRUSH: is the product name

100                       : is the stock quantity

25                         : Price of the product

So we have to load it into our MS SQL Table named "tbl_DynamicData". But the text file changed frequently, for example

PRD1;TOOTH BRUSH;100;25

PPD2;SAVING LOTION;200;250

PRD3;SAVING CREAM;290;130

Here the Product quantity changed from 214 to 200, 240 to 290 and when we run the package it automatically determine the changes and changed it to our destination table accordingly. It juts copy the entire data at first time and each and every time it just look at the changes of source and act accordingly to change the destination table.

Step-1 [ Create the Destination Table ]

/*

   PRD1;TOOTH BRUSH;100;25

   PPD2;SAVING LOTION;214;250

   PRD3;SAVING CREAM;240;130

   Destination Table

*/

IF OBJECT_ID('tbl_DynamicData') IS NOT NULL

   BEGIN

     DROP TABLE tbl_DynamicData;

   END

GO

CREATE TABLE tbl_DynamicData

       (PRONUM          VARCHAR(50)   NOT NULL PRIMARY KEY,

        PRODNAME    VARCHAR(50)   NOT NULL,

        STKQTY      DECIMAL(10,0) NOT NULL,

        PRICE       DECIMAL(10,2) NOT NULL,

        Hash        INT           NULL);      

Step-2 [ Enable CHECKSUM ]

Go to the given link and down load the checksum transform and install it.
http://www.sqlis.com/sqlis/post/Checksum-Transformation.aspx

Right click on tool bars à Select the Choose Item from the shortcut menu. The choose toolbox item appears. In the SSIS Data flow Items select the Checksum Check box and click ok. The Checksum appears in the toolbars dataflow transform tabs.

Step-3 [ Prepare the SSIS Package ]

Property of CheckSum Transform

Property of Lookup Transform

Property of Conditional Split Transform

Property of OLE DB Command

The SQL Command

UPDATE tbl_DynamicData

    SET PRODNAME =?,   

            STKQTY=?,     

            PRICE=?,      

            Hash=?

WHERE PRONUM=?                  

 Property of RowCount

Hope you like it.

Posted by: MR. JOYDEEP DAS

SSIS Dynamic Flat File Connection

Introduction

if someone tells you "How create a SSIS package which extract data from flat file source and load it into a SQL server table".

This is pretty simple tasks if we take the static flat file connection. But here is a twist wearing for us.  To understand it please read the case scenario carefully.

Case Scenario

Think about a situation where we have a folder and a text file is copied in that folder every day and we have to load the text file in our SQL Server database Table. The text file contains coma separated records like Student ID, Student Name, Subject, City, State, Country. The name of the txt file is [ LoadedRecord ] + [ Day of Month ] + [ Month of Year ] + [ Year ]. Example of the file name is "LoadedRecord38042013.txt" and it is loaded in the fixed path "F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection" in our case.

The Sample of Txt File

Here our motto of demonstrating the Connection string expression builders of a flat file connection in the connection manager only.

How to Do that

Step-1 [ Creating SSIS Package level variables ]

In the SSIS Package first we create a variable describe bellow.

Name        : v_Path

Scope       : Package level

Data Type : String  

Value        :  F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection

Step-2  [ Configuring the Flat file Source  ]

Drag a Data Flow Tasks in control flow containers. Right click and Edit the control flow tasks. In the data flow tab drag a flat file source and right click it and select edit, the flat file source editor window appears.

Step-3 [ Configure the Flat file Connection Manager Editor ]

Now we configure the Flat file connection manager editor.

Step-3 [ Configure the Property expression of the Flat file connection ]

Now we configure the Property expression of the Flat file connection within connection manager. To do this right click the Flat file connection on the connection manager then selects the property. In the property window select the Expression and click the ellipse button. The property expression editor window appears.

In the Property Expression window there are two sections. For the Property select the ConnectionString and click the expression the Expression builder windows appear.

Step-4 [ Expression Builder ]

In the Expression Builder window type the following expression in the Expression section and click the Evaluate Expression Button to evaluate it.

The expression is

@[User::v_Path] + "\\LoadedRecord"+(DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))+(DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))+(DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))+".txt"

To save the Expression, click on OK and this expression will now be saved with your connectionString property and be resolved automatically each time it is run.

Step-5 [ Understand the Expression ]

The fixed path of the text file is "F:\Practice_SQL\SSIS\SSIS Examples\Dynamic Connection" and we put it into the variable named @[User::v_Path]

The file name start with a common string LoadedRecord so + \\LoadedRecord

Next the file name contains the Day+Month+Year

For day : (DT_STR,4,1252)DAY( DATEADD( "dd", -1, getdate() ))

For month: (DT_STR,4,1252)MONTH( DATEADD( "dd", -1, getdate() ))

For Year:  (DT_STR,4,1252)YEAR( DATEADD( "dd", -1, getdate() ))

Last the extension of text file +".txt"

Hope you like it.

Posted by: MR. JOYDEEP DAS