SSIS -How to Read First Row From Flat File [Header Row]

Scenario:
Sometime we have to read the first row( Header Row) from flat file and then validate against our definition table and if Header matches with our definition then proceed to load otherwise send email to related people that they have send the file with wrong information. The good part of this validation is that the Vendor can quickly send us new file instead of we start load with wrong file and that will fail our package.In case of failure we have to debug the package or look into log files to find out error and that can be lengthy process. With Header Validation we can always save time where wrong file is sent accidentally.

Solution:
We can use Script task to perform this. Let's create a variable "FilePath" in our package that will have file path and "HeaderRow" Variable to hold the first row from Flat File.

Step 1: 
Create a flat file with name SourceFile.txt with below data
ID,Name,Address
1,Aamir,My Address
2,Raza,ZZZZ Address

Step 2: 
Create Variables and set the path of file in FilePath variable as shown below
Step 3: 
Choose the variables in Script task for ReadyOnlyVariables and ReadWriteVariables as shown below

Step 4:
Click on Edit Script button and write script as shown below. I used MessageBox.show just for testing. The value is set to HeaderRow Variable that we can use anywhere in package for Header Row Validation or any other purpose.

Final Output : 
As in our script we are displaying the value of HeaderRow variable that can be seen in snapshot. This variable can be used in Execute SQL Task or in Precedence Constraint for Header Row Validation.

8 comments:

  1. This is really smart move which can save lot of time. Thanks for sharing this Aamir

    ReplyDelete
  2. How can we get the headers of multiple flat files in a path into table.

    ReplyDelete
  3. IN the same way do you know how do you do with the second row ??
    thanks

    ReplyDelete
  4. I want to create a source flat file that have two header rows. How i can create a flat file with two headers and data as well? And later i want to send first header data into first table and second header data into other table.

    ReplyDelete
  5. Hi, I want to load only specific row from text file to db
    please help me

    ReplyDelete
  6. Assam 6th, 7th, 8th, 9th, 10th Textbook 2023 for SEBA Distribution Process Starts Centrally with Assam Government Guideline and SEBA is the State Level Authority for Conducting Examinations and Providing Assurance for the Quality of Education imparted in Schools within Assam Supervision of Secondary Education Department. Assam 6th, 7th, 8th, 9th, 10th Textbooks 2023 is Available Our Web Portal, SEBA 6th Class Books SEBA has Developed the new Textbooks at the High School Standard in Prepare by Senior experts. Assam Board once Publishes the Assam Assam 6th, 7th, 8th, 9th, 10th Standard Textbooks 2023 Other Study materials on the official web site.

    ReplyDelete