来自带有参数的链接服务器的SSIS Ole DB Source作为存储过程 [英] SSIS Ole DB Source as Stored procedure from linked server with parameters

问题描述

全部.

我正在使用链接服务器中的存储过程.我想在ole db源代码中使用该过程.

I am using stored procedures from linked server. I want to use the procedure in ole db source.

我编写了查询，该查询可在SSIS中使用.

I wrote the query, which works in SSIS.

select ID,

LAST_NAME_ENG,

LAST_NAME_G,

FST_NAME_ENG,

FST_NAME_G,

BIRTHDATE

from openquery (linkedserver, 
'exec [linkedserver].get_records @SESSION_ID = 12 , @SYSTEM = ''oCRM'', @ENTITY_NAME = ''CLIENT''

 WITH RESULT SETS (([ID] [int] NOT NULL,

[LAST_NAME_ENG] [varchar](50) NOT NULL,

[LAST_NAME_G] [varchar](50) NOT NULL,

[FST_NAME_ENG] [varchar](50) NOT NULL,

[FST_NAME_G] [varchar](50) NOT NULL,

[BIRTHDATE] [date] NOT NULL))');

我可以在SSIS ole db源中使用它并成功获取所需的数据.但是下一步存在问题:

I can use it in SSIS ole db source and successfully get required data. But in the next step there is the problem:

我需要将参数从SSIS传递给@SESSION_ID而不是"12".而且我找不到正确的方法.

I need to pass the parameter to the @SESSION_ID from SSIS instead of '12'. And I cannot find the right way to do it.

有很多建议使用动态sql并构造具有所需参数值的完整查询字符串，然后执行它，但是如果我愿意-SSIS无法从动态查询中获取列数据.

There are a lot of advices to use dynamics sql and construct full query string with required parameter values and then exec it, but if I will do it - the SSIS couldnt get columns data from the dynamics query.

有没有解决的方法? 任何想法都会有所帮助.

Are there ways to solve it? Any ideas will be helpfull.

谢谢.

关于，尤里.

推荐答案

创建一个字符串变量，例如 SQL_Query .在变量定义中-设置 EvaluateAsExpression 并将表达式定义为"your SQL statement ... @SESSION_ID = " + [User::Session_ID_Variable] + " rest of SQL statement"，其中 Session_ID_Variable 包含您的条件值.如果 Session_ID_Variable 不是字符串-您必须使用(DT_WSTR, length)将其强制转换为字符串. SQL_Query 中的结果将是您的目标SQL表达式.
然后在OLE DB源中-将变量指定为SQL命令源，然后选择 [User :: SQL_Query] .
在所有情况下，存储过程都必须返回相同格式的结果集. SP不返回任何结果集将使DataSource失败.

Create a string variable, say, SQL_Query. In variable definition - set EvaluateAsExpression and define expression as "your SQL statement ... @SESSION_ID = " + [User::Session_ID_Variable] + " rest of SQL statement" where Session_ID_Variable contains your conditional value. If Session_ID_Variable is not string - you have to cast it to string with (DT_WSTR, length). Result in SQL_Query will be your target SQL expression.
Then in OLE DB Source - specify variable as SQL command source and select [User::SQL_Query].
Stored procedure have to return resultset of the same format in all cases. SP returning no resultset will fail DataSource.

这篇关于来自带有参数的链接服务器的SSIS Ole DB Source作为存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！