SSIS vs SSMS !

  • Here's a more theoretical question for you, to make change from all that hard code stuff.

    It all started when someone ( a 'user') asked me what SSIS was.

    So it was that I sat down and tried to explain some basics about what SQL Server was, how it worked etc.

    Ultimately, I come to a slide to explain SSMS, and a slide to explain SSIS.

    It is then that I realized that when in early development, I do often pull data into my DB with T-SQL and a linked server, or a bulk_insert.

    So why do I need SSIS again ?

    Ah yes, meta-data, wizards, auditing, logging, neat packages that simply be thrown at my scheduling utility.

    And of course, I don't have to stop and think too much about 'how' I pull data across different servers and mash them all together without taking 24 hours to run ... it's all worked out for me.

    So SSIS 'helps' me a lot .. but I could, in theory, do it all with views and T-SQL.

    I think.

    Thus it was that I found myself typing this little line :

    “There is nothing that a dedicated ETL Tool can do, that cannot be done via SSMS”

    I of course followed this up with:

    "One is just a hell of a lot easier than the other for specific tasks".

    But it's left me with a churning mind.

    If we ignore that fact that to perform some ETL tasks in SSMS with pure T-SQL, would be a hell of a lot of coding, and setting up of support procedures, log tables etc ...

    ... we could do everything that SSIS does, ourselves, right ?

    I'd love to hear comments on this.

    Have I missed something obvious ?

  • There are some things included in SSIS that it would be foolish to re-implement in T-SQL. However, if you consider the Windows Batch commands that can be issued via xp_cmdshell and the .NET code that can be implemented in SQLCLR modules part of T-SQL, and you overlook some aspects of securing SQL Server with respect to enabling those features in your instance then yes, you can do everything in T-SQL that you can do in SSIS.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Jeff Moden - a TSQL guru on this site - swears by TSQL (and thus SSMS) and never uses SSIS for anything.

    Reading text files --> bcp or bulk insert

    Reading Excel --> OPENROWSET

    And so on.

    The biggest issue probably is security issues, as opc.three has pointed out.

    But also row level error handling - which you can do easily in the SSIS dataflow - is problematic in TSQL. It requires a cursor, which will slow things down tremendously.

    So, everything is possible in TSQL, but it will sometimes be harder and/or much slower.

    Sometimes TSQL will be easier and much quicker (for example sorting, grouping and joining relational data).

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

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

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