Use Execute SQL Task Or Use a available SSIS task to build package.

Question

Post reply

Use Execute SQL Task Or Use a available SSIS task to build package.

saurabh.deshpande

SSCommitted

Points: 1738
More actions
October 6, 2010 at 9:02 am

#140989

Hi all
I came across a strange situation.
1. I have to load my FACT table. So, I written a long SQL query
and put in the EXECUTE SQL TASK. And executing from there.
2. But, another approach is through DATA FLOW Task. By using all
available task ex, Lookups, Merge Join, Conditional Split, etc and
load the FACT table.
Like general concept of ETL is DATA FLOW consist of no's of
Tasks/Transformations.
So, out 2 approaches which is acceptable.
1. Call long SQL Scripts from EXECUTE SQL TASK. Or
2. Building DATA Flow’s.
Thanks

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

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

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

You can do it either way. I'd encourage you to try it both way just to see how well each one performs.

Also, ask yourself this question: "Why one is easier to maintain?"

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

saurabh.deshpande SSCommitted Points: 1738 More actions · Answer 2

1. I tried both the way, on performance front EXECUTE SQL Task is

good as compared to DATA Flow’s.

DATA Flow's takes long time to execute.

2. I observed development through SQL task takes less time, as

compared to building DATA Flow’s.

maintance point of view, I think both are equal.

Do you agree with me?

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 3

saurabh.deshpande (10/6/2010)
1. I tried both the way, on performance front EXECUTE SQL Task is
good as compared to DATA Flow’s.
DATA Flow's takes long time to execute.
2. I observed development through SQL task takes less time, as
compared to building DATA Flow’s.
maintance point of view, I think both are equal.
Do you agree with me?

I can't agree or disagree on #1 since I'm not running it. I can say that I'm not surprised. There are cases where SSIS will be faster. It depends on the circumstances.

On #2? That depends. My SSIS projects usually take longer than pure T-SQL but they're also more complex.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]