Create SQL Stored Procedure to perform DTS Function Lookup

We are currently running a DTS job nightly that backs up a table from an IBM Universe DB to a local SQL DB to be queried (via a vbscript file) throughout the day.  The source table is very large and we have no control over the database.  Our DTS job is starting to fail, and the error states that it can't create a temp table on the Universe DB.

I would ideally like to create a simple Stored Procedure that does the lookup to the source Universe Database ad hoc instead of requiring a nightly backup.  This way I can just run an ADODB Query from VBScript that calls a SQL Stored Procedure.  The SP then does the DTS lookup into the source table and returns the ResultValue.  Our Lookup Value is an identity field, so I will always be returning a single result back.

The actual lookup is very simple:
select ResultValue from TransactionLog where LookupValue = '12345'

What is the best way to accomplish this?  I don't have an ODBC connection that allows me to connect to the source database (like D3 Raining Data), but I am open to that if I can find a way to do it.

I'm very comfortable with writing SQL Queries, but I've never written a DTS job (except this one created with the GUI).

Thank you.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
<<What is the best way to accomplish this?>>
From what you describe there is no need for DTS...Simply create a liked server pointing out directly at the IBM table and call the select the same way you would with a normal table except that the syntax indicates the full schema path instead of only the table name...Ex: assuming your linked server is called IBMDB2
select ResultValue from TransactionLog where LookupValue = '12345', just call
select ResultValue from [IBMDB2].[SchemaName].[owner. TransactionLog where LookupValue = '12345'

Hope this helps...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.