Capturing Trailer Record information from Flat file in SSIS

One fine day, when we are working with SSIS, we got a requirement. Here is the scenario:

  1. We have a flat file which has a trailer record; initially we need to load the data from the Flat File to a table in SQL Server database.
  2. After the data is loaded into the database, we need to verify the total number of records that are loaded with the Total number of records in the Trailer information.

 

3. Once the verification is valid, then we need to continue the process else we need to send a notification to the configured recipients.

All these steps need to done using SSIS package. Below are the steps that we need to follow to meet the above requirement.

SSIS:

  1. Open a new SSIS project; File –> New –> Project –> Select Integration Services Project.
  2. Create a  variable Trailer_Count with the package scope of datatype Int32; Rowcnt with the package scope and integer datatype (this is for retrieving the total number of records from the database).
  3. Drag and Drop Data flow task (DFT_FileLoad) in the Control Flow tab.
  4. In the Data Flow tab for the above data flow task, initially drag and drop Flat file Source. Click on New Connection. Browse for the source file and name the columns in the advanced tab that is in Flat file connection.

  

 5. We need to identify the trailer information in the flat file by using the Condition Split.

6.  In the Conditional Split editor type as below i.e., FINDSTRING([ColumnName from the feed file], “the name that need to be identified as a trailer record”, [Occurance of the word in feed file])

In my flat file the trailer information will be starting with the string “Date”, hence I am finding the string ‘Date’ in the corresponding 1st column, if it exists then it will flow through right direction and values will be assigned to variables, else the rows will be redirected to left and loaded into the destination database.

 

7. Now, let us see how the trailer information is sent to the variables in the package.

8. In the Derived column task Derived Column_Trailer, we will assign the value from the trailer record which contains the information of total number of records in the flat file, named as Der_count (here we are using the data conversion DT_WSTR – it’s purely depends on the requirement).

 

9. Drag the Script Component from the transformation and select Transformation as Component Type.

10. Here we need to assign the value that is coming from the derived column i.e., Der_count to a package variable Trailer_Count.

 

11. Select the variable Trailer_Count from the ReadWriteVariables. Go to Input Columns page check the check box corresponding to the column name that was given in the Derived Column task (in the above step)

12. Click on Edit Script and type as below:

*******************Start of the Script******************************
/* Microsoft SQL Server Integration Services Script Component
*  Write scripts using Microsoft Visual C# 2008.
*  ScriptMain is the entry point class of the script.*/
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    int tempCount = 0;
    public override void PreExecute()
    {
        base.PreExecute();
        /*
          Add your code here for preprocessing or remove if not needed
        */
    }
    public override void PostExecute()
    {
        base.PostExecute();
        /*
          Add your code here for postprocessing or remove if not needed
          You can set read/write variables here, for example:
          Variables.MyIntVar = 100
        */
        Variables.TrailerCount = tempCount;
    }
    public override void Input0_ProcessInputRow(Input0Buffer Row)
    {
        /*
          Add your code here
        */
        tempCount = (int)Row.DerCount;
    }
}
 *******************End of the Script******************************

13. This script will assign the value from the derived column to a package variable.

14. Now, after the data flow task, drag and drop the execute sql task, this is for retrieving the total number of records from the database that are loaded from the flat file.

  

 15. In the SQLStatement give the query that takes the count of all records from the table in the database (as above). Select Single Row from the Result Set.

 16. Assign the count of records from the database to a variable in a package i.e., RowCnt as below:

 17. Click on ok.

18. Now take another Execute SQL Task and connect this with the above execute SQL task.

 

19. Double click on the connector between these two Execute SQL tasks. In the Precedence Constraint Editor, Select Expression and constraint and in the Expression type as below, this condition will check if the count of records from the database and count of records from the trailer are equal are not, if success then it executes the second Execute SQL Task, else it sends a notification to the configured recipients.

 

Leave a comment

2 Comments

  1. saravanakumar

     /  October 5, 2014

    Simply superb….This post really helped me a lot .

  2. S Mohapatra

     /  May 25, 2011

    Awsome article !!! really very helpful…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: