DTS execution and Logging the errors on the Fly | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

DTS execution and Logging the errors on the Fly

Hello All, I’m new to DTS and was asked to work on a task. It involves copying records from tables of one server to a database on another server. This is pretty simple; however, they would want the package also to log all errors,display a summary of tables transferred along with the record count and email the log file. The Typical errors that are required to logged are
[1] SQLServer does not exist or access denied
[2] Login Failed for user "Username"
[3] Invalid Database : Could not locate entry in sysdatabases for database
‘Transact’. No entry found with that name. Make sure that the name is entered correctly.
[4] SELECT permission denied on object ‘Employees’, database ‘BEmployee’,
owner ‘dbo’ I’m not sure if DTS allows this type of logging. One way to address the
above would be to check for the above errors before the actual package runs. But if this can be handled on the fly it would be nice. Apart from logging the above errors, it is required to also have the list of
tables transferred along with successfully transferred number of records and number of errored records in the format as below. Table Transferred Errored
———- ———— ———–
Employee 23999 0
Dept 21 2
Order 874655 0
Inventory 92129778 98 Thanks in Advance, rajesh
The intrinsic logging capabilities of SQL Server 2000 DTS are limited to the Event log, text files and the msdb.sysdtssteplog table. While they provide information on the tasks, errors and number of records affected per task, they fail to offer any info on the name of the tables/views acted upon by the DTS tasks. I have a feeling there is a script out there for this kind of thing but my best bet would be to save the package as a Visual Basic module and tweak the code appropriately to provide the extra logging capabilities you seek. This is also a very long shot. Nathan H.O.
Moderator
SQL-Server-Performance.com
What method are you using for copying the rows, Data Pump, T-SQL, Copy Objects Task ? Raulie
Hewlett-Packard Company
]]>