wrong format from excel to ssis

asma gh 21 Reputation points

hello , please i have a column age in an excel file with standard format but i display in ssis using source excel it convert it to date like this example any idea how to fix it by having a number column not a date
thank you 44043-excel.png44044-ssis.png

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,451 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Yitzhak Khabinsky 24,941 Reputation points

    MS Excel has no notion of data types. It is an electronic paper that allows any scribble. Everything is a text. That's why strongly typed environment like SSIS needs to guess what is ingested from MS Excel. And that guess is not always correct.

    Your solution would be to add a Data Conversion Transformation between source and destination in the Data Flow Task.

    0 comments No comments

  2. asma gh 21 Reputation points

    which conversion i should use ??

  3. Monalv-MSFT 5,891 Reputation points

    Hi @asma gh ,

    Please drag Data Conversion Transformation in Data Flow Task.

    Hope the following link and pictures will be helpful:

    SSIS Data Conversion



    Best Regards,


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
    Hot issues in November--What can I do if my transaction log is full?
    Hot issues in November--How to convert Profiler trace into a SQL Server table?

    0 comments No comments

  4. asma gh 21 Reputation points

    excel get it as a date but i want to keep the format like it is example "595416:00:00" because i m going to treat them after that as rejected line