Read the First N Rows From the flat file

We are getting the CSV file and business want to load top 10 records in the table. With the help of script component, we can load the first n record into the table.

Let’s see this example

Below is the source file.

 

We want to load the first 10 records from this file into the database.

Now I am taking the Data flow task

 

Taking some variables like file path which I am going to read and the Number of records that I want to read.

   

 

Now I am taking Source as a Script component.

Taking both variables in the script component.

Now we need to create output with output columns.

 

By default data type of this column is a string (DT_STR). We need to convert according to our data type.

Go to the script and click on the edit script.

 

In Solution Explorer, we will get the 3 cs class.

 

Buffer Wrapper and Component Wrapper both classes we can’t write in this class.

Buffer Wrapper class

In this class output columns declare.

 

Component Wrapper

In this class variable declares.

 

Now I am writing the code in the main class.

In Pre Execute class we are assigning the variable value.

 

Now I am writing the code CreateNewOutputRows in class.

public override void CreateNewOutputRows()     {                int i = 0;                 string[] lines = System.IO.File.ReadAllLines(fpath);         foreach (string line in lines)         {             string[] columns = line.Split(',');             if (i != 0) //skipping the header row             {                 Output0Buffer.AddRow();                 Output0Buffer.SalesOrderNumber = columns[0];                 Output0Buffer.SalesAmount = float.Parse(columns[1]);                 Output0Buffer.UnitPrice = float.Parse(columns[2]);                 Output0Buffer.ExtendedAmount = float.Parse(columns[3]);                 Output0Buffer.TaxAmt = float.Parse(columns[4]);             }                        if (i == x)                 break;          i = i + 1;         }

Save and close this window.

Now I am taking the multicast to see the result.

 

Before running this task see the source file.

 

Now I am running this task.

 

I hope this helps this for Reading the First N rows from the flat file.

Thanks for reading!

Token function in SSIS expression

Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned. The function returns a substring of the provided string. The substring is defined in the following way: we define the character expression and the delimiter string that will divide the character expression into many substrings or tokens. The result of the function will be the substring whose position is defined by the occurrence number. 

Syntax

TOKEN(character_expression, delimiter_string, occurrence)

The arguments of the TOKEN function are:

Ø  Character_expression – represents the string where we will search the string we are looking for. 

Ø  Delimiter_string – represents the string that holds one or more delimiters.

Ø  Occurrence – represents the number of the token that will be returned.

Let’s see an example

Suppose we have a excel file and a column (State name) have the value with comma separated and we need to load state code in one column and state name in another column.

State name

Bihar ,BR

Utter Pradesh ,UP

Maharashtra ,MH

Jharkhand ,JH

We want to load this as below

State Name	State code
Bihar	BR
Uttar Pradesh	UP
Maharashtra	MH
Jharkhand	JH

There are multiple ways to get this result.

The easy way using token function.

Taking data flow task and source as  excel file.

In drive column I am using the Token function. We know that that before comma is the state name and after it states code.

  

To see the result I am taking Multicast.

  

If the value of <occurrence> parameter is higher than the total number of tokens in the string, the function returns NULL.

Let’s see in the above example

  

Column has only 2 comma-separated value. Occurrence I am using 5 so it will return a null value.

  

TOKEN returns a null result if the character_expression is null.

TOKEN works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before TOKEN performs its operation. Other data types must be explicitly cast to the DT_WSTR data type.

Token Count Function in SSIS expression

The TOKENCOUNT function returns back the number of times a Token delimiter appears in a string value.

Syntax:

TOKENCOUNT(character_expression, delimiter_string)

Character expression: A string that contains tokens separated by delimiters.

Delimiter string: A string that contains delimiter characters. For example, ";," contains three delimiter characters semi-colon, a blank space, and a comma.

Let’s see an example.

Expression: ‘Bagesh Kumar Singh Greater Noida UP’

The TOKENCOUNT function returns 6 because the string contains three tokens: "Bagesh", "Kumar", “Singh”, “Greater”, “Noida, UP".

  

Keep in mind Delimiter string can’t be empty. If we keep it null it will throw an error.

  

TOKENCOUNT returns 0 (zero) if the character_expression is null.

TOKENCOUNT works only with the DT_WSTR data type. A character_expression argument that is a string literal or a data column with the DT_STR data type is implicitly cast to the DT_WSTR data type before TOKEN performs its operation. Other data types must be explicitly cast to the DT_WSTR data type.