locked
Issue is getting leading 0 in values RRS feed

  • Pergunta

  • 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.

    segunda-feira, 10 de agosto de 2020 07:30

Todas as Respostas

  • 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]

    segunda-feira, 10 de agosto de 2020 09:07
  • 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).

    segunda-feira, 10 de agosto de 2020 13:10
  • 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

    segunda-feira, 10 de agosto de 2020 13:50
  • Why violating 1NF? You should store values as atomic values in their domains data type. For coordinates this is a number, not a string.
    • Sugerido como Resposta Naomi N segunda-feira, 10 de agosto de 2020 14:29
    segunda-feira, 10 de agosto de 2020 14:04
  • 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.

    terça-feira, 11 de agosto de 2020 01:35
  • 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.

    sexta-feira, 14 de agosto de 2020 08:19