Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS vs SSMS ! Expand / Collapse
Author
Message
Posted Friday, October 12, 2012 8:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, February 07, 2014 7:35 AM
Points: 20, Visits: 92
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 ?

Post #1372179
Posted Sunday, October 21, 2012 2:14 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:25 AM
Points: 7,070, Visits: 12,523
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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy
Post #1375207
Posted Monday, October 22, 2012 1:12 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 12,206, Visits: 9,169
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).




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1375276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse