locked
Issue is getting leading 0 in values RRS feed

  • Question

  • I am importing data from excel to SQL Server 2016 using SSIS. Field is Latitude & Longitude

    I am using formula (DT_STR,255,1252)((DT_NUMERIC,9,6)(Latitude)) to import the values in Varchar(255) column. I am facing the issue when Latitude & Longitude starts with 0 eg (-0.737610 or 0.737610).

    After executing the package when I saw the values in Database I saw like (-.737610 or .737610) where leading 0 will get disappear.

    Can anyone please help me resolve my problem.

    Monday, August 10, 2020 7:30 AM

All replies

  • I am using formula (DT_STR,255,1252)((DT_NUMERIC,9,6)(Latitude)) 

    Why do you convert it first to numeric and then to string? A number without leading null is still a valid numeric.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, August 10, 2020 9:07 AM
  • I am converting to Numeric because if I won't do it removes trailing 0.

    So in current example the value will be -0.73761

    To get the decimal place fixed to 6, I am converting to (DT_NUMERIC,9,6).

    Monday, August 10, 2020 1:10 PM
  • It should not remove anything - from which format /file you're importing them? I agree with Olaf, extra conversion seems redundant.

    Looking for new opportunities

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Monday, August 10, 2020 1:50 PM
  • Why violating 1NF? You should store values as atomic values in their domains data type. For coordinates this is a number, not a string.
    • Proposed as answer by Naomi N Monday, August 10, 2020 2:29 PM
    Monday, August 10, 2020 2:04 PM
  • Hi Nitin,

    It's better for you to store the data type of Latitude column as Numeric(9,6) in the destination table.

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Tuesday, August 11, 2020 1:35 AM
  • Hi Nitin,

    May I know if you have anything to update?

    Best Regards,

    Mona


    ""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
    We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
    For more information, please refer to the sticky post.

    Friday, August 14, 2020 8:19 AM