Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Using the "IN" filter in Select statement for source

Status
Not open for further replies.

rodeomount

Programmer
Sep 25, 2007
50
US
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.

 
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.
 
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.
 
does 2000+ include SQL Server 2000? If so could you explain how to go about doing it?

Thanks
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top