How to Read Last Row from Flat File in SSIS Package - SSIS Tutorial

Scenario:

Let's say we have a flat file and we need to read the flat row from this flat file and save into a variable in SSIS Package. How would we do that.

Solution:

We will be using Script Task to perform this requirement in this post.

Step 1: 

Create a sample file that you will use for this SSIS Package.

ID,Name,Address
1,Aamir,XYZ Address
2,Raza,ABC Address
Test Records
This is my Footer Row

Step 2: 

Create an SSIS Package and create two variables.
VarFooter  : This should be string type and we will save our last row from flat file into it.
VarFilePath : This should be string type and we will save Source File path into it.

Fig 1: Read Footer row from flat file into variable in SSIS Package

Step 3:

Bring the Script task to Control Flow Pane and map the variables as shown below.
Fig 2: Map the variables to read footer row from Flat file into variable in SSIS Package

Step 4: 

Paste the below code in Main part of script

string FilFullPath = "";
            string FooterRow = "";
                                 
            FilFullPath = Dts.Variables["User::VarFilePath"].Value.ToString();
               string[] lines = System.IO.File.ReadAllLines(FilFullPath);
               FooterRow = lines[lines.Length - 1].ToString();
                Dts.Variables["User::VarFooter"].Value = FooterRow;
            MessageBox.Show("LastRow: "+Dts.Variables["User::VarFooter"].Value.ToString());


Save the script and close the Editor. Run the SSIS Package and you should get a message with your footer row.
Fig 3: Read Footer row from Flat File in SSIS Package by using Script Task


 You can remove 
MessageBox.Show("LastRow: "+Dts.Variables["User::VarFooter"].Value.ToString());
or comment this line once tested.

No comments:

Post a Comment