Reviews (1-25 of 32)
- It sits inside of Visual Studio and SSMS so you have a consistent look and feel across toolsets
- Extensions. We use Pragmatic work transforms that seamlessly fit into SSIS to make certain tasks easier
- Performance. SSIS is not the fastest tool out there but it is more than enough for our needs and since it is bundled with SQL Server you get great value.
1. To populate our data warehouse. Running SSIS packages from the server on a 10-minute schedule, we extract, transform and load the data into the warehouse to support all internal reporting and provide data as a service to our global partners.
2. Bulk data import to our CRM system. Building packages to run on-demand to bulk import structured data to our CRM instance.
In the past, we have used SSIS packages to complete a one-time migration from a legacy CRM system to the current CRM.
- Ease of use - can be used with no prior experience in a relatively short amount of time.
- Flexibility - provides multiple means of accomplishing tasks to be able to support virtually any scenario.
- Performance - performs well with default configurations but allows the user to choose a multitude of options that can enhance performance.
- Resilient - supports the configuration of error handling to prevent and identify breakages.
- Complete suite of configurable tools.
- SSIS is particularly well suited for jobs that need to be consistent, repeatable, and error managed.
- Ongoing extract, transform, load [ETL] jobs that are scheduled or manual.
- One-time ETL with complex datasets.
- Migrations of large datasets.
SSIS is not well suited for small or simple datasets that can be copied or exported safely to flat files for import. It is possible to do this but would generally take longer to build in SSIS unless there was a good reason to .remove manual handling of the data in transport or the action needed to be testable/repeatable.
- SSIS works very well pulling well-defined data into SQL Server from a wide variety of data sources.
- It comes free with the SQL Server so it is hard not to consider using it providing you have a team who is trained and experienced using SSIS.
- When SSIS doesn't have exactly what you need you can use C# or VBA to extend its functionality.
- Handles multi-step, complex data moves.
- Pulls from a variety of data sources.
- Add-ons are readily available to extend their usefulness.
- Integration with SQL Server and data tools.
- Almost no programming is needed, like drawing simple flow diagrams.
- If you want to be more advanced you can add some VB or C# programming if needed.
- Microsoft tool using all the great connectors, using any data source
- Easy to add a third part like Cozyroc
- Very good for traditional RDBMS ETL and integration.
- Good developer community support online.
- Good at ingesting structured flat files (CSV, TXT, Excel).
SSIS may not be the best tool if you are using it for ETL and data integrations for JSON and XML feeds. The native tree parser is not very good.
- Source system connectivity (API's, SQL DB's, Olap Cubes, etc)
- UI which allows less technical people able to quickly and easily complete the tasks.
- It handles SQL Server databases flawlessly
- It provides a robust developer interface
- It allows a developer to encapsulate complex scripts directly within an SSIS project or reuse scripts across projects
- It interfaces quite well with a large number of available libraries
- Visual programming makes configuration easy and accessible
- The ability to code also allows users to implement complex logic for data manipulation and etc.
- Easy integration with MSSQL Database instances.
- Source systems connectivity (RDBMS, Flat Files etc)
- Embedding SQL and other code in case of complex business logic and data transformations
- Multitude of data transformation options
- Ease of use, easy to learn
- Skills availability in the market
1. Extract data from the source database and transform it and then load it into a destination database
2. Use this for Balancing between 2 or more different databases
3. Extract the data from relational databases and load into data ware house.
- Transform the data
- There are many good workflow tools and ways to control the order in which things happen. In a short amount of time, you can quickly create a package that will move data from point A to point B and have it scheduled to run 4 times a day. Or if you need error handling or other business logic, you can spend more time and completely automate repetitive tasks. Robust? Check!
- SSIS can consume multiple sources of data. From flat files, to Excel, to Oracle, or DB2...I've been able to access multiple data types and move them in and out of SQL databases with SSIS. We had one linux system that ran a Basis database system and there was a need to have something done, but no one could figure out how to make it work. I was able to use SSIS to import files and execute code on a server that had nothing to do with SQL server. So flexible? Check!
- We already use SQL server almost exclusively for our enterprise database needs. The fact that we already have access to this tool at no additional cost to the business is a bonus. The fact that it is powerful, even better. Value? Check!
In the beginning, we had hundreds of Stored Procedures, instead of SSIS packages. The Stored Procedures were poorly made by some users, only thinking on the resulting query and not the execution performance, plus the people doing data mining created tables for a report and then they didn't eliminate such tables that only had one use, also some of those tables kept growing without being needed any longer.
The implementation and onboarding of SSIS was made with the intention to correct some of these T-SQL coding issues. It is easier to understand a diagram than sheets of T-SQL code with good documentation. Besides the performance for bulk inserts was better with SSIS than normal inserts in stored procedures. We were able to divide and define a bit better the roles, between SQL developers, Data miners, and BI engineers.
- Logging, this is essential when you do ETL. With SSIS you can run the package and see step by step the progress, how many tuples complied with the filters, like how many went left and how many were correct, or excluded.
- Using regular expressions with C# direct code by adding Script Components it's easier with SSIS
- Performance, it is difficult to demand good SQL code to every member of the BI team not everyone is specialized in T-SQL.
- SSIS standardizes a bit more the code and allows users not completely familiar with SQL or even C# to achieve what they needed, the package still needs to go through a code review but it is quite easier to understand.
The only main competition I have noticed is the combo of Python, Pandas, and Jupyter; but for that other solution, you will need an experienced team in scripting. So at the end is choose what your team feels more comfortable.
- Great for parsing data from various file formats into SQL server. As an example, we use it to extract data from XML, EDI and other flat files.
- Great for applying custom business logic in the ETL process. These business logic could be built into functions, stored procedures and applied through the SSIS packages.
- I like it's exception handling capabilities and how it's able to show the module that threw up the exception by highlighting it in red.
- Works very well with Visual Studio and as a matter of fact, you can build all your SSIS packages right from SQL without even opening up SQL server or BIDS.
2. Amazing if your primary database environment is SQL server.
3. Works great with Visual Studio and Microsoft even has it now on the Azure platform.
4. Works great with various file formats - XML, EDI, spreadsheets, flat files etc.
5. Works great in scenarios where it is necessary to apply business logic through stored procedures etc.
- SSIS allows you to run many processes in parallel. Thus, you can run multiple data flows simultaneously to increase the throughput of the migration process.
- SSIS provides many tools for transforming data during the migration process.
- 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
- Full refresh loading files (Excel and Flat File) into SQL Server.
- Integrating .Net (VB/C#) scripting
- Incremental loading
- OLAP database loading
- Streaming, real-time/near real-time loading
- Big data loading
- Clear GUI and ETL workflow. It's very easy to understand how the data is being managed. When pulling up a SSIS solution that someone else has created, it's very easy to see what's going on-- how the data is extracted, how it is transformed, and how it's being loaded.
- Deploying scripts. Once a proper package store is configured, you just need to hit deploy and it handles the rest. It's also flexible enough that you can still use SSIS packages without using an SSIS DB for version control by calling them through the file system. Or if you're one of those people who love batch scripts, you can also execute the packages through command line.
- SSIS Package Store. It's a great way to manage your versions and deployments. Bonus is that if you use a package store, it'll also give you error reports after the fact if a package fails for debugging. It'll tell you exactly what step failed and why.
- SSIS Integrates very well with other Microsoft products including Excel and Access. Other ETL tools may have a difficult time integrating with Access, so we have observed SSIS to be superior in this regard.
- SSIS has the capacity to do a fast bulk load (BCP) with transformations, within the bulk load itself. This feature is not available when utilizing the BCP utility outside of SSIS or from other ETL tools. To be clear, the transformation is occurring within the BCP component itself. Other ETL tools will have to utilize a non-BCP load (slower) or do the ETL after the load. This is a great feature I have not seen replicated in other tools.
- SSIS integrates seamlessly with SQL Server RDBMS, including SQL Jobs and Stored Procedures.
- SSIS has nice support, tools, and wizards for fixed length file processing.
- SSIS can query, filter, and transfer data between databases on different servers without establishing explicit trust relationships between those servers.
- SSIS can be used to refresh a reporting database from a transactional source database, transforming or flattening the data and tables as necessary to facilitate reporting. This can be done incrementally, or by emptying and refilling the reporting database from scratch.
- SSIS is configured through graphical interfaces that make it relatively easy to see the flow of data including where problems occur.
- SSIS has a number of tools that allow you to debug SSIS packages and track down problematic data or configurations.
- SSIS allows you to program Script Tasks in C# and VB allowing extremely powerful functionality including looping and sending consolidated alerts.
- SSIS allows you to control virtually every part of the SSIS package (connections, variables, etc.) using configuration files so you can have one package that can be used in several different places (such as dev, test, and production environments) only by editing the configuration file that the package uses when the job is scheduled.
- Transform data
- Issue Logging
- Event Notification
When dealing with Microsoft environments, it is the tool that is easiest to use and implement. When it comes to connecting to Oracle Hyperion or pushing data to non Microsoft environments there can be challenges.
SSIS allows for quick implementations where the flow and environment is not too complex or large.
- Makes it very easy to move data form one system to another
- So many out of the box transformations
- The script task gives you so much flexibility as you can write C# code to do almost anything
- Easy deployment and configuration after the 2012 release
- Providing developers with a wide range of architectural options.
- Providing the ability to connect to a wide array of data sources.
- Proving many different deployment options.
- High data load speeds
- Many data processing modes
- Many data sources and destinations data possible
- Easy connection configuration
- Powerful wizard for data mapping
- Native exception handling
- User-friendly interface
- Easy to learn
- Package can be deployed via Visual Studio
- User friendly
- Compatible with windows
- Cheap and good for mid size companies