Interview question

  • komal145

    SSCrazy Eights

    Points: 9866

    hi ,

    This was the interview question.

    I have three different Dataflow tasks DFT1,DFT2,DFT3.

    DFT1 has to run at 10am

    DFT2 has to run at 11am

    DFT3 has to run at 12pm

     

    All the three data-flow tasks has to be in one package. How can you design package to RUN data-flow tasks at different timings?

    Can you guys please suggest how to design such package?

     

  • Lynn Pettis

    SSC Guru

    Points: 442118

    To be honest, I wouldn't design it that way.  Each Dataflow Task would be in its own package and scheduled to run as SQL Agent job at the appropriate times.

     

  • komal145

    SSCrazy Eights

    Points: 9866

    Lynn- My view is the same as yours. But question was if i have the package with such three different dataflow tasks , how you design it to schedule. I do not know why people ask such questions in interview ( poor designing 🙂 ).

  • Lynn Pettis

    SSC Guru

    Points: 442118

    If asked that in an interview I would ask why it was being designed that way.  It is inefficient and a waste of resources.  Breaking it up in to three separate packages and scheduling independently makes so much more sense than the complexity of trying to handling the scheduling inside of a single package.

     

  • Jeff Moden

    SSC Guru

    Points: 994284

    Lynn Pettis wrote:

    If asked that in an interview I would ask why it was being designed that way.  It is inefficient and a waste of resources.  Breaking it up in to three separate packages and scheduling independently makes so much more sense than the complexity of trying to handling the scheduling inside of a single package.  

    It's ok to tell them what you'd like to do instead but, druing an interview, you still have to answer the question to their satisfaction.  😉  I'd try to help but I know little of SSIS.  To wit, I'd also love to hear the answer to the question even though I agree that it's probably not the best way.

     

    I'd also love to ask what they want to happen if the previous data flow task has not yet competed. 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Sue_H

    SSC Guru

    Points: 90011

    Ugly but I think you can put an Execute SQL task between the data flows and execute WAITFOR TIME.

    Sue

  • Jeff Moden

    SSC Guru

    Points: 994284

    Couldn't you also put a path "director" in the overall flow to select a path based on the time of day and execute the package 3 times?  Again, forgive me if I don't know what I'm talking about because I don't actually use SSIS but it seems like it should be possible if it's worth it's salt.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Lynn Pettis wrote:

    If asked that in an interview I would ask why it was being designed that way.  It is inefficient and a waste of resources.  Breaking it up in to three separate packages and scheduling independently makes so much more sense than the complexity of trying to handling the scheduling inside of a single package.  

     

    I guess if I was asked this in an interview it one question that I would get wrong as I would not be able in good conscious be able to design and write a SSIS package in that manner.  To put it simply, it is wrong.

     

  • frederico_fonseca

    SSChampion

    Points: 14060

    Although it is one of those things that experienced people would frown upon, its purpose is to ensure that the one being interviewed knows how to deal with conditional flows within SSIS and also to give some options of how it could be implemented.

    on this case I would expect a candidate to be able to state "conditional expression with 3 possible branches, 1 for each data flow"

    possibly a SQL statement task to determine the value for the conditional expression

     

    in very complex SSIS tasks such a situation would be common enough where blocks of the package would be executed depending on time or day or even environment where it is executed.

  • Phil Parkin

    SSC Guru

    Points: 243549

    I agree that this is a horrid idea.

    It is, however, pretty easy to solve. Put a Script task before each dataflow and add a wait there, possibly something like this (would need to be tested):

    System.Threading.Thread.Sleep([calculated number of seconds before desired execution time]);

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Jeff Moden

    SSC Guru

    Points: 994284

    Phil Parkin wrote:

    I agree that this is a horrid idea.

    Heh... yeah.. me too.  I hate SSIS. 😀 😀 😀

     

    Phil Parkin wrote:

    It is, however, pretty easy to solve.

    Totally agreed... write a good stored procedure or two and then turn SSIS off. 😀 😀 😀

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

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

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