List of things SSIS does that SQL server either does poorly or not at all:
- Connections to a vast number of datasources – flat files, other RDMS systems, XML sources, etc.
- Complex error handling within dataflows – start and stop dataflows based on severity of the error. Throw an email to admins/DBAs when on error. Pick up the dataflow mid-stream after the error has been resolved.
- Dataflow auditing – Easily check and redirect data that does not meet/match certain criteria – get an email when a certain number of rows in a given process have no matches found. Easily collect statistics on dataflow processes
- Visual/logical representation of dataflows as well as logical modularity of dataflows
Way too often, we hear many misconceptions about what the Integration Services Service actually does. Some think it is required to execute packages. Others think it is used for checkpoint restarts. Others think it speeds up execution. So, which group is correct?
The answer is none of them. The SSIS Service, quite simply, is responsible for managing the Integration Services interface in SQL Server Management Studio. It enables the ability to import/export packages, view running packages, and view stored packages. It really doesn’t do anything more than that.
I guess it depends on what you are doing. SSIS is very powerful, just like old DTS. If you are loading lots of items and expect to have constant change, I would go SSIS all the way. If you are looking to load only a few items and it’s for lots of customers, I would put it in code. I prefer SSIS for in house ETL processes, but I use .Net at client shops when I need to load data from a legacy system into a SQL database. Now as I stated before if you have a lot of transformations and lots of different data silos to load, I think you would be crazy to do this in .Net and I would go SSIS. If you have only a few items to load and it’s for a single application and may be installed as part of an application at various clients, I would go .Net all the way. Just my 2 cents.
I don’t think you are entirely wrong, however you seem a bit misguided. SSIS is not intended to replace the Database Engine but meant to compliment it and enhance it. It’s very effective as an ETL tool. For instance, let’s say you wanted to extract a list of clients from one of your database systems and email that list of data to a Direct Mail house every week. With SSIS this is a very easy thing to put together. There’s an easy means of transferring data from SQL Server to a file (CSV, Excel, etc) and sending an email.
Additionally SSIS is nice in that it has the Script Task which will allow you to write VB Script to do a variety of things. From my history I’ve used it to perform data access and send data from one system to another. This will effectively operate the same as if you were to have an application doing the data transfer… but the great thing about SSIS is that you can deploy the package and schedule it run with the SQL Server Agent.
Hope you will like this post on Microsoft SQL Server.
If you really like reading my blog and understood at least few thing then
please don’t forget to subscribe my blog.
Connect With me on