February 3, 2004 at 5:19 pm
We have several DTS packages that do a simple copy_column transformation between 2 SQL 2000 servers. There is data in the source table, the DTS package runs in a SQL agent job and completes successfully with no errors. However the destination table has no data in it. We run the package manually and it always runs but sometimes the data gets to destination and sometimes it doesn't.
Any suggestions?
February 3, 2004 at 9:38 pm
You can log the package application. I believe that it will record the number of records affected.
The Log the package option is set by FIRST DON"T HAVE ANY OBJECT SELECTED (an annoying property of the DTS designer), then hit the Package menu, then the Properties sub-menu. Goto logging tab. Check the "Log package to SQL Server" check box, set the server and user info. It will write package and step information to sysdtspackagelog and sysdtssteplog.
You can log the transaction as well. That is on the last tab of the transformation property box.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 4, 2004 at 10:02 am
Does this generate the same information as the output text file from the job step that is running the package?
February 4, 2004 at 10:14 am
There is one piece that is not in the exception log file (I think that is what you are talking about):
The column sysdtssteplog.progresscount contains the number of records that were moved by the step.
Another thing that you might do to get details is to run dtrun (with appropriate parameters) and redirect the standard output to a file:
dtsrun /SMyServer /NMyPackage /E>MyPackageResults.txt
This will give a progress report for the run, with every thousand records progress.
Russel Loski, MCSD
Russel Loski, MCSE Business Intelligence, Data Platform
February 6, 2004 at 9:41 am
Could be a security problem. When DTS runs as a job, it runs on the server. You might open the package on the server and attempt to run it manually there. Also check that the owner of the job has access to all of the objects in the package.
Good Luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply