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，我可以通过在{{}周围加上{}来解决，但是可能需要在运行时检索到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.过滤器将是您已经执行的串联.

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 Command.那里不需要映射.

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

基本的外观和感觉就像

OLE源查询

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