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).