DTS- Data Driven Query Task

I use a data driven query task to perform row updates along with appending row data to a text file. In the source I use 'select top 1000 field1, ..., fieldn from table'. The package hangs when executing the data driven task. If I reduce the return count to 'select top 100...' the package execute with no problems. I can do that 10 times, with no problems. But anything more then 100 will hang the package at that task. Please help!

Thanks,
MoniqueTake help from PROFILER and see where it hangs.

The other method of limiting the size of a result set is to execute a SET ROWCOUNT n statement before executing a statement. SET ROWCOUNT differs from TOP.

The TOP clause applies to the single SELECT statement in which it is specified. SET ROWCOUNT remains in effect until another SET ROWCOUNT statement is executed, such as SET ROWCOUNT 0 to turn the option off.

DTS - How to...

I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?One approach:

Write an ActiveX Script to take the place of the Connection to Oracle. In your ActiveX Script, establish the connection to Oracle, pull the data into SQL in a temp table and then use the Data Driven Query Task to process the data from the temp table. The ActiveX script can contain either a lookup to a date/time field which us updated each time the script processes, or you can take the max date/time field from the last job processed as your new starting point.

There are lots of things wrong with this approach, but it is doable.

Another Approach:
In the ActiveX script for the Data Driven Query task, you can do a similar thing with pulling in the last date/time stamp and then use the DTSTransformStat_SkipRow to skip over a record that you don't want to process. This still involves pulling the data across the nework from Oracle to SQL, but you will save some processing time.

Again, there are porbably lots of things wrong with this approach, but it is doable.

Finally, I have not yet discovered a way to manipulate the SQL text in a Data Pump or Data Driven Query object. I believe that it is theoretically possible to do it (by manipulating the DTS package in SQL DMO), but I have never really had the time to fully explore this.

Best of luck to you...

Hugh Scott

Originally posted by brucevde
I am new to DTS and I need some help. I have created a package which connects to an Oracle db, runs a query and then either inserts or updates a sql server table based on a DTSLookup. The package contains 3 connections and one DataDrivenQueryTask. It eventually will be scheduled to execute every 10 minutes.

Its working at the moment but there is one part I am missing. The table in Oracle has a field which contains the date the record was last modified. I have currently hardcoded into the where clause this statement LastModDate > trunc(sysdate).

This returns all records modified for the current day but of course, everytime it executes it will return records I have already processed.

Sorry, for babbling on but finally here's the question

How can I use a variable in my Source query? I was hoping I could store the last execution date/time in a sql table, retrieve it and use it in in the Where clause. Something like LastModDate > @.LastExecuteDate. I am not sure how to set this up. Any ideas or pointers you have would be great?

dtexec Return Success BUT Not Run!

I've created a Maintenance Plan in Microsoft SQL Server Management Studio (Sql Server 2005 SP2 + Windows Updates) and currently trying to execute it via the dtexec command line program.

The problem is that it reports that it executed the Maintenance Plan but it never actually executes the Plan. The data isn't updated and the CPU and hard drive I/O reports nothing happening.

I've checked the argument lists but I don't think I've missed anything...?

On the Sql Machine itself, I run the command line as follows:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Microsoft Windows [Version 5.2.3790]
(C) Copyright 1985-2003 Microsoft Corp.

C:\Documents and Settings\V2Admin>cd \
C:\>dtexec /SQL "\Maintenance Plans\GPI Update" /Server V2SQL\VC2 /User sa /Pass
word XxXxXxX
Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.

Started: 11:29:43 AM
DTExec: The package execution returned DTSER_SUCCESS (0).
Started: 11:29:43 AM
Finished: 11:29:44 AM
Elapsed: 0.375 seconds

C:\>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>I don't understand why you are running using DTEXEC, have you tried to schedule the package that is created using SSIS?|||We are running DTExec as we want chain together and run other processes outside as Sql Server as well, but want to use the History tracker of Sql Server's Maintenance Plans.

The Sql Server Maintenance Plan tasks are kept with Sql Server and the external processes are kept with their relevant tools.

We need to run both together which is why we need to call DtExec.

At the worst case scenario, we can simply run SqlCmd and execute the desired functionality, but that is asking for problems as there would then be two locations to maintain the Maintenance Tasks Sub-Plans.

Not a smart move in any operations manual!
|||

Schedule a maintenance plan and look at the arguments to dtexec in the agent job step. You will find that the subplan that contains your job steps is enabled from that command line. If you just run a maintenance plan without so enabling the subplans, all are disabled, and nothing runs, as you have confirmed in your scenario.

jkh

DTC

Does anyone have any instructions on how to setup the MSDTC properly and
make it work with all the security updates. I am trying to communicate
between to windows 2000 machines and I am getting:

Error 8524
The current Transaction could not be Exported to the Remote Provider.

The distributed transaction works fine on to instances of SQL Server 2000 on
the same machine.

RandySorry Correction

I am running on XP SP2 and trying to establish a transaction with Windows
2000 Server SP4.

"Randal" <rfraser@.novaflow.com> wrote in message
news:X8q5g.497$VV2.39662@.news20.bellglobal.com...
> Does anyone have any instructions on how to setup the MSDTC properly and
> make it work with all the security updates. I am trying to communicate
> between to windows 2000 machines and I am getting:
> Error 8524
> The current Transaction could not be Exported to the Remote Provider.
> The distributed transaction works fine on to instances of SQL Server 2000
> on the same machine.
> Randy