SSIS potential loss of data when converting

Question

Working with SQL server 2014 ans SSIS, I am having the following issue in a data flow:

Data Source: There was an error "The value could not be converted because of a potential loss of data.".

This is the query:

SELECT
    ISNULL(M.ActiveLEDZones, '') [ActiveLEDZones__c]
    , M.Weight [Weight__c]
    , M.WeightStand [WeightStand__c]
    , M.wifi [WIFI__c]
FROM [dbo].[ModelComparison] M 
WHERE CAST(M.CreateDate AS DATE) >= '2012-01-01'

using a distinct I get these values:

ActiveLEDZones__c
3
126
28
36
12
9
384
72
tbd
128
8
2
14
6
NA
16

1
10
0
N/A
32
18
5
64
4

Tables information:

Source: varchar(5)
Target: nvarchar(5)

I am using a data conversion in SSIS:

To be clear, you're converting ActiveLEDZones__c from varchar(5) to nvarchar(5)? — Evaldas Buinauskas
Which component in the Data Flow is producing the error? Source, Conversion, or Destination? — Troy Witthoeft
yeas I am converting ActiveLEDZones__c from varchar(5) to nvarchar(5) and the component that is giving me the error is the Source task — Pablo Gûereca
What happens if you take the Data Conversion component out? I think varchar would convert to nvarchar implicitly. Also what is the datatype of the CreateDate column in the source table? — Tab Alleman

Hadi Hadi · Accepted Answer · 2017-07-28T15:01:40

You have to consider 2 points:

1st

When using ISNULL(M.ActiveLEDZones, '') [ActiveLEDZones__c] in your query, this function will not take the original column data length varchar(5), it will take the longest length found in this query which can cause a similar issue, try using a CAST function to precise the column data length.

CAST(ISNULL(M.ActiveLEDZones, '') AS VARCHAR(5)) [ActiveLEDZones__c]

Additional Testing

use the following queries to create table and check the created table structure:

SELECT
ISNULL(M.ActiveLEDZones, '') [ActiveLEDZones__c]
, M.Weight [Weight__c]
, M.WeightStand [WeightStand__c]
, M.wifi [WIFI__c]
INTO tblTemp_1
FROM [dbo].[ModelComparison] M 
WHERE CAST(M.CreateDate AS DATE) >= '2012-01-01'

AND the following

SELECT
CAST(ISNULL(M.ActiveLEDZones, '') AS VARCHAR(5)) [ActiveLEDZones__c]
, M.Weight [Weight__c]
, M.WeightStand [WeightStand__c]
, M.wifi [WIFI__c]
INTO tblTemp_2
FROM [dbo].[ModelComparison] M 
WHERE CAST(M.CreateDate AS DATE) >= '2012-01-01'

you will see that tblTemp_1 [ActiveLEDZones__c] column differs from tblTemp_1 [ActiveLEDZones__c]

2nd

You can use CAST(ISNULL(M.ActiveLEDZones, '') AS NVARCHAR(5)) instead of ISNULL(M.ActiveLEDZones, '') so the source column will be readed as Nvarchar(5) and there is no need to use Data Conversion Componenent

the query will be

SELECT
CAST(ISNULL(M.ActiveLEDZones, '') AS NVARCHAR(5)) [ActiveLEDZones__c]
, M.Weight [Weight__c]
, M.WeightStand [WeightStand__c]
, M.wifi [WIFI__c]
FROM [dbo].[ModelComparison] M 
WHERE CAST(M.CreateDate AS DATE) >= '2012-01-01'

SSIS potential loss of data when converting

1 Answers