What is our primary use case?
We have used SSIS in many ways. Primarily, it has been used for building ETLs for populating data warehouse and staging areas. We have developed a number of data marts that were populated. We build data migration packages, which have been reused a number of times with minimal configurations. Additionally, we build complex data integrations solutions and data hand-offs between different applications. We have even used it for creating and parsing SWIFT messages for data integration purposes. We also used it for email triggers.
How has it helped my organization?
SSIS is a tool which anybody can learn in no time. When we started the project, the whole team learnt it in one week's time and then were ready to start on project tasks. It gives you control.
What is most valuable?
Script task and Script component for custom tasks: It gives you power to build your own logic if your requirements are not being fulfilled with existing available components. While the big thing is you can build your own components, that is not so straightforward.
It's visual design interface is good and you can easily understand the flow.
The debugging capabilities are great, particularly during data flow execution. You can look into the data and see what's going on in the pipeline. It has good logging capabilities as well.
What needs improvement?
It should have other programming languages supported as well from a scripting perspective. Currently, only C# and VB.NET are supported, which limits it to .NET. It should have Java support as well.
We couldn't explore big data related components, and this area should be up to the mark, if not already. Certain data quality checks should also be part of it. Currently, this is the biggest area that should be looked into. Data quality is an essential part of the integration process and ETL. Currently, only the data profiling task is there.
For how long have I used the solution?
How was the initial setup?
Its setup is very easy. You don't need too many components to start building ETL tools. Plus, there are a lot of online learning available for this tool. It can be deployed on any Windows machine with minimal requirements.
What other advice do I have?
Overall, we remained in a good position and in control while using SSIS. We didn't face any challenges.
Which deployment model are you using for this solution?
Find out what your peers are saying about Microsoft, Informatica, Oracle and others in Data Integration Tools. Updated: September 2021.
535,919 professionals have used our research since 2012.