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.
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.
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.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.