Suggest Types option in the Flat File connection manager

With the help of this option we can identify the data type and length of columns in a Flat File Connection Manager based on a sampling of the file content.

By default, the data type of the CSV or Flat file is varchar (50).

See this file  

Now creating the flat file connection manager.

We can see the data type in the Advanced tab.

 

Now see the data type of each column. 

Now either we need to make the changes in the data type.

With the help of Suggest Type Option, it can automatically identify the data type of the column.

Click on the Suggest Type Button. 

A new window will open. 

Number of rows

Type or select the number of rows in the sample that the algorithm uses.by default it is 200. Based on the data of 200 it will determine the data type. We can increase or decrease the row sampling.

 Suggest the smallest integer data type

Clear this check box to skip the assessment. If selected, determines the smallest possible integer data type for columns that contain integral numeric data.

Suggest the smallest real data type

Clear this check box to skip the assessment. If selected, determines whether columns that contain real numeric data can use the smaller real data type, DT_R4.

 Identify Boolean columns using the following values

Type the two values that you want to use as the Boolean values true and false. The values must be separated by a comma, and the first value represents True.

 Pad string columns

Select this check box to enable string padding.

 

Percent padding

Takes the length of the longest string ([n]varchar, [n]char) element and extends the length by the percentage you specify to anticipate longer strings in future source files.

 Now we are clicking on the Ok button.

It will change the data type of the column based on the row sampling.

See the column data type.

 

Let’s see the example.

In the sample file first few rows having the SalesorderNumber as varchar and remaining column as an integer. 

If we are giving the row sample 5 then it will determine the data type based on the first 5 records.

  

Clicking ok

Now the data type of the column has been changed from float to int.

 

So be careful when we are using the Suggest Type option in the flat file connection manager.