ssis - 使用Foreach从multipe dbs读取 [英] ssis - using Foreach to read from multipe dbs

问题描述

目前，我有一个连接到20多个数据库（相同的结构）的控制流程

这是一个数据流的例子：

OLE DB Source实际上是不同的。

是否可以使用在这种情况下，Foreach循环，以便我避免这些重复？
如果可能，怎么做？

谢谢，

Ilija

我找不到任何好文章，介绍如何连接到不同的dbs。

编辑：

我找到了一篇描述大部分需要的东西的文章： http://sql-bi-dev.blogspot.com/2010/07/dynamic-database- connection-using-ssis.html

我遵循这里的所有步骤形式，但我不明白与查询的一部分。这是来自该文章的查询：

pre $ c $ SELECT $'TableName，N'ConnString，0 RecordCount，GETDATE（）ActionTime

我不明白这个语法，我想这是错误的来源：

[OLE DB来源 1 ]错误：在输出OLE DB源输出（11）时，输出列
ConnString（37）出现错误。返回的列
的状态是：文本被截断或者一个或多个字符在目标代码页中没有匹配
。

[ OLE DB来源 1 ]错误：输出列ConnString（37）
失败，因为截断发生，截断行处置
在输出列ConnString（37）指定截断失败。
指定
组件的指定对象发生截断错误。
$ b $ [SSIS.Pipeline]错误：SSIS错误代码DTS_E_PRIMEOUTPUTFAILED。组件OLE DB Source（1）上的
PrimeOutput方法返回错误
代码0xC020902A。当
管道引擎调用PrimeOutput（）时，组件返回失败代码。失败代码
的含义由组件定义，但错误是致命的，并且管道
停止执行。在这个
之前可能会发布错误消息，提供有关失败的更多信息。

我希望从表中获取数据所谓的机构（我在变量SourceTable中定义='机构' - 如果我设置不存在的表，那么我得到错误，所以我想连接到源数据库是好的）...它混淆了我，我还没有定义任何地方哪些列我需要，所以我猜错误是在这个查询，但我不明白它的语法。任何建议吗？

编辑2：

我遇到的另一个问题是，消息框始终返回相同的值在连接字符串中 - 变量ConnString不会更改。如何从SourceList变量赋值给它？

解决方案

我认为这是可能的。我会这样：

1. Foreach Loop的Collection应该是Foreach Item Enumerator。在Enumerator配置面板的这一点上，您可以创建一个小表。添加一个字符串类型的列。
   
2. 创建一个字符串变量并将其与该列相关联：在Foreach循环编辑器中转到变量映射，选择字符串变量（例如User :: ConnString）给出索引0.循环将遍历您在第一步中设置的条目，并且可以通过此User :: ConnString变量访问值。
   
3. 添加数据流任务进入Foreach循环容器。里面设置了一个OLE DB Source。它需要一个连接管理器。选择与OLE DB连接关联的连接管理器，转到属性，表达式。选择ConnectionString属性，然后在表达式列中键入@ [User :: ConnString]（或使用变量名称）。
   找到您当前使用的所有连接字符串并将其输入到Enumeration Items中。

我认为使用这些步骤可以简化您的数据流。

此过程假定您在所有数据库中都具有相同的表名称。在其他情况下，您也可以迭代表名来连接。 （在Enumeration配置中的第二列，并用适当的表名填充单元格，还需要第二个变量。）

当然，您不必复制整个连接字符串，但它的变化的一部分。我这种情况下，你应该适当地形成你的表达。

编辑

您是对。这个例子有一些问题。 （我认为你的解决方案有点复杂，但是可以使用。）

第一个是（如你所提到的）它没有将值传递给变量。我将用这些完成第六步：将User :: SourceList设置为ADO对象源变量后，选择左侧的变量映射。在Variables列中，首先选择User :: ConnString（将索引设置为0），然后User :: SourceTable second（将索引设置为1）。这将解决你的第二个编辑与消息框。从现在起它应该显示正确的值。

第二个问题是你写的，带有这个截断错误。如果仔细查看错误消息，他们会说一些字符串列的长度为1.这就是为什么发生一些截断的原因。要解决这个问题，请右键单击OLE DB源，选择显示高级编辑器。转到最后一个选项卡（输入和输出属性），展开OLE DB源输出/输出列。首先选择TableName。在右边，在数据类型属性中，你应该看到Lenght = 1。（SSIS无法正确确定这个列的长度，因为你选择SQL命令变量作为数据访问模式，我猜）。根据结果​​表定义，将此值设置为128.还要将ConnString列的长度从1更改为256.

