Post reply

Debugging DTS Errors

  • November 9, 2003 at 2:45 pm

    #106911

    G'Day,

    A DTS job of mine failed this morning and Sunday.

    Unfortunately it failed after running for over an hour so it is impraticle to run it interactively (and it brings the server to its knees) to find out what is wrong with it. The job history is next to useless.

    Executed as user: HAHS\SQLServer. ...nStart: DTSStep_DTSActiveScriptTask_1 DTSRun OnFinish: DTSStep_DTSActiveScriptTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSDataPumpTask_22 DTSRun OnProgress: DTSStep_DTSDataPumpTask_22; 1000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 1000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_22; 2000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 2000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_22; 3000 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3000 DTSRun OnProgress: DTSStep_DTSDataPumpTask_22; 3611 Rows have been transformed or copied.; PercentComplete = 0; ProgressCount = 3611 DTSRun OnFinish: DTSStep_DTSDataPumpTask_22 DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnProgress: DTSStep_DTSDataPumpTask_1; 1000 Rows have been transfo... Process Exit Code 1. The step failed.

    Is there a method to help determine the cause?

    In this instance, it is probably due to a datapump task I added on Saturday. It is named DTSStep_DTSDataPumpTask_25 but does not appear in the list.

    Thanks,

  • November 9, 2003 at 4:06 pm

    #481367

    You can get helpful debugging info if you have package logging enabled. This can be turned on using the Logging tab in Package Properties.

    Unfortunately, if you haven't already enabled the logging there isn't all that much more you can see other than whats in the job history.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 9, 2003 at 7:00 pm

    #481371

    Hi,

    On the logging tab, the option at the bottom, "Error Handling" has a file location and name specified,

    but there is no file at all in the location when I look.

    On the top part, the "Logging" options.. what does this do exactly... ?

    It asks for a server name and logon, but doesn't really specify what is logged, and how I acces the log to see what went wrong.

    Thanks,

  • November 9, 2003 at 7:10 pm

    #481372

    In the top part put the server name and connection details and messages will be logged to that server. Useful if you have packages running on a number of servers. You can have all the logs written to one server.

    To check the log just right-click on the package name in the list and choose 'Package Logs...'. Be careful when reviewing the logs. If there aren't any package logs for the package you've selected, the Package Log dialog will show the first available log, regardless of the package you've selected.

    As for what's logged, well basically everything. Start and end times for each step in the package. Any error conditions for each step. You can also manually write entries using the DTSPackageLog.WriteStringToLog method.

    Hope this helps

    Phill Carter

    --------------------

    Colt 45 - the original point and click interface

    --------------------
    Colt 45 - the original point and click interface

  • November 10, 2003 at 2:06 pm

    #481487

    Thanks,

    That helps a lot.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply