none
How to change SSIS package or database table column to accept text values loading from excel spreadsheet that have been formatted to number RRS feed

  • Question

  • I am loading data from excel spreadsheet to SQL table using SSIS package. Excel spreadsheet column formatted to data type "Number" but column consists only texts ("Y" or "N"). This particular column data won't load to SQL table unless I have manually changed the spreadsheet data type to text.  I am processing 50+ spreadsheets everyday so manually changing this column is not an easy thing. Can someone please help me how to get this worked by changing data type in stored procedure or table.

    Many thanks & you help would be much appreciated.


    Tuesday, October 8, 2019 4:02 AM

Answers

  • Hi Saphire77,

    During my test, the data type in Excel is number, the data type in SQL database is nvarchar(50). But I don't need to use Derived Column to change the data type.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Saphire77 4 hours 32 minutes ago
    Thursday, October 10, 2019 8:25 AM

All replies

  • Hi Saphire77,

    You could use Derived Column or Data Conversion in SSIS.

    If you have further question, feel free to contact us.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, October 8, 2019 5:59 AM
  • Hi Zoe, can you please give me an example how to use Derived Column or Data Conversion in SSIS.

    Thanks heaps

    Saphire

    Wednesday, October 9, 2019 1:47 AM
  • Hi Saphire77,

    You could click the link I have already provided to you in the last post.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 9, 2019 1:57 AM
  • Thanks Zoe, if I use derived column transformation function, what's the data type I should change? In Excel spreadsheet it's formatted to "number", but this field contains only "Y" & "N", database table field data type is nvarchar(50).

    Thank you so much

    Saphire

    Wednesday, October 9, 2019 2:35 AM
  • Hi Saphire77,

    Please try this expression: (DT_WSTR, 50)ColName.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, October 9, 2019 5:51 AM
  • I tried that but still "Y" are not loaded to the field. Package comes with no errors but particular field comes as no values. Any ideas please

    Thanks heaps

    Saphire
    Wednesday, October 9, 2019 8:17 PM
  • Hi Saphire77,

    Could you post your screenshots, it will help us resolve the issue better.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, October 10, 2019 2:43 AM
  • Thank you Zoe,

    Normal workflow is:

    Excel column formatted to "Number" even though its hold "Y".

    Hope this clarifies and your help on this would be very much appreciated.

    Thanks a lot

    Saphire

    Thursday, October 10, 2019 4:23 AM
  • Hi Saphire77,

    During my test, the data type in Excel is number, the data type in SQL database is nvarchar(50). But I don't need to use Derived Column to change the data type.

    Regards,

    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Saphire77 4 hours 32 minutes ago
    Thursday, October 10, 2019 8:25 AM
  • Thank you Zoe, thanks again for your greatest effort to help me. It seem to be you have the similar set up to mine so its hard to believe why mine is not loading…

    Regards

    Saphire

    Friday, October 11, 2019 1:36 AM
  • Hi Saphire77,

    If you have further question, please feel free to contact me.

    If you fund above post was helpful, you could mark it as answer. It will help other people who has similar issue and benefit the community.

    Regards,
    Zoe


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, October 14, 2019 1:21 AM