Problem when Scheduling a DTS Packag (with Script)

  • Hi,

    I have developed a DTS Package that contains a lot of ActiveX Scripts. Most of these Scripts use a external DLL to write to a progress log,

    e.g. Some of the ActiveX Script

    '**********************************************************************

    ' Visual Basic ActiveX Script

    '************************************************************************

    Function Main()

    'msgbox IsObject(DTSGlobalVariables("objLog").Value)

    If IsObject(DTSGlobalVariables("objLog").Value) = True Then

    MsgBox "IsObject = 0"

    If DTSGlobalVariables("ExternalLog").Value = 0 Then

    MsgBox "Created Log !"

    Set DTSGlobalVariables("objLog").Value = CreateObject("MyDLL.Logging")

    DTSGlobalVariables("objLog").Value.strApplication_Name = "DTS Run"

    DTSGlobalVariables("objLog").Value.strLog_File = "C:\Temp\DTS Log.txt"

    DTSGlobalVariables("objLog").Value.Build_Log_File

    DTSGlobalVariables("objLog").Value.Log_Entry 3, 0, "Created Log" ,Now, 1

    Else

    Msgbox "Did Not Create Log"

    End If

    End If

    etc..

    This all works fine when I run the Package directly, but when I schedule the package it does not want to execute the Script correctly and does not even show the MsgBox to help me debug the process.

    What restrictions are there when Sceduling a Package compared to Executing it directly ? The DLL (MyDLL) that I use works fine when using other VB apps that calls it independantly.

    Any help are welcomed.

    Dirk

  • First msgbox is a bad way to output a message, I suggest using a file object and writting out to text file. The reason is a scheduled process runs in the Agent system account and runs on Desktop 0 while a user looks at Desktop 1 so even though the msgbox does run it happens where you cannot look at it.

    First is the server you are trying to run this on the same as the machine you are tsting from? If not then you need to make sure your dll is installed on the server and that it has been registered. If they are the same then the problem is permission related and I forget the details on that but will look for them as I have a chance.

  • Thanks for the reply. The MsgBox I only used to know where in the code I am. You can ignore them. I think you might be right with reference to the permissions, the DLL is defnitely installed and registered on the SQL Server, since other VB apps calls it successfully.

    Looking forward to your feedback,

    Thanks,

    Dirk

  • Dirk,

    try logging into the server as the account under which SQLAgent runs and see if you can execute this.

    Steve Jones

    steve@dkranch.net

  • Ok, first off this is an NT/2000 box I assume. So first try what Steve suggested and login as Agent on the box to see what happens. My other thought is from your object name I assume you are logging data somewhere. Can you tell me how the DLL works, if it does log but to a file on the hard drive the permissions problem may be with the folder you are trying to write to. Just a little detail on the DLL may help a long ways.

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

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