Microsoft Business Intelligence: Get the List of Columns in a CSV Source File

Wednesday 30 June 2021

Get the List of Columns in a CSV Source File

When we are working with the CSV files and that CSV files we are getting from the different sources. Sometimes we are getting the less number of columns from some sources. In this case, our packages get failed.

Below is the example.

This is the correct file. But sometimes we are getting files with missing columns like below.

In this file we are getting two missing columns ( TotalDiscount and DeliveryCharge). In this case our package is getting failed.

To avoid package failure First of all we need to check the required number of columns. If files not having correct columns in this case we cannot process that file. We will log these files and send it to the Respective team.

In the processing directory, we are getting the files.

Now we are designing the package to handle this case.

Foreach loop container we are taking to iterate the files.

SCR_File_Validation Script task is used the validate the files.

If file is having 5 columns then that is correct file.

Note : we are assuming that first line is the columns name.

Taking some variables to store the store the file name and columns count.

We are writing the below code in the script task.

public void Main()

{

// TODO: Add your code here

//User::File_Path

using (var reader = new StreamReader(Dts.Variables["User::File_Path"].Value.ToString()))

{

var line = reader.ReadLine();

var Columns = line.Split(',');

int i = Columns.Length;

Dts.Variables["User::Number_of_columns"].Value = i;

}

Dts.TaskResult = (int)ScriptResults.Success;

}

Based on the column counts we are redirecting the flow.

If column counts are 5 then we are loading the data using Data Flow Task. If not in this case we can load that file and send that information to the respective team.