February 16, 2006 at 11:32 am
I'm attempting to execute a data pump from an Oracle DB to a SQL Server Database using a DTS Package. In the Transform Data Task under the Source tab, I've created a SQL Query in which I would like to use a global variable in part of my criteria for the transform. For example, if I have a global variable called FOO of type string, I would like to use it in the statement like so.
select * from schema.table where id_column > @Foo;
I know for a fact this does not work. I'm relatively new to DTS packages so any help would be greatly appreciated.
Thanks.
February 16, 2006 at 12:30 pm
create a stored proc on your local server
create procedure sp_GetDataFromOracleTable
AS
select *
from schema.table
where id_column > @Foo
When you call it inside the datapump...
EXEC sp_GetDataFromOracleTable ?
click on the parameters button and add the global variable (from the list) in the input variables tab.
I am assuming you set the Oracle DB up as a Linked Server.
February 16, 2006 at 1:32 pm
Change your source query to:
select * from schema.table where id_column > ?;
Now click on the Parameters button. Wait for the Parameter Mapping dialog. Make sure you are on the Input Parameters tab, click inside the Parameter Mapping section under the first column named Input Global Parameters. A dropdown list appears, select the FOO global variable. The Parameters column should show "Parameter 1".
The parameters are numbered on the order of left to right, top to bottom as read from the SQL statement. In other words, "Parameter 2" would be the second question mark (?) encountered in the SQL statement.
February 16, 2006 at 1:46 pm
I was thinking about when I have multiple statements in an SQL task. It would bomb on me. On another hand, large queries are easier to mange in a sp than inside the DTS package. So a little something for future reference.
February 16, 2006 at 2:28 pm
Something else I forgot to mention. The global variable @Foo is populated via an Execute SQL Task via a third connection to the destination database (SQL Server). Then the variable @Foo needs to be used to pull the latest records from the Oracle DB based on index values greater than @Foo to push to the SQL Server DB. I will try suggestions above however it the information I left out changes the procedure, I'll know once I try it. Thanks for help, all. I'll be back if this doesn't work.
February 16, 2006 at 2:52 pm
In a SQL Task..
Select FOO
FROM schema.table2
WHERE...
Click the parameters button...go to the output parameters tab...
pick FOO from the drop-down
Now you have the variable to procede to the datapump. From your description above, it sounds like you already have this portion, but I wanted to make sure.
February 16, 2006 at 2:58 pm
I do have the variable @Foo populated correctly. Now when click the Parameters... button I get the following error regardless as to which method I use above.
Error Source : OraOLEDB
Error Description : Provider cannot derive parameter information and SetParameterInfo has not been called
Any ideas?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply