3

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.".

Hadi
  • 36,233
  • 13
  • 65
  • 124
chaneb
  • 67
  • 1
  • 6
  • `"Normal"` is never going to be able to be cast to a `(DT_UI1)`; it's most certainly *not* an integer. The same is true for all of those values in your inline if. Why are you trying to cast them to an `DT_UI1`? What `DT_UI1` value are you expecting a value of `"Normal"`, `"Divers"` and `"No"` to be converted to? – Thom A Jan 25 '22 at 16:54
  • Depends what solution you are after, @chaneb . There is no solution to trying to convert the `DT_WSTR` value `"Normal"` to a `DT_UI1`, no; it's in no way a 1 byte unsigned integer. hence why I asked why you're trying to convert said values to a `DT_UI1`. – Thom A Jan 25 '22 at 17:02
  • I removed them but it didn't work "Invalid character value for cast specification" – chaneb Jan 25 '22 at 17:11
  • How did you assume that the `Derived column` is causing the issue while the error shows that it was thrown by the `OLE DB Destination` ?! – Hadi Jan 25 '22 at 18:49
  • It helps to look into VIEW menu --> Errors list in Visual Studio SSIS, that lists out WARNINGS - resolve them proactively! – HydTechie Nov 22 '22 at 17:48

1 Answers1

0

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:

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124