sql - Correct type of data for latitude and longtitude SSIS ETL process

Question

I'm trying to convert and upload latitude and longitude data into a database through an ETL process I created where we take the source data from a .csv file and convert it to DECIMAL. Here you have an example of what the two values look like:

Latitude (first column): 41.896585191199556  
Longitude (second column):-87.66454238198166

I set the data type on the database as for:

Latitude DECIMAL(10,8)
Longitude DECIMAL(11,8)

The main problem arises when I try to convert data from file to database and then I get the message [Flat File Source [85]] Error: Data conversion failed. The data conversion for column "Latitude" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

View of my process:

When trying to ignore the error Latitude and Longitude values in the database are changed to NULL... The flat file encoding is 65001. I tried doing conversions for data types: float, DECIMAL, int and nothing helped. My questions are:

1. what data type for these above values should I use in the target database.
2. what data type should i choose on input for flat file ?
3. what data type to set for conversion (I suspect the one we will have on the database) ?

please note that some records in the file are missing the location

• view from Data:
• view from Data Conversion:

UPDATE

When FastParse is run I receive an error message as below:

What data type should I choose in this case ? I set everything up as @billinkc suggested. When I set an integer, for example DT_I4, it results in NULL and the same error as before (in this message there is no possibility to select some data type for the value of Latitude, i.e. DECIMAL or STRING).

question from:https://stackoverflow.com/questions/65909630/correct-type-of-data-for-latitude-and-longtitude-ssis-etl-process

Answer 1

You need DECIMAL(11,8). That has three digits before the decimal place and either digits after.

The conversion failure is no doubt happening when you have longitudes above 100 or less than -100.