Parameters in DTS - Urgent
-
I have a package, in which I have a SQL task that queries an Oracle dB for two values it needs and sets as parameters. There are set up as output params. Then I run a transfer data task (or should I use a data driven query) to move (import) records from Oracle dB:
where parameter 1 > docid
and parameter 2 >= docdate.
Currently the pkg, is replacing the docid and docdate with the values of the output param. This is totally not supposed to happen. I just want to bring over the records based of of the following two queries:
1. Get the max docid and docdate from SQL dB.
2. Select * from Oracle dB where docid > param 1 and >= param 2.
Sounds simple enough but the value from the first query is set and inserts into all of my records. If I put a constraint on the column it errors out talking about dupes. On the Oracle side this docid is unique, not one dupe. What is causing this?
Aurora
-
quote:
I have a package, in which I have a SQL task that queries an Oracle dB for two values it needs and sets as parameters....
1. Get the max docid and docdate from SQL dB.
So do you get the parameters from SQL Server or Oracle??
It sounds an easy enough task. Use an ExecuteSQL task to get the two values and assign them to Global Variables. Use the global variables as parameters in the Source SQL statement for the datapump task.
Hope this helps
Phill Carter
--------------------
Colt 45 - the original point and click interface
--------------------
Colt 45 - the original point and click interface
-
Oops, sorry about the cofusion. I get the values from the SQL dB, and based off of those 'output params', I run a select (data pump task) to get the data from the Oracle dB. However, for some reason it continues to place that value (output param) in the column, over and over again. If I put a constraint on the column it errors out due to duplicate record. Any idea why that might be happening?
Aurora
-
I found out the problem I will share for anyone else who may come across this.
In the data pump task, on the transformations tab there is an option to edit the properties. When you select edit, it brings you to the column source, destination and general tabs. On the general tab, there is a properties option. This option brings you into the Active X scripting options. In this example, we are using VB script. I had to modify this script to properly bring over the records, by properly ‘reading’ the parameters. Sounds easy huh? Now why couldn’t I have found this last week? 🙂
Aurora
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply