Post reply

Trying to create a DTS package to handle recurring payments. Has anyone done this before?

  • June 1, 2004 at 12:05 pm

    #59208

    I'm trying to create a DTS package to handle recurring payments.  It needs to do the following:   

    • Look for current date in a monthly process date field in a table
    • If current date, Look for bit field to determine whether the account has a current balance
    • Create an output of records that will have payments processed for recurring payment processing
    • Pass it thru an command shell (i.e. .exe program that will process the payments against a payment server).

    Any input on how this could be done, would be very helpful my fellow DBA's.

    Thanks!

  • June 2, 2004 at 7:07 am

    #508643

    I am curious as to why you want to use DTS.  This sounds to me like a systematic series of steps involving interaction with a host and client server for which a compiled or scripted language is much better suited. (ie c# or vbscript)

    Steve

  • June 2, 2004 at 7:58 am

    #508660

    Kepr00 is right...but you can use DTS too. Use the ActiveX Task in DTS, this way you will have control over both the logic and the steps involve. Should define three steps, one to generate the source data, two: task to pass the generated data to the payment server (ActiveX Script Task), three: Verify that all your data was processed by the payment server.

  • June 2, 2004 at 11:52 am

    #508698

    Sounds like a good idea, but I'm not very familiar w/ c# or vbscript.  Is it worth my while to become familiar w/ these languages to accomplish these tasks.  I'm not building the processing functions for the payment server.  I'm just passing the billable ID's to a command shell program that another developer is building.

  • June 2, 2004 at 2:03 pm

    #508721

    How are you going to pass the ids?

    Sounds like the "other developer" could include your piece into his app with realitevly little effort.  You could just supply him with the correct stored proc or sql statement that would give him the information that he needs.

    If you still would rather use DTS for the ID accumulation, you could do a couple of things.

    1. You could eithe create a cursor that would loop through your recordset of ids and call xp_cmdshell 'otherapplication.exe commandLineParms' through each iteration
    2. Better still you could get the list of ids and create a file that holds all the information.  then you could just execute the other app one time passing it the path to the id file.

    Using DTS in this manner it will be difficult to determine the success/fail status of other app if you need to update your database with this information.  If you pushed this responsiblity on to the other app then you would have a better information on what occurred during the transaction.  I think that you ought to use either the ActiveX Task and develop a program to do this or have the other application do all the work.

    Steve

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

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