Setting the error file for the package

  • I was wondering if there is anyway to set the error file DTS creates within an ActiveX object. My users would like the capability to set error file path from a global variable. I havn't been been able to find the object to do this. Can you help me?

    Thanks,

    Jason

    P.S. I'm using VBScript.

  • Not sure I have the answer to your question, but you can write a log file to a specified location from the job once you schedule the DTS package. Maybe take a look in sysjobs, sysjobhistory, sysjobsteps and sysjobservers tables in the msdb database. Might be able to update those tables programmatically to save files to a certain locale.

  • Is this sql 2000?

    Steve Jones

    steve@dkranch.net

  • No, its SQL 7.0

  • I don't know if this will help or not but here's something I just found out. My local machine has Service Pack 3. The server its running on is still on SP 2. The "SUCCESS" pathes run fine. It just doesn't trigger the failure workflow when a step fails. It just fails the entire package.

    I know you're all very busy and I appreciate any help you can give me.

    Thanks,

    Jason

  • The DTSPackage Object has a property called LogFileName that you can set to the path of the file you wish to use. 🙂

  • I know you can change package variables, but I'd have to dig through to figure this one out.

    I have an ActiveX script that worked in v7.0, that changed the connection string for a package. Basically, I did the following:

    Set oPKG = CreateObject("DTS.Package")

    oPKG.LoadFromSQLServer strServerName, , , 256, , , , strPackageName

    For Each oConnection in oPKG.Connections

    'debug

    objTxtFile.writeline( " Old DS:" & oConnection.DataSource )

    ' if matches conneciton I want to change

    If oConnection.Name = strPackageConn Then

    oConnection.DataSource = objFileItem

    strSourceFile = strSourcePath & "\" & objFileItem.Name

    strDestFile = strProcessedFolder & "\" & objFileItem.Name

    Next

    intError = oPKG.Execute

    I would bet there is a way to grab the error file location from the object model of the package. You'd have to dig through BOL to see. A quick glance at the OLAP unleashed shows there is a .LogFileName property you can set.

    Steve Jones

    steve@dkranch.net

  • I made a quick package and saved as VB, looks straight forward, here is a snippet:

    Public goPackageOld As New DTS.Package

    Public goPackage As DTS.Package2

    Private Sub Main()

    Set goPackage = goPackageOld

    goPackage.Name = "New Package"

    goPackage.WriteCompletionStatusToNTEventLog = False

    goPackage.LogFileName = "c:\test.txt"

    Andy

  • Thanks everyone. I appreciate all your help.

Viewing 9 posts - 1 through 8 (of 8 total)

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