Share via

wrong format from excel to ssis

asma gh 21 Reputation points
2020-12-01T09:32:44.567+00:00

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,525 questions
0 comments

4 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Yitzhak Khabinsky 25,731 Reputation points
    2020-12-01T13:48:27.023+00:00

    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
  2. asma gh 21 Reputation points
    2020-12-01T15:28:41.353+00:00

    which conversion i should use ??

  3. Monalv-MSFT 5,896 Reputation points
    2020-12-02T02:31:46.337+00:00

    Hi @asma gh ,

    Please drag Data Conversion Transformation in Data Flow Task.

    Hope the following link and pictures will be helpful:

    SSIS Data Conversion

    44109-dcindft.png

    44110-dataconversiont.png

    Best Regards,
    Mona

    ----------

    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
  4. asma gh 21 Reputation points
    2020-12-02T10:54:53.98+00:00

    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