locked
Import date from excel file to SSIS Excel Source RRS feed

  • Question

  • I have an excel sheet with dates only. By default the Date column in excel sheet is in UK format. Problem occurs when date comes in US or any other format. When i fetch date column through SSIS Excel Source, it returns NULL for any format other than UK.

    In my SSIS Excel Source, I want to get the date as it is in the excel file irrespective of its locale.

    Date can be in any for these format:

    21/01/2012

    01/21/2012

    21012012

    21-01-2012

    01-21-2012 00:00:000

    Can anyone help me out in the same?

    Thanks in advance

    Wednesday, January 25, 2012 10:05 AM

Answers

  • Hi Guys,

    Finally i got the solution.

    Goto Excel Connection Manager, right click on it, open the Properties. Under ConnectionString append IMEX=1 at the end, This forces mixed data to be converted to text.

    Note: Don't forget to close your excel file before running the package. I was doing the same mistake of not closing the excel file, once i closed it i got the correct output.

    Thanks for the help guys.

     


    -HS
    • Proposed as answer by Eileen Zhao Friday, January 27, 2012 2:50 AM
    • Marked as answer by Husain Sadiq Friday, January 27, 2012 5:27 AM
    Wednesday, January 25, 2012 12:20 PM

All replies

  • Hi,

    How do you know in which format this is: "01/02/2012"? I think you need to enforce a specific format in your source otherwise it will be hard.

    David.

    Wednesday, January 25, 2012 10:09 AM
  • As david has rightly put his suggestion. there has to be a policy by which the data will have to adhere.
    Please vote as helpful or mark as answer, if it helps
    Cheers, Raunak | t: @raunakjhawar | My Blog
    Wednesday, January 25, 2012 10:19 AM
  • Hi David,

    UK format will come in higher precedence, So any date with second part greater than 12 will be treated as US format.

     

    -HS


    -HS
    Wednesday, January 25, 2012 10:40 AM
  • Hi Guys,

    Finally i got the solution.

    Goto Excel Connection Manager, right click on it, open the Properties. Under ConnectionString append IMEX=1 at the end, This forces mixed data to be converted to text.

    Note: Don't forget to close your excel file before running the package. I was doing the same mistake of not closing the excel file, once i closed it i got the correct output.

    Thanks for the help guys.

     


    -HS
    • Proposed as answer by Eileen Zhao Friday, January 27, 2012 2:50 AM
    • Marked as answer by Husain Sadiq Friday, January 27, 2012 5:27 AM
    Wednesday, January 25, 2012 12:20 PM