Using the "IN" filter in Select statement for source

[rodeomount]

I need to use a parameter to pass in one string with multiple values for use inside the "IN" filter of a select statement. Example: parameter is named myParam

I want to set myParam value to 'A2fg','A2fh','A2fi','A2fj'

And use it in the select statement inside the source query of a dts package like this


Select * from tblTable where ID in (?)

I've tried it but keep getting an error when clicking on preview that states:
[Microsoft][ODBC Driver For Oracle]Wrong Number of Parameters.

 

[carp]

I'm very new to sQL Server, so this may be absolutely worthless. However, I've run into similar problems in PL/SQL and was able to work around it by passing the target in completely enclosed in surrounding quotes - like ''A2fg','A2fh','A2fi','A2fj'' or "'A2fg','A2fh','A2fi','A2fj'". The problem you are running into is that the target routine sees 'A2fg','A2fh','A2fi','A2fj' as four different values instead of one character string. The enclosing marks make your list appear as a single value (at least, they do in Oracle). Hope this helps.

 

[rodeomount]

Does anyone know if it's possible to pass in a query as a global parameter?

I tried adding a Dynamic Properties Task to set the global variable to the values I wanted posted inside the "IN" filter of the query but it never set the entire string to the value of the global variable.

 

[fredericofonseca]

you can with 2000+. not with earlier versions

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[rodeomount]

does 2000+ include SQL Server 2000? If so could you explain how to go about doing it?

Thanks