By: Dallas Snider | Comments (3) | Related: > Data Types
Problem
The numeric data inserted in my database table after my SQL Server Integration Services (SSIS) package executes does not look like the data from its comma-separated source file. What can I do to fix the problem?
Solution
In this tip, we will examine the importance of using the proper data types for storing numbers in both the database table and in configuring SSIS packages. In the first image below, we see the comma-separated value source file in Excel. The second image shows the results of a query after this data was loaded to the database with extra digits in each column.
When encountering this problem, make sure to inspect the source file with an application other than Excel such as Notepad++. This will ensure that your data is correct and has not been formatted by Excel. The next image shows the raw data in a text editor.
Next we need to examine the SSIS Flat File Connection Manager where we defined the columns in the file. As we can see below, the first column of the file was read by SSIS using the float data type as defined in the Flat File Connection Manager.
Also, the table has been defined with the columns having the float data type.
The float and real data types are classified as approximate number data types. We need to be using an exact number data type such as numeric. When using numeric data types, we should define the precision and the scale. The precision is the maximum number of digits on both sides of the decimal point. The scale is the number of digits to the right of the decimal point. In this example, Columns 1, 2, and 3 will have a precision of 4 and a scale of 3, while Column 4 will have a precision of 7 and a scale of 6. Scale must always be less than or equal to the precision. Let's start to fix this problem by changing the data types of our database table columns from floats to numerics with the appropriate precision and scale.
Next, let's go into the SSIS Flat File Connection Manager and change the data types of our file columns from floats to numerics with the appropriate precision and scale.
Because we changed the data type in the source, SSIS will ask us if we "want to replace the metadata of the output columns with the metadata of the external columns." Choose "Yes" and the warning triangle should disappear.
Now after we run our SSIS package the data in the table matches what is observed in the source file.
Summary
In this tip, we have shown that precision and scale do matter when working with non-integer numeric data.
Next Steps
Check out these other tips relating to data types.
- Comparing SQL Server and Oracle Datatypes
- Concatenation of Different SQL Server Data Types
- Comparing SQL Server Datatypes, Size and Performance for Storing Numbers
About the author
Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
