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.

2 comments:

  1. I'm trying to copy a huge flat file source header with roughly 250 columns but I'm getting the below error when I execute the Script Task. Any help would be appreciated.

    Getting DTS Script Task: Runtime Error
    Project name: ST_e381b1cfb888465690b1a0bfd3a3de36
    Exception has been thrown by the target of an invocation.


    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()

    ReplyDelete
  2. TODAY I GOT MY DESIRED XMAS LOAN AMOUNT $520,000.00 FROM A RELIABLE AND TRUSTED LOAN COMPANY. IF YOU NEED A LOAN NOW EMAIL CONTACT drbenjaminfinance@gmail.com

    Hello, I'm here to testify of how i got my loan from BENJAMIN LOAN FINANCE(drbenjaminfinance@gmail.com) I don't know if you are in need of an urgent loan to pay bills, start business or build a house, they offer all kinds of loan. So feel free to contact Dr. Benjamin Owen he holds all of the information about how to obtain money quickly and painlessly without cost/stress via Email: drbenjaminfinance@gmail.com

    Consider all your financial problems tackled and solved ASAP. Share this to help a soul right now THANKS.

    ReplyDelete