大网数据广告活动
数据库与应用安全

Error 0xC0202009 occurs when SSIS converts parameter

· 52CE 我爱测

This article provides resolutions for the error 0xC0202009 that 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

Symptoms

When you run an SSIS package, the execution fails because of a parameter cast error, and you receive the following error message:

Code: 0xC0202009
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)))

Note: 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 20080122.

Cause

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.

Resolution

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)))

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 09/02/2020
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。