Steps to follow when creating a new flat file in SSIS
Using an existing flat file in SSIS is straightforward, but creating a new one can be a pain - this blogs gives some steps to follow.

Posted by Andy Brown on 04 December 2014

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating New Flat Files in Integration Services

SSIS works pretty well, on the whole, but it always throws me when it comes to creating flat files (writiing to existing flat files is fine, because the column headers are already set up).  So here's a quick run through to provide help for anyone who needs it!

The Problem

We want to export a list of X Factor mentors to a flat file:

The file to export

If you want, you can get a copy of this file here.  Here's what the answer will look like:

Text file produced

The text file produced by the package.


One Solution

This isn't the only solution, but it is one which will work.  First create a package containing a data flow task as follows:

A data flow task with Excel source

The data flow task should contain an Excel source.



Now add a Flat File destination from the Other Destinations category (using the Destination Assistant doesn't seem to work):

Flat File destination

Double-click on this destination to add it to your data flow task.


Now connect the source to the destination:

Connecting source and destination

Feed the output from the Excel workbook into this new flat file.



Double-click on this destination to configure it - it doesn't yet have a connection:

Flat file connection drop list

There is no flat file connection yet, not surprisingly.

Click on the New... button on the right to add a connection:

Adding a connection

Click on this button to create a new flat file connection.


Choose the sort of flat file you want to create:

Flat file type

We're going to go for a simple comma-delimited flat file.


Give your connection a name (here we've called ours Link to test file) and also - more importantly - choose what you're going to call the new flat file you're creating (here we're calling it testfile.text):

File name for flat file

You can also click on the Browse button (not shown in this diagram) to change the folder in which the file will be created.

Make sure that you choose to put column headings at the top of your flat file:

Column names in the first data row

You should normally tick this box to create a first row giving the column headings.

When you click on the Columns tab, you should see the headings set up for you:

Columns in a flat file

The columns MentorId and MentorName have been created for you.

Back in the Flat File editor, you can now map your columns:

Column mappings

Click on this tab to map your columns in the usual way.


Everything should now work normally!

This blog has 0 threads Add post