Post reply

DTS Package - object contentions and dependencies

  • July 15, 2004 at 2:03 pm

    #60408

    1) If I would want 2 jobs/DTS packages (import jobs - text file to populate a db table) to run at the same time, with both jobs making use of the same text file, but populating to 2 different tables, will I be experiencing any contention problems with the text file? 

    2) If I put these two transactions/transformations in just one package, running two transformation tasks (from 1 input text file), will this be just the same as #1? 

    3) If I'd want these 2 jobs to be dependent on each other's successful run (no commit on the other if the other fails), will this be possible?

    Thank you for any input/recommendations/suggestions.

  • July 15, 2004 at 2:29 pm

    #514748

    1.  If you create a separate "Text File Source" connection for each transformation, it will work properly.

    2.  Same answer as above...keep both transformations in the same package.

    3.  You can achieve this by configuring an ActiveX Script task on the end of the 2 tranformations (have both transformations come in to the ActiveX Script Task via a "On Completion" workflow arrow).  Have the ActiveX script task check the execution status for both transformations.  If one of the steps errored out, rollback the transaction completely.  Make sure you have the package properly figured for transaction use as well.

    I can go on for pages about that last question...do you see what I'm trying to explain?  Let me know if you have any more questions or if you want me to dig in to that further.

    Thanks,

    Beau Frusetta

  • July 15, 2004 at 2:49 pm

    #514754

    Oh my, thanks for the quick response!!! I really appreciate it.

    For your #1 suggestion, what if we have space issues as far as the server/database is concerned and that it may not be able to do the 'duplicate' of the text file?  Is there any other way to go about this without consuming too much space?

    Thank YOU!

    -Chebang

  • July 15, 2004 at 3:01 pm

    #514757

    I don't understand your question...you can have 2 connections to 1 file...both connections are setup referencing the same file, but they are broken out as 2 connection objects in DTS.  I think you were thinking that I meant create 2 files... ...that's not what I meant if you read it that way.

  • July 15, 2004 at 3:51 pm

    #514765

    so I can connect to the same text file (using 2 connections) without having contentions.. thanks for clarifying that.

    as far as the rollback is concerned, how should i create the activex script?  what objects and properties should i use?  (e.g. DTSTransformation_1.Failure or Connection_1.Rollback)

    would you have any script similar to this available already or at hand that i may learn from?  where would i be able to get reference on learning how to do this?

    thank you so much!

     

  • July 16, 2004 at 8:45 am

    #514878

    The transaction is managed within the Workflow Properties/Options.  Look at the section labeled 'Transaction'

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

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