How to transfer data from Excel spreadsheet to flat file without column headers?

Question

Got a simple data flow in SSIS with excel source and flat file destination. The headers in excel are on first row and in SSIS I've the 'first row has headers' ticked in excel connection manager.

In my flat file the data is being loaded and all the data looks correct except the headers from the excel.

When I set up my flat file connection manager (ffcm) it was using the comma delimited setting for the columns

Checked in columns in ffcm and all the columns were there.

and after a few runs I noticed that I had not ticked the 'Column names in the first data row' in the flat file connection manager. Now that I have done this I have an error

TITLE: Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [DTS.Pipeline]: "component "Flat File Destination" (487)" failed >validation and returned validation status "VS_NEEDSNEWMETADATA".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

So unticked that again but made no difference.

Checked the columns in the ffcm and they are now set to column0, column1, column2....etc.

Also when I run it it puts out a number of lines of commas realted to the rows in excel sheet:

,,,,,,,,,,,,
,,,,,,,,,,,,
,,,,,,,,,,,,
,,,,,,,,,,,,

and I seem to be getting in a bit of a pickle and need some better advice about what the problem may be.

Answer 1

It seems that you have lost the field mappings between your Excel Source and Flat File Destination since you last configured the values.

Unchecking and checking the box Column names in the first data row on the Flat File Connection Manager has renamed the actual column names of the flat file destination. These new columns should now be re-mapped on the Flat File Destination component.

If you notice the warning sign on your Flat file destination, double-click the flat file destination. You will receive a message something similar to the one shown below.

On the Flat File Destination, you will notice the warning message Map the column on the Mappings page if the field mappings have been lost..

On the Flat File Destination, you will notice that field mappings have been lost and you need click Mappings page to configure the field mappings between source and destination.

I believe that this is the issue you are facing.