Setting the error file for the package

  • jasoningram

    Ten Centuries

    Points: 1215

    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.

  • jwiner

    SSCrazy

    Points: 2241

    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.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

  • jasoningram

    Ten Centuries

    Points: 1215

    No, its SQL 7.0

  • jasoningram

    Ten Centuries

    Points: 1215

    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

  • satrigney

    SSC Eights!

    Points: 896

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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720447

    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

  • Andy Warren

    SSC Guru

    Points: 119694

    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

  • jasoningram

    Ten Centuries

    Points: 1215

    Thanks everyone. I appreciate all your help.

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

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