I have a chance to work with SSIS from the year of 2011 and till today. There are lot of advantages, disadvantages and limitations for integration services. Below are the best features which I liked:
1. Used for extracting the data from Datawarehouse and inserting in to any databases. It will be working for cross databases like Oracle, Sybase, DB2 and others. So it is used by ODBC & OLE.
2. Lot of Good features for Exporting and Importing the data in basics like .csv, .txt. .dat, .xlsx etc.,
3. Can integrate with many different data systems, not just SQL Server, and supports a number of built-in transformations.
5. Integration services are designed to deliver higher performances, so it is having massive capabilities of delivering the data quickly in nature.
6. Logging the errors and pieces of information are very easy since all involve Microsoft inbuilt codings by a developer.
7. SSIS is transforming the data without the need for a staging area like temporary tables or any other like TEMPDB area. As well it allows parallel execution of data flows which saves lot of times.
There are some of the dislikes which we handled in our project:
1. Performance is not like spring batches or Informatica since Microsoft servers having their own limitations.
2, It is more supportive of Microsoft regions rather than open provider networks in nature.
3. In order to improve performance, we have to procure more license cost of MS providers and securities.
4. SSIS generally better for simple transformations and not for complex transformations.
5. SSIS are more used in .NET Data providers as an alternative to the OLEDB Providers and not user friendly to the systems such as SAP and Oracle.
Basically, we are using the SSIS packages (.dtsx) in order to get the data from Enterprise Data warehouse in .dat format and using transformations for Subrogation. Basically .dat to subrogation format files. Once the files are converted to the required format, these are getting inserted into SQL server 2012 using the business logic. As well there are a lot of SSIS packages used for business transformation and daily basis to meet the business-driven information.