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.

2 comments:

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

    ReplyDelete

Note: Only a member of this blog may post a comment.