sql server dts: SSIS Parameters with Multiple Values (For IN clauses) -- sql server dts

SSIS Parameters with Multiple Values (For IN clauses)

Adam St. Pierre
3/27/2007 6:03:15 PM

sql server dts: Hello, everyone!

I'm attempting to develope an SSIS package that uses variables passed to
determine selected values.

I'm using an OLE DB Source, and passing the query in that source values from
paramters.

For example:

Select * from dbo.Test where Value1 = ?

This works fine. However,

Select * from dbo.Test where Value1 IN (?)

... does not. The parameter is given the value 'blah1','blah2'.

Why does it not accept this format, with strings or integers? Is there any
way around this that does not involve a static set of parameters?

Re: SSIS Parameters with Multiple Values (For IN clauses)

Allan Mitchell
3/28/2007 5:47:59 AM

Hello Adam,

The way I would do this is to use a Variable and build the statement. Have
a variable say of SQLStatement and one of Arguments

SQLStatement would be set to EvaluateAsExpression = true

The Expression would be something like

"SELECT * FROM dbo.dimCustomer WHERE FirstName IN (" + @[User::Arguments]
+ ")"

Arguments would have an initial value of ''

If I change that now to 'Jon','Eugene'

It works as expected

--

Allan Mitchell
http://wiki.sqlis.com | http://www.sqlis.com | http://www.sqldts.com |
http://www.konesans.com

[quoted text, click to view]