SSIS : Importing CSV file to the table with INT datatype columns and issue with decimals.

Question

Hi Team - We get CSV file with the below sample values

Partnumber, Org, Month1, Month2, Month3, Month4
12345, CA, 0,9,8,7

9999, TX, 1,2,3.6,5

My table is being loaded like here : 12345 CA 0 9 8 7
9999 TX 1 2 4 5

if you see here the value 3.6 is being rounded off automatically and loading to table as 4. I don't want this to be rounding off.

Actually, the file should NOT load into table if there is a decimal value in the file. Could you please let me know how to throw error/fail the process if there is a decimal value in the file ? Thank you in advance

My table structure is like below

   CREATE TABLE TABLEA ([Part_Number] [varchar](50) NULL,

	[Org_Name] [varchar](500) NULL,

	[Month1] [int] NULL,

	[Month2] [int] NULL,

	[Month3] [int] NULL,

	[Month4] [int] NULL)

Answer 1

Hi @kkran

You may use Execute SQL Task to do that.

Step1: Create a staging table first in your database with below code. And then load the csv file data to the staging table first.

 CREATE TABLE staging table ([Part_Number] [varchar](50) NULL,

	[Org_Name] [varchar](500) NULL,

	[Month1] VARCHAR(MAX) NULL,

	[Month2] VARCHAR(MAX) NULL,

	[Month3] VARCHAR(MAX) NULL,

	[Month4] VARCHAR(MAX) NULL)

Step2: Add the Execute SQL Task with below command.

declare @count int
select @count = (SELECT sum(CASE WHEN CHARINDEX('.',[Month1],0) > 0 THEN 1 
                             WHEN CHARINDEX('.',[Month3],0) > 0 THEN 1 
							 WHEN CHARINDEX('.',[Month2],0) > 0 THEN 1 
							 WHEN CHARINDEX('.',[Month4],0) > 0 THEN 1 ELSE 0 
								END)
FROM 
staging table)
if @count >0
begin
    raiserror('There is decimal in my_table',16,1)
end
else
begin
 INSERT INTO TABLEA SELECT * FROM staging table
end

And then it will throw error/fail the process if there is a decimal value in the file.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 2

Hi @kkran,

*.csv/txt flat files always have host of problems where column delimiters, column separators, invisible characters like null terminators \0, special characters based on encoding, and line breaks are in the middle of the actual data.

The most reliable format for data feeds is XML enforced by an XSD. An XSD plays a role of a data contract between sender and receiver. It will guarantee proper data format/shape, data types, cardinality, and enforce data quality.

SSIS has for that XML Task, operation Validation: https://learn.microsoft.com/en-us/sql/integration-services/control-flow/validate-xml-with-the-xml-task?view=sql-server-ver16