SSIS ActiveX Script Task

Introduction

In this article we are trying to discuss about ActiveX Script task. This is provided only for the purpose of backward compatibility with the dependent component of Data Transformation Services (DTS) nothing else. ActiveX Script task is use for custom code with Older DTS.

But in SSIS we have much more advance feature like Script Task. 

Use of ActiveX Script Task

It can include the business logic in the package. The business logic means the conditional logic to manage the package workflow. By this we can access the value from database table object (ActiveX Data object ADO) and can populate the variable by this value. It can perform the complex database related calculation like ADDDATE, DATEDIFF etc. It can extract data by using Active Directory Services Interface (ADSI) from Active Directory for user information.

Case Scenario

By using ActiveX Script task we are going to create a text file in the name of "Task.txt" in the location "F:\Practice_SQL\SSIS\ActiveXScriptTask" and write "Hello I am ActiveX Script Task" into the above text file.

How to Do that

Step-1 [ Control flow Task ]

Drag an ActiveX Script Task into the Control Flow pan.

Step-2 [ ActiveX Script Task Editor ]

Here we choose the VB Script language.

Important

In this point we may be get an error

To solve this problem, please follow the MSDN Link given bellow

http://msdn.microsoft.com/en-us/library/ms143755.aspx

Step-3 [ Run the Package and see the Result ]

Hope you like it.

SSIS Lookup Transform

Introduction

SSIS data flow control has a very important transform called the Lookup Transform. So I decide to write this article related to it.

What is the use of Lookup Transform

The main functionality of the lookup join is to make a Join with other source with current source and fetch the result in a desired format.

[Current Source] + [Other Source] à [Fetch Result] à[Desired Format]

The source can be anyone of the following Chased object, Table, Destination file source, a result from query etc.

The Lookup transform be available for data source like SQL, ORACLE and DB2.

Case Study

Here we have a flat file (FlatFile-A.txt) which contains the product information. There is a SQL table object named tbl_LOOKUPMATCH which contain the match records of product information. The SSIS package use the Lookup Transform and segregate the matched records from flat file to a table objects called tbl_LOOKUPMATCHDESTINATION and unmatched records to another table objects called tbl_LOOKUPUNMATCHDESTINATION.

Please follow the diagram to understand the case study.

Configuring the Lookup Transform

Step-1 [ Flat file source, Lookup Match and Destination Table Objects ]

Creating Lookup Match and Destination Table Objects

-- Lookup Match Table

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

   BEGIN

      DROP TABLE tbl_LOOKUPMATCH;

   END

GO

CREATE TABLE  tbl_LOOKUPMATCH

       (

          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,

          PRODNAME VARCHAR(50) NOT NULL

       );

GO

-- Inserting Records in Lookup Match Table

INSERT INTO  tbl_LOOKUPMATCH 

       (PRODCD, PRODNAME)

VALUES ('1', 'RICE'),

       ('2', 'OIL');   

Lookup Match tables Records

-- Records of Lookup match table      

SELECT * FROM tbl_LOOKUPMATCH; 

PRODCD               PRODNAME

1                              RICE

2                              OIL

-- Creating Match Lookup Destination

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

   BEGIN

      DROP TABLE tbl_LOOKUPMATCHDESTINATION;

   END

GO

CREATE TABLE  tbl_LOOKUPMATCHDESTINATION

       (

          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,

          PRODNAME VARCHAR(50) NOT NULL

       );

GO 

-- Creating Un Match Lookup Destination

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

   BEGIN

      DROP TABLE tbl_LOOKUPUNMATCHDESTINATION;

   END

GO

CREATE TABLE  tbl_LOOKUPUNMATCHDESTINATION

       (

          PRODCD   VARCHAR(50) NOT NULL PRIMARY KEY,

          PRODNAME VARCHAR(50) NOT NULL

       );

GO           

Step-2 [ SSIS Data flow ]

Step-3 [ Configuring Flat file source Editor ]

Step-4 [ Configuring Lookup Transform Editor ]

Step-5 [ Configuring OLE DB Destination Editor ]

Step-6 [ Run the SSIS Package ]

Hope you like it.

Retrieving Master Child Record From Flat file

Introduction

One of my friend ask me write an article related to retrieving Master Child(Transaction) records from Flat File source.

It is a concept of Primary Key and Foreign Key. Here in this article we are trying to learn it. Here I am not going to describe related to Primary key and Foreign key relation.

Simple Case Scenario

Here we have a Flat file which contains the sales related records. Here is the sample of that

Saleno              Date                   ItemCode            Quantity            Rate                  Amount

S1                      01/01/2013          I101                      10.000                200.00               2000.00

S1                      01/01/2013          I102                      5.000                  100.00               500.00

S2                      02/01/2013          I101                      11.000                200.00               2200.00                    

In the above flat file we can see the both Master and Transaction data presents and we have to separate then and store it in different table objects where primary key and foreign key relation is there.

The Master Table must contain the following attributes (Columns):

1.    Sales no

2.    Date

The transaction Table must contain the following attributes (Columns):

1.    Sales no

2.    Item Code

3.    Quantity

4.    Rate

5.    Amount

To solve this problem we are taking the help of surrogated key concept.

Solution of this Case

Step-1 [ Create the Destination Table object for Master and Child Records ]

-- Master Table

IF OBJECT_ID(N'dbo.TBL_MASTER', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[TBL_MASTER];

   END

GO

CREATE TABLE [dbo].[TBL_MASTER]

       (IDNO     INT         NOT NULL IDENTITY(1,1) PRIMARY KEY,

        SALENO   VARCHAR(50) NOT NULL,

        SALEDT   DATETIME);

       

-- Child Table

IF OBJECT_ID(N'dbo.TBL_CHILD', N'U') IS NOT NULL

   BEGIN

      DROP TABLE [dbo].[TBL_CHILD];

   END

GO

CREATE TABLE [dbo].[TBL_CHILD]

       (SALENO   VARCHAR(50)   NOT NULL,

        IDNO     INT           NOT NULL,

        ITEMCD   VARCHAR(50)   NOT NULL,

        QTY      DECIMAL(20,3) NOT NULL,

        RATE     DECIMAL(20,2) NOT NULL,

        AMOUNT   DECIMAL(20,2) NOT NULL);

GO

-- Setting the Foreign Key Relation

ALTER TABLE  [dbo].[TBL_CHILD]

ADD CONSTRAINT [FK_TBL_CHILD_IDNO] 

FOREIGN KEY (IDNO) REFERENCES[dbo].[TBL_MASTER](IDNO);

GO   

Step-2 [ SSIS Package Control Flow ]

Step-3 [ Configuring Data Flow Task ]

Load Master Data Flow Tasks Configuration

Load Child Data Flow Tasks Configuration

Step-4 [ Run the package and Analyze it ]

Hope you like it.