HomeETLWhen to use T-SQL or SSIS for ETL

Comments

When to use T-SQL or SSIS for ETL — 17 Comments

  1. Hi James,
    Good article. I wrote a lengthy reply but accidentally hit CTRL-W and lost the whole damn lot 🙂
    I can’t be bothered writing it all out again but I will bring up what I think is one vital point; that is, you haven’t explicitly mentioned the one true differentiator of SSIS – its ability to combine data from different data sources and operate upon it *in a single operation*.

    Everything else is by-and-large subjective.

    Extensibility? T-SQL has got SQLCLR.
    Complexity? That simply depends on one’s opinion.
    Ease of dev – Again, a matter of opinion.

    The one absolute differentiator of SSIS dataflows is their heterogeneity.

    JT

    P.S. Can you give an example of the following:
    “text files with an inconsistent number of fields per row can only be done in SSIS”
    I don’t see what about this scenario makes it easier to deal with this in SSIS (except for the fact that it has the Flat File source – but I don’t think that’s what you’re getting at)

  2. Thank you for writing this article down. Out of frustration to prove to the hiring manger that I am right on T-SQL is faster and outperformance SSIS in many different ways although SSIS has man advantages over T-SQL and your article covers that.
    (but do I need to prove to stupid people that they don’t know the stuff or should I move on)
    Two points I want to add to your article are these.
    1. SSIS can only handle row by row process (procedural) where as T-SQL can handle both procedural and set-based.
    2. SSIS can’t handle recursion such parent/child relationship.

    • I know your post is an year ago but thought of replying,the right way to do it is using tsql inside a storeprocedure and place it in SSIS package,1)the ssis package can handle setbased all you need to do is first load into a temporary table and bulk update to actual table which is same when it comes to tsql 2)handling recursion such parent/child relationship need to be done with tsql in the source task or lookup task of ssis depending on what you need.

  3. Pingback:SSIS Lookup or T-SQL Join « Derek Tech Cornor

  4. Pingback:Presentation Slides for Building an Effective Data Warehouse Architecture | James Serra's Blog

  5. Its really grt post and made me to learn , ‘ how to use non-equi join using lookup and merge join transformation. Thanks a lot.

  6. Pingback:SQL Server Agent job steps vs SSIS - SQL Server - SQL Server - Toad World

  7. Another point worth a mention is error-handling when importing/exporting rows. It is a lot easier to handle erroneous data and process it differently in SSIS as compared to SQL.

  8. Pingback:Preparation for the 70-467 SQL BI exam | x86x64

  9. Pingback:SQL Server:SSIS vs Stored Procedure – Andy Tsai學習筆記

  10. Hi James,

    I have a question about ETL. When designing an ETL we can do data transformation steps in database (procedures or SQL) and we can do this steps in a ETL tool (PowerCenter, Pentaho DI, SSIS…), as far as I know there are steps that have better performance in ETL tool and others have better performance in database. For example there are ETL tools that make the sorts or aggregation faster than database procedures or SQL.I would like to know if there are more steps that go different in a ETL tool than in a database, in order to have some criteria to make a design decision.

    Thanks in advance

  11. The greatest pro T-SQL point is conversion to another database, like Oracle. There are tools to convert T-SQL code. There are no tools that I’m aware of that will convert SSIS packages.

    So , not only is T-SQL faster in performance, but if the business is even considering moving from SQL Server to Oracle then T-SQL is definitely the way to go.

  12. I’m just curious how this blog post holds up in 2023. Have there been any big developments that might change your points listed in the post?

    Thanks!

  13. I agree with using sql will make the transforms much faster. Export data into raw files, and import data into staging tables, then use sql to transform into your public schema. We saw improvement of more than 10x speed by moving our transforms to sql.

Leave a Reply

Your email address will not be published. Required fields are marked *

HTML tags allowed in your comment: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>