Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    33

    Angry Unanswered: Help - DTS Custom Task to handle Job Failuer

    I need to find a better way to handle DTS Job Failure issue. Currently, we have about 50 jobs which executed through DTS packages. Everytime when sources were not there or came in late, the DTS sent out an email to my page which I carried every day. Some those came in during the holidays even though I know the source party won’t generate the source files at that day.

    Trying to avoid to get beep everytime when job was failed. Someone suggested that it is possible to add kind of executable file within the Custom task and let it trigger the DTS packages. If, for example, a holiday then don’t run the package so I won’t get the page.

    Any hints suggestions would be greatly appreciated

    J827

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I get unhappy when I see 0 replies, but I have not done this before but this is how I would handle it.

    I would write a dll in VB. It would check todays date against a list of holidays I might keep in a database table. If it did'nt return a result I would would use the VB syntax for firing off stored procedures (still in my dll).

    Of course before you do this you need to already have your 50 DTS packages in place.

    I would then build a new DTS package whose only step was to fire off this custom DLL. Register your cutom task and assign it icon and then build your DTS package using the DTS Designer (not the wizard).

    Good luck.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    create a table or array with the dates of all non action days (holidays weekends down days).
    create a master package that runs all of your sub packages(use the execute package task).
    create a Execute SQL (or activex)Task to evaluate the NonActiveDays table against the execution of the package if getdate() = @nonactiveday, return 0
    else 1
    then pass that value as a global variable to the task
    when the 1 hits, run the package
    this is a fairly simple solution.

    and just for giggles if your code returns a 0, you can have the task email you a reminder that the task didnt run becuase of a holiday.

    this opens you up to many possibllilties, for instance you could have this code run in a job the day before to remind you that the next day is a holiday and dts wont run....

  4. #4
    Join Date
    Mar 2004
    Posts
    33

    Thumbs up

    thanks thanks!
    Last edited by jqiu827; 02-08-05 at 16:54.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Are you executing this from the web like in ASP.Net or something?

    If you are, the problem is that the DTS package needs to run in the same security context that you created it in. The IUSR_etc... account in IIS does not have the ability to execute the package. The workarounds are sloppy and I have discussed them on this forum before. Do a search for ASP and DTS.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Mar 2004
    Posts
    33

    Angry Can't register EXE type in the Custom Task

    I created a C# ‘Console Application’ to trigger the DTS package directly and it worked (see code below). But it was an EXE (not DLL) file type.

    I would like to plug this app as part of control in an existing DTS package by adding it into a Custom Task. I just noticed the DTS Custom Task only allows adding DLL format file to be registered. Then I recreated the app with ‘Class library’ Type and tried to register / run Regsvr32 ‘appName’. I got “Could not find entry point for DLLRegisterServer in the DLL” / “appName.DLL” was load but the DLLRegisterServer entry point was not found, the file can’t be registered.

    Any helps would be highly appreciated!


    --------------------------------------------------------------------
    try
    {
    DTS.Package2Class package = new DTS.Package2Class();
    object pVarPersistStgOfHost = null;

    string ServerName = "DEV";
    string ServerUserName = null;
    string ServerPassword = null;
    string PackagePassword = null;
    string PackageGUID = null;
    string PackageVersionGUID = null;
    string PackagName = "DTSTestPackage";


    package.LoadFromSQLServer(
    ServerName,
    ServerUserName,
    ServerPassword,
    DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrus tedConnection,
    PackagePassword,
    PackageGUID,
    PackageVersionGUID,
    PackagName,
    ref pVarPersistStgOfHost);

    package.Execute();
    package.UnInitialize();

    // force Release() on COM object
    System.Runtime.InteropServices.Marshal.ReleaseComO bject(package);

    package = null;
    }
    catch(System.Runtime.InteropServices.COMException e)
    {
    Console.WriteLine("COMException {0}", e.ErrorCode.ToString() );
    Console.WriteLine("{0}", e.Message);
    Console.WriteLine("{0}", e.Source);
    Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
    }
    catch(System.Exception e)
    {
    Console.WriteLine("Exception");
    Console.WriteLine("{0}", e.Message);
    Console.WriteLine("{0}", e.Source);
    Console.WriteLine("Stack dump\n{0}\n", e.StackTrace);
    }

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •