OLE DB Destination: Invalid character value for cast specification

My table source:

num_facture	TYPE	actif	date
1	1	1	2010-01-31 00:00:00.000
2	2	1	2011-01-31 00:00:00.000
3	3	2	2012-01-31 00:00:00.000
4	4	2	2013-01-31 00:00:00.000

The Column data types are:

Column	Data Type
TYPE	tinyint
Actif	tinyint
date	datetime

I’m working with SSIS to load my destination table.

So, I want to change TYPE for

• Normal if 1
• Divers if 2
• Intra-Société if 3
• Prospect if 4

Also, to change actif to:

• no if 1
• yes if 2

Also, get the seniority from the date

So, I created a Derived Column transformation with 3 columns as follows:

Type Column

TYPE == 1 ? "Normal" : TYPE == 2 ? "Divers" : TYPE == 3 ? "Intra-Société" : TYPE == 4 ? "Prospect" : ""

Actif Column

actif == 1 ? "No" : actif == 2 ? "yes" : ""

Date Column

DATEDIFF("YY",date,GETDATE())

All of these transformations didn’t work.

[OLE DB Destination [288]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Invalid character value for cast specification”. An OLE DB record is available. Source: “Microsoft SQL Server Native Client 11.0” Hresult: 0x80004005 Description: “Invalid character value for cast specification”.

[OLE DB Destination [288]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[Derived Column actif] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: “The value could not be converted because of a potential loss of data.”.

Answer

The main exception is:

Invalid character value for cast specification.

The value could not be converted because of a potential loss of data.

Check the destination SQL table columns data types and the columns mapping on the destination.

As the Error description provided in the question shows, it was thrown by the OLE DB Destination component. This means that Derived Column Transformations are working fine.

I suggest reading the following article to learn more about SSIS data types, data conversion transformation, implicit and explicit conversion:

• SSIS Data types: Change from the Advanced Editor vs. Data Conversion Transformations

Suppose the data type mismatch is caused by the values generated by the Derived Column. In that case, you can try using a casting operator or a Data Conversion Transformation to solve the conflict.

Besides, you should be aware that the Derived Column transformation outputs are of type DT_WTR or DT_STR, so they should be mapped to a VARCHAR or NVARCHAR columns in the destination table.

---

Update 1

Based on our discussion on your related question: Derived Column to handled mulitple conditions in CASE statement?. It looks like you are trying to insert a string value within a tinyint column as you are trying to replace the type code with the equivalent string value and reinsert it into the SQL table.

You should not edit your data since it is well designed and stored. You should create another table (example: TypeReference(Id tinyint, Description VARCHAR(50)) and store all the string values that you are using in your CASE statement. Then you should join both tables once you need to retrieve the string values.

For example:

SELECT myTable.*, TypeReference.Description 
FROM myTable LEFT JOIN TypeReference 
     ON myTable.Type = TypeReference.Id

Besides, if creating the SSIS package is only for retrieving those values, there is no need to create this package.