时间戳

首页 > 解决方案 > 将 SSIS 参数传递给 OPENQUERY

sql - 将 SSIS 参数传递给 OPENQUERY

问题描述

请帮助我将 SSIS 中的参数传递到“OPENQUERY”中，以测试我正在使用下面的脚本但出现错误的查询：

脚本：

DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT  @Date = '28 Nov 2018'
SELECT  @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)

错误：

OLE DB provider "MSDASQL" for linked server "TEST" returned message "[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]Invalid date string (7497)".
Msg 7321, Level 16, State 2, Line 4
An error occurred while preparing the query "SELECT * FROM PUB.TEST 
WHERE Test_Date >= '28 Nov 2018'" for execution against OLE DB provider "MSDASQL" for linked server "TEST".

SSIS OLE DB 源中的脚本应如下所示：

DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT  @Date = ?
SELECT  @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)

标签： sqlsql-servertsqlssisopenedge

解决方案

将脚本修改为下面，问题出在日期格式上

DECLARE @TSQL varchar(8000), @Date varchar(11)
SELECT  @Date = '28-Nov-2018'
SELECT  @TSQL = 'SELECT * FROM OPENQUERY([TEST], ''SELECT * FROM PUB.TEST
WHERE Test_Date >= ''''' + @Date + ''''''')'
EXEC (@TSQL)

推荐阅读