Error 0xC0202009 occurs when SSIS does a parameter cast in SQL Server
This article helps you resolve the error 0xC0202009 that occurs when Microsoft SQL Server Integration Services (SSIS) does a parameter cast in SQL Server.
Original product version: SQL Server
Original KB number: 3001293
When you run an SSIS package, the execution fails because of a parameter cast error, and you receive the following error message:
Source: Data Flow Task
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0"
Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
For an OLE DB Source component, when you have a data flow task that contains a parameterized query, you may experience this issue. For example, you have the following query:
SELECT mydate FROM dbo.myTable WHERE mydate >= convert (char, dateadd(year,-1,cast( ? as datetime)))
This issue only occurs if you try to use an OLE DB Source component together with parameters in the query string. A parameter marker
? is mapped to an SSIS user variable parameter1 that is defined as SSIS String
The issue occurs because of the behavior change in how OLE DB handles parameters. In Microsoft SQL Server 2012, the new stored procedure of
sp_describe_undeclared_parameters , replacement of set
fmtonly returns a different result for the parameter type. This change is by design.
In the example query in the Symptoms section, the original behavior is to describe
? to be
char(8). However, the new sp_describe_undeclared_parameters says that
? should be
datetime. Therefore, the internal cast from string to
datetime that is handled by OLE DB provider returns the error message.
To resolve this problem, rewrite the query to add an additional explicit cast to the original data type. Then,
sp_describe_undeclared_parameters returns the correct, expected type. To resolve this issue in the example query that is described in the Symptoms section, update the query as follows:
SELECT mydate FROM dbo.myTable WHERE mydate >= convert(char ,dateadd(year,-1, cast( cast( ? as char(8)) as datetime)))