在这些更改之后，示例应该可以正常工作。 （在我的情况下，它的工作。）这个例子使用了我写的很多相同的概念，只是它从数据存储获取连接字符串，而不是从包本身。前者可以是一个更强大的解决方案，我接受。我希望你可以使你的包工作。请，让我知道如果没有。

Currently, I have a control flow that connects to more than 20 dbs (same structure)

In this controlf flow everything is the same except Data Flows which are different only in connection to db. This is example of a dataflow:

OLE DB Source is actually what's different.

Is it possible to use a Foreach loop in this case so that I avoid these repeatings? If possible, how to do it? I couldn't find any good article which describes how to connect to different dbs.

Thanks,
Ilija

EDIT:
I found an article which describes most of the thing I need: http://sql-bi-dev.blogspot.com/2010/07/dynamic-database-connection-using-ssis.html

I followed all steps form here but i don't understand a part with query. This is a query from that article:

SELECT '' TableName, N'' ConnString, 0 RecordCount, GETDATE() ActionTime

I don't understand this syntax and I guess this is where the errors come from:

[OLE DB Source 1] Error: There was an error with output column "ConnString" (37) on output "OLE DB Source Output" (11). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.".

[OLE DB Source 1] Error: The "output column "ConnString" (37)" failed because truncation occurred, and the truncation row disposition on "output column "ConnString" (37)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "OLE DB Source" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.

I would like to get data from table called Agency (which I defined in variable SourceTable = 'Agency' - if I set non-existing table then I get error, so i guess the connection to Source DB is ok)... and it confuses me that I haven't defined anywhere which columns I need, so I guess an error is in this query, but I don't understand its syntax. Any suggestion?

EDIT 2:
Another problem I ran into is that Message box always returns me the same value in Connection string - Variable ConnString doesn't change. How can I assign value from SourceList variable to it?

解决方案

I think it is possible. I would make this way:

1. Foreach Loop's Collection should be "Foreach Item Enumerator". At this point in the Enumerator Configuration panel you can make a little table. Add a column with a type string. Here will come the connection strings of the databases.
2. Create a string variable and associate it with this column: Go to Variable Mappings in Foreach Loop editor, choose the string variable (e.g. User::ConnString), give index 0. The loop will iterate through the entries you set in first step, and you can access the values through this User::ConnString variable.
3. Add a Data Flow task in to the Foreach Loop container. Inside set up an OLE DB Source. It needs a connection manager. Select the connection manager associated with OLE DB Connection, go to Propertes, Expressions. Select the ConnectionString property, and type @[User::ConnString] to expression column (or use your variable name).
4. Locate all connection string you currently use and type them to Enumeration Items.

I think with these steps you can simplify your data flow.

This process assumes that you have the same table names in all databases. In other case you can also iterate the table names to connect to. (Second column in the Enumeration configuration and fill the cells with the apropriate table names. You also need a second variable.)

Of course you not have to copy the whole connection string but the changing part of it. I this case you should form your expression appropriately.

EDIT

You are right. There are some problem with this example. (I think it's a bit complicated for your solution, but it can be used.)

The first one is (as you mentioned) it did not pass the values to the variables. I would complete the 6th step with these: After you set User::SourceList as ADO object source variable, select Variable Mappings on the left side. In Variables column choose User::ConnString first (set index to 0), then User::SourceTable second (set index to 1). This will solve your second edit with the MessageBox. From now on it should show the right values.

The second problem is what you wrote, with this truncation error. If you look at the error messages carefully they say that some string columns have length of 1. And that's why some truncation occures. To solve this, right click OLE DB source, choose Show Advanced editor. Go to the last tab (Input and Output Properties), expand OLE DB Source output/Output columns. Choose TableName first. On the right side, in Data Type Properties you should see Lenght = 1. (SSIS could not correctly determine the length of this column, because you choose SQL command variable as Data access mode, I guess). According to the Result table definition, set this value to 128. Also change the length of ConnString column from 1 to 256.

After these changes the example should work. (In my case it worked.) This example uses much the same concept that I wrote, just it gets the connection string from a data store but not from inside the package itself. Former can be a more robust solution, I accept. I hope you can make your package work. Please, let me know if not.

这篇关于ssis - 使用Foreach从multipe dbs读取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！