Interview question

Question

Interview question

komal145

SSCrazy Eights

Points: 9918
More actions
April 19, 2019 at 6:57 pm

#3470223

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?

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

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

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: 9918 More actions · Answer 2

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: 442467 More actions · Answer 3

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: 1004402 More actions · Answer 4

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.

Change is inevitable... Change for the better is not.

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

Sue_H SSC Guru Points: 90860 More actions · Answer 5

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: 1004402 More actions · Answer 6

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.

Change is inevitable... Change for the better is not.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

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 SSCoach Points: 16165 More actions · Answer 8

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: 246977 More actions · Answer 9

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]);

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Jeff Moden SSC Guru Points: 1004402 More actions · Answer 10

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.

Change is inevitable... Change for the better is not.

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