Using global variable in DTS

Question

Using global variable in DTS

wondemh

Mr or Mrs. 500

Points: 536
More actions
August 9, 2001 at 1:15 pm

#104664

Hi everyone,
I have a problem.
Using DTS I created an Execute SQL task that queries an SQL Server table for the maximum activity date and puts this date in a global variable. Then I use that value in the global variable in a Transform Data task to query the Oracle table by writing a script that says:
select * from Oracle_table where activity_date > ?
? refers to the global variable
This fails when I try to reference the global variable.
It works when the Transform data task is used to transfer data b/n two SQL Server databases
Thanks
Henok

Henok

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Sean Burke SSCommitted Points: 1504 More actions · Answer 1

Henok,

I didn't want a day to go by without letting you know someone was looking into your issue. Fridays can get a bit crazy, but one of us should get you some info tonight. Let us know if you figure it out before we do!

Sean

Sean Burke SSCommitted Points: 1504 More actions · Answer 2

In what manner does the task fail? Does it produce an error, or does it just not return the correct results (you were expecting to get ten records, but it gave you none - or twenty)?

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 3

Hi Sean,

Thanks very much for your quick reply. I am sorry for not getting back to you on time. Here's the problem: I am able to create a global variable and assign a date value to it in an Execute SQL task. Then I create the Transform Data task that copies data from an Oracle table to the SQL Server table. The 'source' tab in the Transform data task contains the script to run against the Oracle database. When typing the sql script in the Source tab of the Transform Data task, I should be able to hit the 'parameters' button to select an input global variable (the same variable to which I assigned a date value earlier). But clicking on the parameters button gives me an error. So the problem is that I am not even able to select an input parameter for my query that is to be run in the source tab.

Thanks for your help!

Henok

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 4

Sean,

Here's the sql script in the source tab:

select * from Oracle_table where activity_date > TO_DATE(?, 'DD/MM/YYYY')

TO_DATE() is an oracle function that I use to format the date from the input parameter.

Here is the error message:

Hresult of 0x80040e51 (-2147217839) returned

Unexpected error occured. An error result was returned without an error message

Henok

Sean Burke SSCommitted Points: 1504 More actions · Answer 5

Have you tried the sql statement without the TO_DATE function included? At first glance, I would bet that is at least part of your problem. I'll try and re-create the error here later today.

Sean

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 6

Sean,

I have tried it without the TO_DATE function. I still get an error. Were you able to recreate the error?

Thanks for your help!

Henok

Leon Platt SSCommitted Points: 1921 More actions · Answer 7

When you tried: select * from Oracle_table where activity_date >?

Did you get the same error message or a different one. I can not try to recreate your problem because I do not have an oracle database but I was able to do the same type of opperation between two microsoft sql server databases.

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 8

Leon,

My package works when the source and destination databases are SQL Server databases. When I change the source database to an Oracle database, then the query for the source database refuses to recognize the input parameter (which is the global variable set in a previous task).

I appreciate your help.

Henok

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 9

The error message is the same when I just type

select * from Oracle_table where activity_date > ?

Also, when I hit the preview button to see the result set I get the following error message:

HResult of 0x80040e10(-2147217904) returned

Unexpected error occured. An error result was returned without an error message.

Henok

Sean Burke SSCommitted Points: 1504 More actions · Answer 10

I'm still working on getting an oracle DB on my test server. Have you tried to run this with the date hard coded, and see what you get?

Just found this on the KB:

http://support.microsoft.com/support/kb/articles/Q240/2/05.ASP

Hmmmmmmm...

Sean Burke SSCommitted Points: 1504 More actions · Answer 11

found this for "debugging hresults for ole-db":

0x80040E10 -2147217904 No value given for one or more required parameters

http://support.microsoft.com/support/kb/articles/Q168/3/54.asp

Sean Burke SSCommitted Points: 1504 More actions · Answer 12

and this one for your previous error (same url):

0x80040E51 -2147217839 The provider cannot derive parameter info and SetParameterInfo has not been called

wondemh Mr or Mrs. 500 Points: 536 More actions · Answer 13

Thanks for you help Sean!

The first links were helpful!

I changed the source database connection to Microsoft ODBC for oracle and now I am able to refer to the global variable set in a previous task. But the query still doesn't run. I am one step closer though. I get the following error when executing the piece:

0x80040E10 -2147217904

When I hard code an actual value for the date it actually works just fine.

Thanks for your help! Kindly let me know if you find out anything.

Henok

Sean Burke SSCommitted Points: 1504 More actions · Answer 14

I'll run a few more tests. Let us know if you figure it out, or come up with any work-arounds.