February 24, 2004 at 3:14 am
hi everyone
i am a junior DBa who just started working on the SQL-server and need to know if anyone could help me with a programme to create a table in order to run in the DTS package of actions being done while the DTS is in progress.
I donot want to use the EM tool as it does not keep the times of when jobs started or ended once the execution is done the window disssapears.
any suggestions out there?
i understand the GUI tool allows one to schedule a DTS package and keep a record of the task(DTS PACKAGE) but as i download about 15+ tables in one dts package ,i would like to be able to go back and see how long a particular table took to transfer...
February 24, 2004 at 11:39 pm
Not totally clear what your after but EM DTS tool allows you to design a dts package which it appears you have already done. There is also an option to schedule the package you have designed so that a SQL server job is created which can be set to start when you want as for any other scheduled job in SQL Server, output is maintained and a log of when it ran ets is kept in job history. Option is under EM , DTS, local packages, then right click on the package and select schedule package. The GUI that opens is the normal job scheduler GUI.
February 25, 2004 at 4:28 am
You can log the dts package run by going to the package properties and entering a file name into the Error File box. This will show details of the whole run, not just the errors. If you want to run the package using dtsrun.exe you can specify a log with /L<logfile name>.
When the log file is written it details the step names rather than the task names. What I do is to use the disconnected edit function to rename the steps (beware - if you do this you have to rename the precedents too). The log file will then show you 'Step_LoadFileA' rather than 'DTSStep_DTSPumpTask_1'.
An example from something I'm currently working on shows:
Step 'DTSStep_AdvancePeriod' succeeded
Step Execution Started: 24/02/2004 17:24:05
Step Execution Completed: 24/02/2004 17:24:06
Total Step Execution Time: 0.04 seconds
Progress count in Step: 0
This type of log will also show steps that didn't execute.
If you're running a job that runs the package you can also use the advanced tab on the job step to define an output file. This gives a different sort of output which may be better or worse depending on what you're really trying to see.
Give them a go and see what suits your purpose.
February 25, 2004 at 9:19 am
You could create a table like this.
CREATE TABLE [dbo].[DTSPackageExecutionLog] (
[DTSPackageExecID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[DTSPackageName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Step] [int] NOT NULL ,
[Success] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[UpdatedDate] [datetime] NOT NULL ,
[UpdatedBy] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
Then create a new step after each of your steps to insert to this with either success or fail.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply