Flat File Connection Manager

A Flat File connection manager is used to access the data in a flat file. In most cases, the columns in the file is comma delimited. But sometimes we'll have to split a line into columns by the length of each column. In the following example, I'm going to create a flat file students.txt in C:\SSIS folder to show you how to cut the line into columns.

Here is the content of the file.

Student Name 1      000021
Student Name 2      000022
Student Name 3      000023

The first column is 20 characters long and the second is 6 characters long.

Now let's start to create the Flat File Connection Manager.

  1. Create a new project LearnSSIS2 under the solution LearnSSIS to hold all the packages regarding Data Flow task. The project can be created by right-clicking the Solution 'LearnSSIS' in Solution Explorer and choose "Add" then "New Project...". The other steps is the same as that we did in Hello World section.

  2. Rename the default package to DataFlowTask.dtsx. Then Right-click the area of Connection Managers of the package and choose "New Flat File Connection..." to open the Editor.
  3. [New Flat File Connection]

  4. Choose the File name, change the Format to "Fixed width" and make "Column names in the first data row" unchecked.
  5. [Flat File Connection Manager Editor General Settings]

  6. Click Columns tab then drag the red vertical line to 28 position and click 20 to delimit the columns of name and age.
  7. [Flat File Connection Manager Editor Columns]

  8. Click Advanced tab then click Column1 and change the DataType to DT_I8.
  9. [Flat File Connection Manager Editor Advanced]

  10. Click Preview tab to check the data.
  11. [Flat File Connection Manager Editor Preview]

  12. Click OK button to save the settings. The Flat File Connection Manager is created in the Connection Managers area.

In the next section, I'm going to use this connection manager to create a Data Flow Task.