March 3, 2004 at 10:00 pm
I have written a DTS package that copies data from SQL Server to ORACLE. Basically I have a set of staging tables that get populated. Then everything that's in the staging tables gets inserted into Oracle tables. Any data greater than a specified date that exists in the Oracle tables is deleted, and the staging tables contain only data greater than the specified date.
Here's the catch:
The specified date is stored in a global variable. I have an Execute SQL task that deletes the rows in Oracle. Becuase the connection for the Execute SQL task is an Oracle OLE DB connection, the task won't let me use the global variable in the sql statement.
So -
I created an ActiveX Script Task using VBScript. I construct the delete sql statement within the script task and then get a reference to the Execute SQL task and finally assign the newly constructed sql statement to the execute sql task. Using the script task allows me to assign the date value from the global variable.
Works like a champ! - until the task runs unattended (scheduled job on sql server). When the job runs scheduled, rather than deleting only a portion of the records in the Oracle tables, all the records are deleted. The job runs fine when I run it by clicking on the start button in Enterprise Manager. I will point out that the execute sql task sql statement gets updated by the activex script task, but the sql statement returns to what was there previously. I imagine this is because the package doesn't get saved after the execute sql task gets updated. I haven't been able to figure out how to do this within a DTS package programmatically, nor am I convinced that it would solve the problem.
Any suggestions?
Thanks,
DGC
March 4, 2004 at 2:43 am
How about:
Write the value in the global variable to a table in SQL Server (one table, one row, one column)
Transfer this into a matching table in Oracle (delete anything in the Oracle table first so that its also one table, one row.
Then use the Oracle table with the date in the deletion statement.
A bit naffish but it could work.
JK
March 5, 2004 at 6:38 am
A technique we've been using instead of a global variables is to store the date in another table and then change the date in that table when we need to.
example:
DELETE FROM Data_Table
where cr_date < (select cleanUp_date from Date_Table)
David Bird
March 5, 2004 at 8:04 am
How about Dynamic Properties Task? I can show SQLSERVER example you can fit to call PL/SQL proc or something?
Add a "Dynamic Properties Task" to your DTS layout and put it in between an "ActiveX Scripting Task" and an "Execute SQL" task.
Set the Execute SQL task to use SELECT NULL for its design time SQL statement. You will change this programatically in just a minute.
Create global variables called GV_PARAM and GV_NEWSQL
Then code an ActiveX Scripting Task to include this...
Dim sSQL: sSQL = ""
sSQL = sSQL & "DECLARE @OUT_VAR VARCHAR(200) " & vbcrlf
sSQL = sSQL & "DECLARE @PRM_VAR VARCHAR(1000) SELECT @PRM_TABLE ='" & DTSGlobalVariables("GV_PARAM") & "' " & vbcrlf
sSQL = sSQL & "DECLARE @RETVAL INT " & vbcrlf
sSQL = sSQL & "EXEC @RETVAL = [dbo].[P_YOURPROCNAME] @OUT_VAR OUTPUT, @PRM_VAR " & vbcrlf
DTSGlobalVariables("GV_NEWSQL") = sSQL
Then add precedent constraint OnSuccess to link ActiveX task to DynamicProperties Task
Then design time hook "DynamicProperties Task" DestinationProperty for SQLStatement of ExecuteSQLTask to be the value of the
DTSGlobalVariables("GV_NEWSQL")
Then add precedent constraint OnSuccess to link the DynamicProperties Task to the ExecuteSQL task.
This way you can test in design time by simple executing the ActiveXScripting Task manually(right click, execute this step) and then executing the DynamicProperties Task manually and then check the results in the ExecuteSQL task.
See that SELECT NULL was replaced with what you want.
This gives you control of parameters you can set in TSQL to kickoff the DTS Package. Then have your job call the TSQL to set Global Variables and execute DTS Package.
Good Luck
March 5, 2004 at 8:13 am
Thanks everyone for the suggestions. Julie - I think the solution you provided may be the best interim.
JHouston - the package I have now already does this dynamic assignment using a slightly different method and the Execute SQL displays exactly what I want after my ActiveX Script Task executes. Like I said the package runs exactly as expected when I execute it via DTS designer. But when it runs as a scheduled job it does not execute properly.
I will get a chance to work on the package some more today. I'll post my findings here. Thanks again everyone for your input!
March 5, 2004 at 12:35 pm
We have had some issue when passing dates to Oracle. We now include something like the following which helps aliviate any issue in date conversion on the Oracle side.
=====
WHERE OracleDateColumn = TO_DATE('" & DTSGlobalVariables("gvMySQLDTSDate").Value & "', 'MM/DD/YYYY')"
{modify the conversion for the date format you are passing}
=====
Its possible that when you schedule on the server, the server's regional settings for the date formats are different than on your local machine that you used to test.
March 8, 2004 at 9:57 pm
Thanks for all the suggestions. It turns out that the SQL Server box had the regional setting for short date set as "m/d/yy". It's not a sql specific setting, but a global setting for the server. I was already using the TO_DATE function (learned pretty quickly to do this anytime passing a date to Oracle), so the following was being passed:
delete from MyTable where Date >= TO_DATE('2/1/04','mm/dd/yyyy').
I guess Oracle translated the date to '2/1/1904' or '2/1/0400' or who knows!?!? Anyway, thanks again everyone for jumping at the opportunity to assist a fellow SQL developer!
Donny
March 22, 2004 at 4:51 pm
I just tried out Grasshopper's Dynamic Properties Task Properties idea and it worked great. Not intuitive at first but good potential.
Always something new to learn about DTS. I put in boatloads of text comments all over the place because its one of those things you can easily forget.
I appreciate it
Andre
June 2, 2004 at 2:33 pm
I found this thread because I was searching for something that would help me with the use of date parameters in the SQL Query section of the source tab of a Data Pump task where the source was an ODBC connection to Oracle. The above thread validated my problem (an Oracle ODBC connection does not allow the use of the "?" parameter the way the native SQL connection does) and showed me the way around it:
(1) Build the SQL statement dynamically in an ActiveX Script task, and write it to a global variable
(2) Use a Dynamic Properties task to modify the Data Driven Query task to use the SQL statement in that global variable
I was already planning on using the Dynamic Properties task to change the output file name (destination is tab-delimited text file) so I could automate this package for a daily data extract. The task/connection/workflow for my package is:
ActiveX Script Task - on success - Dynamic Properties task - on success - ODBC Oracle connection - Data Pump task - Text File connection
Details available upon request. Hope this helps someone the way the rest of this thread helped me.
Dan
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply