Hi,

In DTS Lookup, I want to write a query as

SELECT col1
FROM Table1
WHERE col2 like '%0001' ( this would be parameter) this returns one row.

col2 is varchar column

I wrote it as

SELECT col1
FROM Table1
WHERE col2 like ?

But it doesnt seems to work.

Any idea what should be the value of parameter. I tried passing value as '%0001', '0001', %0001, but no luck... i.e. it doesnt return any row.

Any help would be appreciated..

Regards,
Nilay.

You can adapt this example from QA

declare @au_fname varchar(30)
set @au_fname = 'ean'

select * from Authors where au_fname like '%' + @au_fname


I just plugged this into an ExecuteSQL task and it works

select * from authors where
au_fname like '%' + ?



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


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

It works in ExecuteSQL task but it doesnt work in Data Transformation Lookup.. I need specifically in lookup because while transformation I have to find corresponding value..

"Allan Mitchell" wrote:

Works for me

In my package I am transferring Pubs:authors to a text file

In my transformations I have



dim val

val = DTSLookups("lkp").Execute("ean")

msgbox val


DTSDestination("au_id") = DTSSource("au_id")
DTSDestination("au_lname") = DTSSource("au_lname")
DTSDestination("au_fname") = DTSSource("au_fname")
DTSDestination("phone") = DTSSource("phone")
DTSDestination("address") = DTSSource("address")
DTSDestination("city") = DTSSource("city")
DTSDestination("state") = DTSSource("state")
DTSDestination("zip") = DTSSource("zip")
DTSDestination("contract") = DTSSource("contract")
DTSDestination("XXX") = DTSSource("XXX")
DTSDestination("rowguid") = DTSSource("rowguid")
Main = DTSTransformStat_OK


My Lookup Query looks like this

SELECT au_id
FROM authors
WHERE (au_fname LIKE '%' + ?)





--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Nilay Shah" <NilayShah (AT) discussions (DOT) microsoft.com> wrote

to find corresponding value..