在 SSIS Select 和 From 子句中使用变量 [英] Use variables in SSIS Select and From clause

问题描述

我正在导入几家商店的销售和采购成本，其中每家商店都有自己的包裹(以利用并行性).设置这个可能相当耗时，因为我必须进入每个商店 OLE DB Source 并调整 FROM 子句以捕获一个商店有一个单独的数据库(商店编号 9999 的数据库是 [database].[dbo].[sales9999]).

I'm importing several stores' sales and purchase COSTs where each store has its own package (to utilize parallellism). Setting this up can be rather time consuming, as I have to to go into each stores OLE DB Source and adjust the FROM clause in order to capture that a store has a seperate data base (the database for store number 9999 is [database].[dbo].[sales9999]).

我还想在每个商店的 Select 子句中使用商店编号.例如，对于客户 9999，我需要将 Select 9999 AS StoreKey 作为列.

I also want to use the store number in the Select clause for each store. For instance for customer 9999 I need to have Select 9999 AS StoreKey as a column.

因此我想要这样:

SELECT ? as StoreKey, a.saleprice, b.purchasecost
FROM ? AS a JOIN ? AS b ON a.ID = b.ID 

在我的 OLE DB 源中，将 ? 与相应的参数匹配.但是，当我尝试实现这一点时，我收到了一个错误，即参数只能在我不需要的 Where 子句中使用.

In my OLE DB Source, and match the ?'s with the corresponding parameters. However when I tried to implement this I've received the error that parameters only can be used in the Where clause, which I don't need.

有人有什么想法吗?

问候，

推荐答案

借助将变量作为表达式求值，您当然可以做到这一点.例如，你的 select 语句可以写成下面的变量表达式:

You can certainly do it with an aid of Variable being evaluated as an Expression. For example, your select statement can be written as the following Expression for Variable:

"SELECT N'"+@[User::strStoreKey]+"' as StoreKey, a.saleprice, b.purchasecost 
    FROM "+@[User::strTableName]+" AS a JOIN "+@[User::strAnotherTable]+"
    AS b ON a.ID = b.ID"  

这里的变量必须定义为字符串数据类型或在表达式中强制转换的类型.
使用这种方法时要记住的几个要点:

Here the variables have to be defined with String data type or type casted at Expression.
Several important points to mind when using this approach:

1. 所有Select 查询的结果集在列、名称和数据类型方面必须相同.
2. 您必须提供默认值来查询构建变量，即 strStoreKey、strTableName 和 strAnotherTable，这会导致有效的查询.否则您的包在保存时将无法通过验证.
3. 您的数据流的属性设置如下DelayValidation=True.这会在执行之前验证数据源或目标，而不是在包加载时验证.

1. Result sets of all Select queries have to be identical, in terms of columns, its names and data types.
2. You have to provide default values to query building variables, i.e. strStoreKey, strTableName and strAnotherTable which results in valid query. Otherwise your package will fail validation at saving.
3. Your Data Flow should have property set as follows DelayValidation=True. This validates Data Source or destination right before execution, not at Package load.

当目标表名称设置为变量时，您也可以将此方法用于可变目标；同样的限制适用.

You can use this approach with variable destinations as well, when destination table name is set at variable; the same restrictions apply.

这篇关于在 SSIS Select 和 From 子句中使用变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！