Migrating the Excel sheet with different datatype

Question

I am migrating the excel sheet which has different data types using ssis

But data format is changing in source level itself

My excel sheet looks like below:

COL1

Null 1/1/2009 Null 1/2/2009 11.99 123.99

When i see in the source preview it self i am seeing data like

COL1

Null 1/1/2009 12:00:00 AM Null 1/2/2009 12:00:00 AM 12/30/1899 12:00:00 AM 12/30/1899 12:00:00 AM

i feel there need to be some setting in the the connection string given to the excel for maintaining the respective formats of each cell. Please suggest me if there are any ideas

Answer 1

Have you had a look at the what the "Data Conversion" Data Flow Transformation component can offer you?

I had a related issue in transferring data from a database to a spreadsheet. Number types were getting inserted as text types (even after adding a conversion component). It appeared that, after quite a bit of reading, that when copying to excel, the process defaulted to the data type of the above cell of each column.

The only away around this (and it does sound a bit rubbish) is to have a hidden row of data in your destination spreadsheet immediately above where your data will be inserted. The data in each cell of this row should contain a value that is of the type that data in column should be.

Give it a shot

Answer 2

My problem is when selecting from source it self data is converted like that and i am not able to convert it back. i too using data convertion component and i am getting only 12:00:00 from that datetime data.