SSIS: Filtering Multiple GUIDs from String Variable as Parameter In Data Flow OLE Source

Question

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'

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 (?) 

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?

Answer 1

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

You cannot concatenate values and make that work with a parameter.

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

How can I make it work?

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

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

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] + ")"

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

Basic look and feel would be like

OLE Source query