Whats quicker; SQL or SSIS?

Hi all,

I'm a bit short of time for a project so can't really do both and compare (but will at some point), so therefore;

What is quicker; an SQL statement or using the objects in SSIS?
i.e. If I can do a lookup by joined table or sub-query, is that quicker than the lookup object in SSIS?
Same goes for data conversions/cast statement, derived columns, the union object etc.  

I'd assume they're slower which, if they are slower, then leads me to ask, are they only there for people that don't know SQL?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I dont quite understand you thinking
ssis is a replacement for DTS
I think this is a false dilemma. T-SQL and SSIS achieve very different things and there isn't really much overlap.

If you want to integrate and transform data from heterogeneous data sources then SSIS offers vastly more than T-SQL does. I know you can achieve some things using linked servers but for most purposes you are limited to SQL-DBMS and a few other providers. Performing lookups and joins across different systems using linked servers isn't remotely feasible in many cases. Querying file data sources using T-SQL is only possible using a few very simple file formats.

On the other hand if your data is already on a single SQL Server and you want it to stay there then T-SQL should give you everything you need. In the majority of cases the SQL-DBMS (T-SQL or SQL-CLR) will be the most efficient engine for processing the data because other methods require the data to be fetched and then resent over some external protocol (OLEDB or ODBC).

There are potential exceptions around very large aggregations and lookups that SSIS can achieve in memory but might otherwise hit TempDB using purely SQL. These are exceptions though. In most cases I would assume T-SQL to be most efficient unless you identify a specific performance bottleneck.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kintonAuthor Commented:
All of the data is on the one box so that answers my question perfectly thanks dportas.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.