SSIS- Load data from Text

In datawarehousing field, it is more likely to load data from text files and it is deveopers head ache to deal with the problems of formatting issues.  In this post I would like to show one typical issue with text file which can be tackled very easily.

I have a file with Sudent,Mark and address details which need to load into the table. I have created the flat file connection in SSIS and realised that it was not able to recognize the columns due to some extra junk lines in text file before starting of the actual required details and considered as a single column as shown in Figure 1.

Figure 1

Find out the number of lines with junk values and skip those lines by providing the numerical skip values to the property Header rows to skip as shown in Figure 2. Also provide the tick mark for the property column names in the first data row which will consider the first line after the skipped lines as a column name.

Figure 2

now go back to the columns but you can see that still all the columns are showned in one column instead of seperate also column delimiter field is empty and disabled.
Figure 3

This is because it is refering the old field settings. You can change this by hitting Reset Columns and providing the required delimiter.


Post a Comment

Popular posts from this blog

Hadoop - Hive - Load data from csv/xls files

Microsoft BI Implementation - Cube back up and restore using XMLA command