How to create a dynamic IN query in SSIS 2008?

Question

I have a variable @csv which hold a comma separated value such as: -a -a,b -a,b,c

I need to pass it in a query in my OLE DB source in a data flow to create a query such as:

SELECT COUNT(1) FROM table WHERE col1 IN @csv

So if @csv="a,b" then internally it should resolve into

SELECT COUNT(1) FROM table WHERE col1 IN 'a','b'

How can this be best achieved in SSIS 2008? Can I avoid the script component to create a dynamic query and storing it in a variable?

Answer 1

How can this be best achieved in SSIS 2008? Can I avoid the script component to create a dynamic query and storing it in a variable?

The easiest/best way would still be with a script component.

Otherwise you could:

1. use the csv as data source and select your result
2. use the and "add column" tool to add the rest of your SQL query around the result
3. store the result into a variable
4. Then use a the OLE DB datasource with "query from variable"