none
Data from flat file not accepting the declared datat type

    Question

  • Good Morning All,

    I have a package that loads data from a flat file to a sql server table. The column name is 'QTY' (Quantity). The data is coming as follows:

    020000,000099,043000,001000,520000.........

    I'm using the datat type DT_NUMERIC (6,2) in my SQL Server table, but instead of loading the data as

    200.00,0.99,430.00,10.00,5200.00

    Its loading the data as

    20000.00,99.00,43000.00,1000.00,520000.00

    I tried different data types as well but still no luck, can anyone please direct me ? Thanks

    Friday, July 12, 2013 11:25 AM

Answers

  • Thanks Koen,

    But i have 10's of tables that do this, is there any other workaround?

    • Marked as answer by PMunshi Friday, July 12, 2013 5:12 PM
    Friday, July 12, 2013 12:36 PM
  • You'll need to manipulate the data in some way, and dividing it by 100 is the simplest way I can think of.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by PMunshi Friday, July 12, 2013 5:12 PM
    Friday, July 12, 2013 12:39 PM

All replies

  • You don't have any decimal seperator in your source data, so SSIS will think 020000 as 20000.00 (dropping leading zeroes and adding the two decimal places).

    Just divide your data by 100 to get the result you want.


    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    Friday, July 12, 2013 11:33 AM
  • You can achieve using Derive Column and Data Conversion Transformation.

    Refer the below screenshot.


    Regards, RSingh

    Friday, July 12, 2013 12:23 PM
  • Thanks Koen,

    But i have 10's of tables that do this, is there any other workaround?

    • Marked as answer by PMunshi Friday, July 12, 2013 5:12 PM
    Friday, July 12, 2013 12:36 PM
  • You'll need to manipulate the data in some way, and dividing it by 100 is the simplest way I can think of.

    MCSE SQL Server 2012 - Please mark posts as answered where appropriate.

    • Marked as answer by PMunshi Friday, July 12, 2013 5:12 PM
    Friday, July 12, 2013 12:39 PM
  • Thanks Koen and Singh. i finally ended dividing by 100.
    Friday, July 12, 2013 5:13 PM