Add dummy single row into the first row of excel file in SSIS

Question

I receive excel file every week. Suppose it has following columns:

Cust_Name(string), Cust_Id(int), Cust_filename(string),description(string/Unicode string/ Unicode text stream), explanation(String/Unicode string/ Unicode text stream).....

I am facing problem with column data types. SSIS excel source is considering same column data type as string, sometimes as Unicode string and sometimes as Unicode Text stream based on underlying excel data.

So what I want to do is to add dummy row which contains Unicode Text stream data and insert it to 1st row of excel using script component. So that when I read that excel, SSIS excel source will automatically detect those columns as Unicode Text stream every time and later on using Data Conversion I can convert some columns into appropriate data types. I want to know how can I insert dummy text row into first row of excel. Existing column data will be followed by that dummy row data.

Goal:

1) Get the excel file,

2) Read excel & Add dummy "text" row into the first row & save to some other name using SSIS source script component.

Answer 1

In excel connection manager uncheck the box which says read the first row as header row. Also add IMEX=1 in connection string. This will add the headers as dummy row and do the job..

Let me know if this works