SSIS:从字符串变量中过滤多个 GUID 作为数据流 OLE 源中的参数 [英] SSIS: Filtering Multiple GUIDs from String Variable as Parameter In Data Flow OLE Source

问题描述

我有一个 SSIS 包，可以从 SQL 表中获取新的 GUID 列表.然后我将 GUID 分解成一个字符串变量，以便用逗号将它们分开.它们如何出现在变量中的一个例子是:

I have an SSIS package that obtains a list of new GUIDs from a SQL table. I then shred the GUIDs into a string variable so that I have them separated out by comma. An example of how they appear in the variable is:

'5f661168-aed2-4659-86ba-fd864ca341bc','f5ba6d28-7283-4bed-9f11-e8f6bef225c5'

问题出在数据流任务上.我在 SQL 查询中使用该变量作为参数来获取源数据，但无法获取结果.当 WHERE 子句看起来像:

The problem is in the data flow task. I use the variable as a parameter in a SQL query to get my source data and I cannot get my results. When the WHERE clause looks like:

WHERE [GUID] IN (?) 

我收到一个无效字符错误，所以我发现隐式转换不能像我想象的那样与 GUID 一起工作.如果这是单个 GUID，我可以通过将 {} 放在 GUID 周围来解决这个问题，但有可能需要在运行时检索 4 或 5 个不同的 GUID.

I get an invalid character error so I found out the implicit conversion doesn't work with the GUIDs like I thought they would. I could resolve this by putting {} around the GUID if this were a single GUID but there are a potential 4 or 5 different GUIDs this will need to retrieve at runtime.

认为我可以通过这个来解决它:

Figuring I could get around it with this:

WHERE CAST([GUID] AS VARCHAR(50)) IN (?)

但这根本不会产生任何结果，在我当前的测试中应该有两个.

But this simply produces no results and there should be two in my current test.

我想一定有办法做到这一点......我错过了什么?

I figure there must be a way to accomplish this... What am I missing?

推荐答案

你不能，至少不能使用你提供的机制.

You can't, at least not using the mechanics you have provided.

我愿意在这一点上被证明是错误的，但如果我能做到这一点，我会被诅咒.

I'm open to being proven wrong on this point but I'll be damned if I can make it work.

诀窍是老派，并通过字符串构建/连接进行查询.

The trick is to just go old school and make your query via string building/concatenation.

在我的包中，我定义了两个变量，filter 和 query.filter 将是您已经在执行的串联.

In my package, I defined two variables, filter and query. filter will be the concatenation you are already performing.

查询将是一个表达式(右键单击，属性:将 EvaluateAsExpression 设置为 True，表达式将类似于 "SELECT * FROM dbo.RefData R WHERE R.refkey IN (" + @[User::filter]+ ")"

query will be an expression (right click, properties: set EvaluateAsExpression to True, Expression would be something like "SELECT * FROM dbo.RefData R WHERE R.refkey IN (" + @[User::filter] + ")"

在您的数据流中，然后将源从变量更改为 SQL 命令.那里不需要映射.

In your data flow, then change your source to SQL Command from variable. No mapping required there.

基本的外观和感觉就像

OLE 源查询

这篇关于SSIS:从字符串变量中过滤多个 GUID 作为数据流 OLE 源中的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！