-1

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.

Srini V
  • 11,045
  • 14
  • 66
  • 89
Ichi
  • 1
  • 3
  • and what have you tried so far ? – mrid Nov 03 '17 at 11:34
  • Tried with type guess row logic, IMXE=1 logic. Converting excel to csv manually, Tried with adding dummy row (text value) in first row of excel manually, Changing all column data types in excel. All possibilities i have tried. – Ichi Nov 03 '17 at 16:31

1 Answers1

0

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

sam
  • 1,242
  • 3
  • 12
  • 31