Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Almir_7
Starting Member
14 Posts |
 Posted - 2008-11-17 : 12:08:31
|
| I'm trying to find a way to have my scheduled DTS packages run and bring back data from last successful run date/time. Where does SQL 2000 store the last successful run date/time and how could i retrieve it and use it in my DTS?Right now i pull back data based on the date/time range. For example i pull back the last day's worth of data or last 2 hours. As in my example bellow:declare @StartDate datetimeset @StartDate = dateadd(hh, -2, getdate())declare @EndDate datetimeset @EndDate = dateadd(hh, 0, getdate())Select *from db_data_tablewhere closed_date between StartDate and EndDateThis query transfers the data gathered for last 2 hours. The query is located inside of a scheduled DTS package.I'm looking to find either the last successful date/time stamp in one of the system tables or if i can write some code to write that last successful date/time into a table that i can query next time around.All ideas welcome. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 12:18:55
|
| its better to use a seperate log table for capturing run date time each time and then use it for the subsequent running on dts. Inside dts include steps to read max date from table and extract data based on it and at end populate the table with current rundate for next runs. |
 |
|
|
Almir_7
Starting Member
14 Posts |
Posted - 2008-11-17 : 12:33:34
|
| I agree. I'll probably want to write the date from when the scheduled job started rather then when it finished so that any data that might be returned during the time the job runs would not be lost.How would i use this date/time stamp from the table in my query? How can i call that date/time in the DTS query? So i write at the end of my DTS a statement to write the date/time stamp to a new table to keep track. How can i re-use this date/time now on the next run?I use the transfer data task in the DTS and the query pulls data from another server. The source is set to SQL query and the date range is used as in my example above. Can i use the new table and the new date/time column as a parmater? How? Any examples? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 12:36:48
|
quote:
Originally posted by Almir_7
I agree. I'll probably want to write the date from when the scheduled job started rather then when it finished so that any data that might be returned during the time the job runs would not be lost.How would i use this date/time stamp from the table in my query? How can i call that date/time in the DTS query? So i write at the end of my DTS a statement to write the date/time stamp to a new table to keep track. How can i re-use this date/time now on the next run?I use the transfer data task in the DTS and the query pulls data from another server. The source is set to SQL query and the date range is used as in my example above. Can i use the new table and the new date/time column as a parmater? How? Any examples?
You basically need two execute sql tasks. one which reads max date from table and other which inserts current date to table.You also need a parameter which you would decalre in dts to store date value returned from table and use it for other queries to get changed data. |
|
|
|
Almir_7
Starting Member
14 Posts |
Posted - 2008-11-17 : 15:35:29
|
| Can you point me to any examples or articles where something like this is done? |
|
|
|
Almir_7
Starting Member
14 Posts |
Posted - 2008-11-17 : 17:32:06
|
| Can the parameters or global variables be shared between different connections? I have the transformation task between two servers to download the data? I created a SQL task and i was able to create a global variable but then i can only see it in the tasks that use the same connection (ie. on the local server). The point of getting the global variable or parameter is to be able to filter the source query. I wasn't able to find anything on how to retrieve the global variable. I tried using the lookup query but that didn't lead anywhere as there is no examples or ways to call the variable created in the lookup query. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-17 : 23:12:51
|
| you can share variables. use dynamic properties task for setting value of connection to a variable. |
|
|
|
|
|
|
Return data since last successful run with DTS