I'm trying to create a transform data task using the following query:
select * from Code_Authorizations_phi where Authorization_pkey in (Select
pkey from Code_Authorizations where dir_pkey = ?)

When I click the 'parameters' button or 'Parse Query' button I get the
following error:
Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Parameter Information cannot be derived from SQL
Statement with Sub-select Queries. Set parameter information before preparing
command.

Is there anyway that I can use a parameter as part of this sub-query?

Thanks!

I think the better way of doing it is to create a stored procedure
You may create a stored procedure and pass the paramemter and return the
result set like:

Create procedure ProcedureName(@CustomerID nvarchar(50)
as
Select * from customers where CustomerID in (Select customerid from Orders
where customerid = @CustomerID)

Then in the Execute SQL Task
Exec ProcedureName ?

Ed


"CBarry" wrote:

You could of course use a Stored proc or function to do this but you can
also do this

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.konesans.com - Consultancy from the people who know


"CBarry" <CBarry (AT) discussions (DOT) microsoft.com> wrote