March 17, 2011 at 3:29 pm
Fundamental point of this post : Shold I construct my DW with SQL and SQL Agent, or with Integration Services?
detail : i've built a comprehensive 30 ish table structure that I use to feed my OLAP cube. I've done this using SQL, which in itself has been a learning curve and some of the SQL constructs i've used should really be replaced with more efficient code. But im wondering if I should replace these code(s) with SSIS Packages and move in that direction. I have used integration services for some parts of my DW where loading data from an external source via FTP - so I know I could use that.... but interested to know what your collective thoughts are.
Thanks
March 17, 2011 at 3:45 pm
ProKelly (3/17/2011)
Fundamental point of this post : Shold I construct my DW with SQL and SQL Agent, or with Integration Services?detail : i've built a comprehensive 30 ish table structure that I use to feed my OLAP cube. I've done this using SQL, which in itself has been a learning curve and some of the SQL constructs i've used should really be replaced with more efficient code. But im wondering if I should replace these code(s) with SSIS Packages and move in that direction. I have used integration services for some parts of my DW where loading data from an external source via FTP - so I know I could use that.... but interested to know what your collective thoughts are.
Thanks
It depends, as usual.
I typically use SSIS when doing cross server data migration. Any significantly complex data manipulation you'll probably want to do from a staging table. Any simple lookups and the like you'll probably want to do mid-stream in SSIS. A combination of the two will usually yield the most efficient result.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 1:13 am
ProKelly (3/17/2011)
Fundamental point of this post : Shold I construct my DW with SQL and SQL Agent, or with Integration Services?detail : i've built a comprehensive 30 ish table structure that I use to feed my OLAP cube. I've done this using SQL, which in itself has been a learning curve and some of the SQL constructs i've used should really be replaced with more efficient code. But im wondering if I should replace these code(s) with SSIS Packages and move in that direction. I have used integration services for some parts of my DW where loading data from an external source via FTP - so I know I could use that.... but interested to know what your collective thoughts are.
Thanks
Well, SSIS does have integrated logging and event handlers, so that will scale better to bigger solutions than just plain SQL. It has also a good integration with .NET (script task/component). OK, SQL Server has CLR, but I think the learning curve is smaller in SSIS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2011 at 4:25 am
My only concern with moving to SSIS is my experience performing tasks such as lookups from OLE DB sources operations are SLOW, really slow compared to SQL code.
I appreciate just saying things are slow isn't helpful and from reading these forums there are many reasons things could be slow, i'll have to look into them. Also, i've been working on SSIS Standard Edition - i'll be moving to Enterprise Edition shortly.
Appreciate your comments. I'll have a work through moving some components from SQL to SSIS packages in my spare time and see what performance impacts these may have
March 18, 2011 at 4:28 am
ProKelly (3/18/2011)
My only concern with moving to SSIS is my experience performing tasks such as lookups from OLE DB sources operations are SLOW, really slow compared to SQL code.I appreciate just saying things are slow isn't helpful and from reading these forums there are many reasons things could be slow, i'll have to look into them. Also, i've been working on SSIS Standard Edition - i'll be moving to Enterprise Edition shortly.
Appreciate your comments. I'll have a work through moving some components from SQL to SSIS packages in my spare time and see what performance impacts these may have
If you configure the lookup component correctly with the caching options, I wouldn't say it is slow. It will certainly perform well with the Full Caching option if the dataset isn't too big. (but you must be using 2008).
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 18, 2011 at 4:31 am
ProKelly (3/18/2011)
My only concern with moving to SSIS is my experience performing tasks such as lookups from OLE DB sources operations are SLOW, really slow compared to SQL code.
I've found the exact opposite unless you're looking up 5 rows from your original datasource against a million row cache (to be extreme in the example).
I appreciate just saying things are slow isn't helpful and from reading these forums there are many reasons things could be slow, i'll have to look into them. Also, i've been working on SSIS Standard Edition - i'll be moving to Enterprise Edition shortly.
Appreciate your comments. I'll have a work through moving some components from SQL to SSIS packages in my spare time and see what performance impacts these may have
Anything that you do on a row level, generally leave in SSIS. If it affects multiple rows simultaneously, you might look between T-SQL and SSIS to see who's faster.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:33 am
Koen Verbeeck (3/18/2011)
ProKelly (3/18/2011)
My only concern with moving to SSIS is my experience performing tasks such as lookups from OLE DB sources operations are SLOW, really slow compared to SQL code.I appreciate just saying things are slow isn't helpful and from reading these forums there are many reasons things could be slow, i'll have to look into them. Also, i've been working on SSIS Standard Edition - i'll be moving to Enterprise Edition shortly.
Appreciate your comments. I'll have a work through moving some components from SQL to SSIS packages in my spare time and see what performance impacts these may have
If you configure the lookup component correctly with the caching options, I wouldn't say it is slow. It will certainly perform well with the Full Caching option if the dataset isn't too big. (but you must be using 2008).
I only work in 2k5 and I agree with you (or is that disagree about the 2k8 bit...) hmmmm... confusion and disagreement. The world falls asunder at the logical failure of simultaneous agreement and disagreement! 😉
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
March 18, 2011 at 4:39 am
Craig Farrell (3/18/2011)
Koen Verbeeck (3/18/2011)
If you configure the lookup component correctly with the caching options, I wouldn't say it is slow. It will certainly perform well with the Full Caching option if the dataset isn't too big. (but you must be using 2008).I only work in 2k5 and I agree with you (or is that disagree about the 2k8 bit...) hmmmm... confusion and disagreement. The world falls asunder at the logical failure of simultaneous agreement and disagreement! 😉
OK, I did some research and apparently you can use the different caching options in 2005, but you'll have to set some hidden properties in the advanced editor, while in 2008 you can just select the different options in the General tab of the normal editor.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply