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.
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 ?