引言

在商业智能开发工作室 (BIDS) 中工作并使用OleDb 连接时，我遇到了一个问题，即我只能在非常有限的地方（例如 WHERE 子句中）明确使用 SQL 查询中的参数

SELECT Name
FROM Source1
WHERE Name = ?

尝试在其他地方使用它们会导致错误。以下是几个简单的例子

SELECT ?

SELECT Name, UPPER(Name + ?) AS 'UpperName'
FROM Source1

SELECT s1.Name, s2.Name
FROM Source1 s1
FULL OUTER JOIN Source2 s2 ON s1.Name + ? = s2.Name

每个都导致以下错误

无法从 SQL 命令中提取参数。提供程序可能无法帮助从命令解析参数信息。在这种情况下，请使用“来自变量的 SQL 命令”访问模式，其中整个 SQL 命令存储在变量中。

建议的解决方案是使用“来自变量的 SQL 命令”选项而不是“SQL 命令”。通过创建字符串变量并将我们的查询移动到那里，然后通过将数据访问模式更改为提到的选项，我们可以解决大多数此类问题，但是... 字符串变量最多可以包含 4000 个字符。如果我们的查询更长，并且我们无法缩短它或将其转换为存储过程，并且必须按原样使用它，那么这里有一种我将在这里描述的技巧。

准备工作

让我们从我将在这里使用的数据库和表开始

CREATE DATABASE TestDatabase
GO

USE TestDatabase

CREATE TABLE Source1(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
CREATE TABLE Source2(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
CREATE TABLE Destination(Name NVARCHAR(10) NOT NULL PRIMARY KEY)
GO

INSERT INTO Source1 VALUES(N'Source1')
INSERT INTO Source2 VALUES(N'Source2')
GO

我们应该得到一个如此简单的数据库

现在转到 BIDS 并准备一个包

可见的数据流包含在以下控制流组件中

让我们做一个用例测试。我想从源表中提取名称，但我不知道要使用哪个源。因此，我使用一个名为 TableId 的整数变量来确定我的源。请编辑 OLE DB Source 组件并将以下 SQL 查询写为 SQL 命令

现在单击“参数”按钮，注意到了一个错误被引发

脚本任务

错误的建议解决方案是使用“来自变量的 SQL 命令”选项而不是“SQL 命令”，如果我们的查询不超过 4000 个字符，我们可以这样做。但这里并非如此。为了简单起见，我们需要想象我们的查询大于可用限制，并且我们无法将其放入字符串变量。因此，我们需要找到一种方法来绕过此限制。

我将在此处描述的解决方案是使用名为 Script Task（使用 C# 语言）的控制流组件。请按如下方式将 Script Task 添加到控制流中

下一步是为 SQL 查询和我们要加载的源表 ID 准备变量。当然，我们不能像原来那样将原始查询放入字符串变量中，因为它太大了！ （我们需要想象这一点）。但是我们也不能将其留空，因为 BIDS 假定变量包含可以在设计时进行解析、验证和开箱即用的真实 SQL 查询。为了安抚 BIDS，我们需要在那里放置一些东西，这将定义所有列及其类型。我使用 select 语句，其中 NULL 值转换为特定类型的所有列，或者使用空字符串和零用于更简单的类型。在这里，我们将使用空字符串作为 Name 列的占位符

此外，我们需要指出我们的变量包含一个表达式。使用变量属性将 EvaluateAsExpression 选项设置为 true

由于我们有 SQL 变量，我们需要在运行时使用 Script Task 更改它。为了实现这一点，我们需要在 ReadOnlyVariables 部分中声明 TableId 变量，并在 ReadWriteVariables 部分中声明 SqlQuery 变量

之后，我们就可以编辑脚本了

单击“编辑脚本...”按钮（稍后）后，Visual Studio 出现了一些样板。我们需要导航到 Main 方法，该方法将包含“TODO: 在此处添加您的代码”注释

将 TODO 注释替换为以下代码

var tableId = (int)Dts.Variables["User::TableId"].Value;

var sqlQuery = Dts.Variables["User::SqlQuery"];
sqlQuery.EvaluateAsExpression = false; // Without this line, variable won't be updated.
sqlQuery.Value = string.Format("SELECT Name FROM Source{0}", tableId); // Update query.

代码简单易懂。我们正在获取声明的变量，并以其最终形式准备新的 SQL 查询。这是填充的 Main 方法的屏幕截图

单击保存按钮并关闭 Visual Studio 后，我们的 Scipt Task 就可以使用了。但是，我们还没有完成数据流。编辑 OLE DB Source 组件并将数据访问模式设置为“来自变量的 SQL 命令”。然后选择 User::SqlQuery 作为变量名。请注意，我们只看到具有定义的占位符的静态值

将 OLE DB Source 连接到 OLE DB Destination

是时候编辑 OLE DB Destination 了。我们可以这样做，因为 BIDS 知道由于我们在 SqlQuery 变量中使用占位符，从 OLE DB Source 传输了哪些列

还剩下一件事。由于我们使用空 unicode 字符串而不是 NULL 转换为 NVARCHAR 作为列占位符（空字符串较短），我们需要显式增大该列的输出字符串长度。打开 OLE DB Source 的高级编辑器。导航到“输入和输出属性”选项卡页面。从“输出列”中选择 Name 列，并将其 Length 属性设置为足够大小

调试代码

最后，我们准备好执行 Task。在 Data Flow Task 上设置断点。请注意，SqlScript 变量已被 TaskScript 更新

数据流表明已传输了一行

查询 Destination 表显示有效结果

希望这个技巧对您有所帮助！

感谢您的阅读 :)