How to Read First (Header) and Last ( Footer) Rows from Flat File and Save into Variables in SSIS Package - SSIS Tutorial

Scenario:

Let's say we have a flat file source with Header and Footer information. We might want to read the first and last row from the flat file and save the first and last row into string variables in SSIS Package that we need to use in next task/s.

In this video, we will learn how to read the first and last rows from flat file source by using Script Task. I have already written a complete post how to validate header, footer and file name against definition tables.

Step 1: 

Create sample file with some data, In this scenario, I have below data for my sample flat file.

ID,Name,Address
1,Aamir,XYZ Address
2,Raza,ABC Address
Footer 2 Records

Step 2: 

Create an SSIS Package and Inside SSIS Package create three variables, one for Header , one for footer row and one to hold source file path.

Fig 1: How to Create First and Last Row from Flat file and save into variables

Step 3: 

Bring Script Task and place on Control Flow pane, Open Script Task and map the variables as shown below.

Fig 2: Map the variables to Script Task to read Header and Footer row from Flat file in SSIS Package

Paste the below script in Main() part of Code.

string FilFullPath = "";
            FilFullPath = Dts.Variables["User::VarFilePath"].Value.ToString();
               string[] lines = System.IO.File.ReadAllLines(FilFullPath);

               Dts.Variables["User::VarHeader"].Value = lines[0].ToString();
               Dts.Variables["User::VarFooter"].Value = lines[lines.Length - 1].ToString();
            MessageBox.Show("Header: " + Dts.Variables["User::VarHeader"].Value.ToString()
                      + "   Footer: " + Dts.Variables["User::VarFooter"].Value.ToString());

Save the Script , Close the Editor and then Run your SSIS Package. You should get the Header and Footer row in message box.

Fig 3: First and Line read from Flat File in SSIS Package by using Script Task

Comment the last line which we are using to display the message. The first and last rows are saved in VarHeader and VarFooter variables in Script Task and now you can use in next tasks.