Overall Satisfaction with SQL Server Integration Services
Integration Services is the primary extraction, transformation, and loading tool we use to populate our SQL Server and Azure SQL DB and DW for our data and our clients' data. We do a majority of our logic for preparing both reporting and application data within SSIS components, scripts, or within T-SQL Stored Procedures executed within SSIS Control flows. It is only used within my group but my group is the only group directly populating our reporting databases.
- Native data connections to SQL Server and Azure SQL DB and DW
- Flat file processing
- .NET C#/VB scripting
- Ease of use in designing and implementing control flows within conditional processing and looping
- Integration with Access/Excel should be more seamless and less problematic
- CASS certified address standardization
- Higher performing Slowly Changing Dimension functionality
- SFTP
- Incremental loading (deletion, upsert, etc.)
- PowerBI integration. I really really really want to be able to refresh reports via IS packages
- More Azure administration tasks
- Office365 and Sharepoint integration
- Investment in SS/SSIS was already done before I joined. But we could not operate without SSIS at this point. So much implementation has done within SSIS that migration would be next to impossible at this point.
I've used several solutions from just simple ETL processes of stored procedures run manually to using Informatica.
SSIS is a good in between solution. It has sufficient maturity to be preferable to scheduled procs in SS agent jobs. But does not have the sophistication and full feature set of Informatica. But significantly cheaper than Informatica. Plus since it's bundled with SS all SS shops already have it.
SSIS is a good in between solution. It has sufficient maturity to be preferable to scheduled procs in SS agent jobs. But does not have the sophistication and full feature set of Informatica. But significantly cheaper than Informatica. Plus since it's bundled with SS all SS shops already have it.