djedgar (2/4/2008)
To answer the second part of your query first, the decision to implement as a dataflow rather than use the T-SQL in a stored procedure or in a SQL task within SSIS is mainly legacy based. Although the ability to change the way the package works is far simpler in SSIS than having to trudge through the SQL. This will also make it easier for future developers to follow on and improve on existing flows within the package.
Maybe change SSIS task is simple and fast but you don’t forget about software development and ETL other tracks.
-Find impact changes
-Forward changes to test environment
-Multi user development
-Test changes (fe. blackbox test input – output)
-Debug and error handling
-Versioning
-Deployments task
-Logic for concurrent loading/ incidental loading…
… etc.
Maybe it’s only my opinion but TSQL rules !
I don't think its only your opinion 🙂
But I can't help but take issue with some of the things that you say there:
-Find impact changes SSIS has this. Limited, but it has it.
-Forward changes to test environment If this means its possible to promote code to different environments then SSIS has this too.
-Multi user development SSIS has this
-Debug and error handling SSIS has this
-Versioning SSIS has this (versioning of packages). And unless there's some sort of new feature that I don't know about there is no versioning of stored procs within the database engine
-Logic for concurrent loading/ incidental loading Not quite sure what this means but with SSIS its possible to load multiple tables concurrently.
And that's just my opinion 🙂
-Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson