TrustRadius
Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.SQL Server Integration Services, worth a lookWe use SSIS in a number or different use cases. 1. Traditional ETL for our data warehouse. 2. Automate reporting. We have a number of reports that go out to thousands of customers daily. We have implemented SSIS to run these. 3. Application integration. With SSIS we are able to quickly integrate from our order entry and operations software into our ERP and Financial software.,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.,Funny thing, working with Excel requires hacks and work arounds. Really wish Microsoft would fix this. More transformations to reduce the need of 3rd party tools. Tasks like SFTP would be nice.,9,We partner with other companies to help provide value to their customers. We had a number of them that requested reporting sent to their customers monthly. Our traditional Devs spec'd they would need up to a week toget that coded, tested and released. With SQL Server Integration Services and SQL Server Reporting Services we had the first iteration out in a day. This sends out over 3,000 reports, using our Partners Branding we get these out in less than 1 hour saving development resources. In a growing company from Startup to Enterprise, integrating our home grown order entry into our ERP system was proving difficult by hand. Once I implemented SQL Server Integration Services we had it done in less than a month. This reduced our project implementation time by over 1 month which saved consulting time and expenses. We use SQL Server Integration Services to backup our production boxes, cleanse and change the data to comply with different countries regulatory requirements, and restore that data to our Development and Test environments allowing our developers to have the most recent data set possible.,SAP Data Services, Informatica Enterprise Data Integration and Syncsort Connect ETL (formerly DMX),Microsoft SQL Server,8,9Data integration for everyone. A powerful tool that anyone can master.We are currently using SSIS packages on two major projects: 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.,Connection managers for online data sources can be tricky to configure. Performance tuning is an art form and trialing different data flow task options can be cumbersome. SSIS can do a better job of providing performance data including historical for monitoring. Mapping destination using OLE DB command is difficult as destination columns are unnamed. Excel or flat file connections are limited by version and type.,10,Enabled migration of complex business/customer data with one person resource. Launched automated ETL package within weeks with less than 2 person resource. No production failures/downtime since launch.,Workato, Matillion, Microsoft SQL Server and Centerprise Data Integrator,Microsoft Dynamics 365 (formerly Microsoft Dynamics CRM), Workato, ClickDimensions, Microsoft Power BI, Microsoft Project, OneNote,9,10,3,3,ETL operations to maintain the data warehouse with near real time data. Data migrations bringing all parts of the organization under one hood. Complex bulk data import from global network of distributors.,In-place, complex data transformations - one time events that require the use of business logic to transform operational data . Minimal use of development resources to maintain business intelligence infrastructure,Data archival / purging Data cleansing,10,Control Flow and Data Flow views/screens/canvases are beautifully setup so that you can see top level and drill down to specifics. Error and warning icons on control flow and data flow tasks along with clear icons show you where you still need to update/fix the process. Tasks and connection validation on startup / update makes sure you are always aware of breaks/changes,Trying to improve performance / runtime can be painful because you have to keep track of what changes you made and the impact to performance. It's easy to get lost when making multiple changes what their impact on runtime is. Requires visual studio,NoSQL Server Integration Services: A Great "Free" ETL Tool for Most Use CasesIn my work in the health care field, we use SQL Server Integration Services as our default ETL tool of choice for pulling data into SQL Server from Teradata, Oracle, flat file and other SQL Server databases, and for creating data extracts to send to our clients, typically in the form of flat files.,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.,SSIS has been a bit neglected by Microsoft and new features are slow in coming. When importing data from flat files and Excel workbooks, changes in the data structure will cause the extracts to fail. Workarounds do exist but are not easily implemented. If your source data structure does not change or rarely changes, this negative is relatively insignificant. While add-on third-party SSIS tools exist, there are only a small number of vendors actively supporting SSIS and license fees for production server use can be significant especially in highly-scaled environments.,8,Because SSIS comes "free" with SQL Server, there is no additional one-time or recurring software license fee to pay. SSIS has a fairly shallow learning curve so any Microsoft-oriented software development team can adopt it and put it into production service with very little training time needed. Packages created in SSIS can easily be transitioned from development to production use either via direct deployment to SQL Server or via a deployment management system.,IBM InfoSphere DataStage and Talend Open Studio,Microsoft SQL Server, Visual Studio IDE, Oracle Database, Teradata Database,9,9SSIS enables you to build the data lake of your dreams!The IT department at our organization uses SQL Server Integration Services. We use SSIS to perform extract, transform, and load (ETL) data operations. Our primary use is to move data from a source system or database, restructure the data to optimize it for reporting, and store it in a database instance used for reporting.,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.,The package publishing feature has gotten better over the years, but it could still be simplified. Incorporating features from add-ons into the standard application would be helpful (mostly in relation to data sources). Easier configurations for multiple publishing targets (dev/test/prod) with associated data connections.,10,By using SSIS, we have been able to eliminate simple, redundant data move operations that were conducted manually. We have been able to build a stable data repository that combines data from different sources and provides a more holistic view of the business's data. SSIS provided a flexible framework that we were able to use to connect to a variety of data sources--Oracle, SQL Server, Teradata, CSV, and web--which enabled us to build a system for business-critical reporting.,Informatica PowerCenter and IBM Cognos,8,Microsoft SQL ServerIn the world of IT this should be a standard tool, at least in any Windows environmentEven though we are a smaller company, we use this huge army Swiss knife to accomplish a number of technical IT tasks. First and foremost we use it to integrate systems like MS CRM and SAP, constantly enhancing the data flow and making tasks easier to accomplish for the users. Secondly, I personally use it clean up data and maintaining my BI ETL scripts running out 12-year-old data warehouse for our analysis cube. The actual tool is currently only in use by the IT department by using Visual Studio (BIDS).,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,File handling Integration with office tools could always be improved, MS, as usual, provides these 80% solutions to make room for third vendors. Error message or event handling, better messages and link to processes.,10,Like any IT department, we almost always have an MS SQL license and therefore also access to this tool, though the free version also works just fine with a little less functionality. Makes things like data cleaning or integration easy. Scheduling and running SISS jobs can be done in many ways, very flexible. Negative. I would say source control is difficult, there’s no elegant solution.,,Microsoft BI, MS SharePoint, Microsoft Dynamics 365 (formerly Microsoft Dynamics CRM), Cozyroc, Kronos Workforce Central,10,10SSIS - Strong performer in the traditional data space, but can be improved in handling JSON objectsWe implement data solutions for our clients. We use SSIS frequently as an ETL and Data Integration Tool for clients that have the Microsoft enterprise licensing as it offers decent base-level capabilities for traditional database management systems, and we can justify the tool selection since the technology cost-effective under MS licensing.,Very good for traditional RDBMS ETL and integration. Good developer community support online. Good at ingesting structured flat files (CSV, TXT, Excel).,The tool struggles out of the box handling emerging datasets such as JSON feeds. Unstructured datasets can be challenging to work with. Some out of the box can be very resource heavy, and the UI is not very straight forward. Luckily there's a large community of SSIS users that can provide guidance.,8,We have delivered numerous projects to our clients using SSIS where the client has been very happy with the delivery of our solution. The big struggle with SSIS is having multiple devs on a single SSIS package, this can create merge nightmares from a source control standpoint. Overall SSIS is a good option for clients who have Microsoft enterprise licensing. It provides a good set of capabilities for RDBMS systems, but you will need more custom dev if you are ingesting emerging feeds such as JSON objects.,Denodo,Microsoft Power BI, Microsoft SQL Server, Denodo,8,8SSIS for data integration and ETL!We use SSIS to connect to API's and other DB stores/wh/marts, etc. In our projects, many different data sources and types exist, and we build BI from the disparate sources that exist within our larger clients. SSIS is used as tool to harvest those disparate sources and run jobs to populate our end DB which feeds our visualization tools.,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.,Debug and quickly/easily troubleshoot logic and errors while running jobs or procedures. Version control sometimes seems to be an issue when many different sources are coming into play.,8,Quick and easy tool to bring disparate data together to make final harvest a 1 stop shop for data visualization No need to pay elaborate hourly rates for Python scripts etc if you don't need to harvest data that way.SQL Server Integration Services: a great tool for small to medium sized implementationsSSIS is used within my organization to move data from one data source to another, performing data translations, transformations, lookups and calculations during the data movement. This process often includes very complex data transformation processes including the use of APIs, external references and various class libraries. SSIS is currently used in various areas across the entire organization to solve SQL server-based data transformation issues.,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,SSIS memory usage can be quite high particularly when SSI and SQL server are on the same machine SSIS is not available on any environment other than Microsoft Windows SSIS does not function with any database engine back-end other than Microsoft SQL Server,6,SQL Server Integration Services has had a positive impact on our organization as it has allowed us to integrate data from external data sources via CSV and flat files SQL Server Integration Services has a waning positive impact on our organization as we are moving more toward Big Data and Oracle data Given the initial investment in SQL Server Integration Services, the ROI has been positive as it has enabled business-critical ETL for the past 5 years,Oracle Data Integration Platform Cloud, Information Builders WebFOCUS and Informatica Cloud,Alteryx Connect, Informatica Cloud,6,6Good integration with MSSQLIt is mainly used by technical analysts with the purpose of delivering data integration and reporting projects to customers. It addresses the need for complex multi-source data ingestion, data manipulation, and integration to a MSSQL database.,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.,Component properties are not very well defined, which makes the learning curve harder As control flow and data flow often looking similar visually at first glace, it takes awhile to differentiate which one you are working on as users need to look at the tiny symbol and text on the tab to do so. A more straightforward color-coded or larger visual cue to differentiate between the two would make this easier.,8,As it is part of the SQL related products, customers are more receptive to the product due to the lower risk. This increased our product offerings in our solutions for projects. The work required to implement an SSIS project is relatively quick compared to other tools and it reduces the amount of time required by staff to configure.,Dundas BI,Dundas BI, camunda BPM, Microsoft SQL ServerIf your back end RDBMS is SQL Server, SSIS gives you a bang for your buck.SSIS was being used as an ETL tool both by IT as well as business teams. It is now replaced by a competitor though. The tool was being used for basic extraction and loading purposes, with hardly any complex data transformation being done. Though there were use cases to exploit the tool, neither Microsoft nor in-house consultants really helped with the tool, with the result being it getting replaced.,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,version control/configuration management Programmatic issues like NULL handling (it's RDBMS counterpart SQL Server database uses NULL differently) The source connectivity options should be enhanced,8,It wasn't used effectively, resulting in not realising ROI to the extent we had anticipated It was easy to learn, which resulted in business teams using it extensively without IT support, resulting in lots of packages/code to maintain,Informatica Enterprise Data Integration, IBM InfoSphere Information Server and Oracle Data Integrator,Informatica Enterprise Data Integration, Oracle Data Integrator, IBM InfoSphere Information Server,250,10,Simple extraction and loading self service data integration complex transformations,None, with the version it was then,None really,6,No,Price Product Features Product Usability Existing Relationship with the Vendor,Things have changed now and there are more use cases to consider than what we had considered years ago. It's a different ball game now. We might do it grounds up this time,9,Don't remember instances,Pretty much everything is easy to use,complex transformations which SSIS can't really handle,No,8SSIS is one of the best ETLsSQL Server Integration Services is used for the following purposes in my organization. 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 Lookups Performance,Script components are always a problem Cannot debug properly inside of Script component,9,Positive Impact: Faster Performance Negative Impact: Script Task and Script component always hangs up,Informatica Enterprise Data Integration,Informatica Enterprise Data IntegrationA solid ETL tool. Data doesn't stay in one place, SSIS keeps it going where it needs to go.Like most businesses, we have various sources of data that management likes to be able to compare to each other. I use SSIS primarily to move data between our source systems and data marts and warehouses that our reporting software can be pointed at. I also use SSIS to deliver scheduled file exports to external customers or to import files into one of our critical systems for use. I even tend to use it for non-SQL related things such as file system and ftp tasks. If it needs to be extracted, transformed, or loaded somewhere, I use SSIS to do it.,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!,I know in my "pros" comments, I said it was nice because we already had access to SSIS by virtue of being able to install it on existing SQL servers with no additional license cost. But, if you rely heavily on SSIS, you will want to have it on its own server rather than letting it share resources with a very active SQL server. That means additional licenses. It can consume a lot of resources, depending on the amount of data you're pushing through SSIS at any given time. Current versions of SSIS do a much better job of managing deployment of packages into production. It used to be an all-or-nothing proposition so if you had to make a small change to a project that had many packages in it, you'd have to redeploy the entire project which means lots of extra testing. The introduction of package level deployment was welcome. SQL server and SSIS play very well together when they have enough resources. If you're using virtual servers and can add CPU/RAM/Space easily, then by all means, put them together and manage the resources so they stay out of each other's way. If you don't have the capability to do that, then you'd be better off having SSIS on a separate server. When everything is working well, it is amazing. But if you make SSIS and SQL fight over resources, it's not pretty (SQL wins that fight by the way in case you were wondering!) If I'm being honest, I haven't had to point SSIS to a huge variety of source systems. It could be that SSIS doesn't play well with certain DBMS' (I've heard Sybase compatibility complaints before) and you'll need to do some research and testing before actually using it in production.,10,I use SSIS to automate tasks that I'm repeatedly asked to do. "Hey, can you go into the system and close any open orders that we've fully filled?" Sure....then I schedule a package to do that for me every hour so I'm never asked again. It saves me time, which gives value to the company. It removes the risk of human error. When people build files and send them, there's the risk that it doesn't happen the same way every time or gets forgotten. With SSIS, you spend some up front time building a process, but then you deploy it and forget it (unless it emails you that there was an error. You are putting error handling in your ETL, right?). Very repeatable and consistent business solution.Don't let SSIS rest if you already have it with SQL Server comboIn 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 https://sqldusty.com/2011/11/06/data-cleansing-with-regular-expressions-in-ssis/ https://www.linkedin.com/pulse/using-regular-expression-file-filter-ssis-sean-werick/ 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.,Be careful when you edit a package, if the version is above the SSMS you are using then it will not be compatible. You have to compile or edit the SSIS package in the same version of SSMS you are using. To explain it a bit better if you have SQL 2014 in your laptop, pull a package for the DB server which is running SQL 2012, after you edit the package it will not be allowed in the SQL server. Python, Perl scripts are still a high competition for SSIS, mostly because they are very easy to manipulate, if you need a change you can do it directly with notepad. Plus Python now has an add-on called Pandas which is great for manipulating data.,9,It comes with SQL server, so if you are already using that for the DB, we had no charge for using SSIS for the same solution. That is one big advantage of Microsoft, they provide the whole combo for BI at once. ROI is a time measure, if you bought SQL just for the DB, but then you want to use SSIS, most probably it is already there. If you try even an open source solution, you will have to install it and get trained on it, for SSIS the learning curve is very short. Be careful, SSIS is for ETL, not for middleware at an enterprise level. In the begining it might work but it is not the purpose of the tool, for middleware solutions where the business depends on, don't get confused and if you are still looking for Microsoft go for BizTalk.,,TIBCO JaspersoftGreat ETL toolWe currently use SSIS for imports of purchase orders into our ERP - SYSPRO. These purchase orders come in various file formats: EDI, XML and excel spreadsheets. SSIS helps us aggregate these various files into a common import platform and apply business logic such as ship date calculations, SKU availability checks, customer hold checks etc.,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.,Not sure if it has JSON support but if it does, that would be awesome! Basically, the ability to consume data from a JSON data set. In as much as Microsoft built it for the SQL database, it would be awesome if we could leverage SSIS for data ETL into other databases like MySQL and Oracle etc. Add more color themes! The default color theme is old school and really sucks if you ask me.,8,Saves us a considerable amount of time required to import our data. Streamlines the order import/processing process for our business and makes it easier for us to meet our target ship dates as we have no bottlenecks during the import flow.,Microsoft Visual Studio Team System, Microsoft Azure and GitHub,Microsoft Azure, GitHub, Slack, Visual Studio IDE, Microsoft Visual Studio Team SystemSSISI've used SSIS to support individual departments within an organization. Typically I use SSIS to automate migrating and transforming data from one location to another. SSIS has a diverse range of source and destination formats that makes it easy to move data between different systems. There are many add on tools for other source / destinations that are not out of the box. For example, Dynamics CRM.,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.,The one issue that I have with SSIS is that sometimes the business logic gets baked into the SSIS package. This can make it harder to debug. In some cases this makes sense if the source and destination is not a database. However, when using a database as a source I prefer to manipulate and transform the data via sql and then simply expose the dataset to SSIS after the data has been prepared. I find it easier to write and debug sql directly rather than working in SSIS. However, in cases when a database is not involved then putting the business logic in SSIS makes sense.,9,SSIS can have significant ROI by automating data flow processes and putting data integrity checks and transformations in place during the process. The reliability of the process increases and time is recouped because no end user is responsible. Ability for non programmers to create and automate processes.,,QuickBooks Online, Microsoft Visual Studio Team System, Visual Studio IDE, BitbucketSSIS AddictIntegration 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,8,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.,Use it if you have a Microsoft StackI cannot say for the whole organization, but we use SSIS for just about all our automation processes. When managing a large data warehouse it is incredibly useful to automate the ETL process. We primarily use it for the data warehouse, but it's versatile enough to use for other automation tasks, reports, and notifications.,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.,I think it handles undefined/dynamic data sources poorly. Considering that we use it primarily to ETL data from other systems across the whole organization to bring into our BU's data warehouse, we sometimes have issues when the source has changed. If someone adds a column without letting us know, we'll need to modify the SSIS packages. Sometimes the error codes are vague or cryptic. When debugging a SSIS package I have to google the code or error message and hope someone has a similar issue on stack overflow. SSIS really only works if you're already using a lot of Microsoft Products like Microsoft SQL Server or SQL Server Reporting Services. As mentioned in the name of the application, "integration services", it's designed to integrate your products together so that you can get the most out of it.,8,Using SSIS has allowed us to free up a lot of time from our business users. We do a lot of predictive analytics here and we generate models/reports for various business units outside of our own. This involves ETL, running stored procedures, executing R scripts, and running other analytical tools over the data and generating data. By automating a lot of these tasks, these users are free to do more research and explore other opportunities for revenue/profit. SSIS is a pretty popular tool and it is not difficult to hire users that have experience. We don't need to spend a lot of time training new associates no how to use the product which saves us a lot of resources. Overall, we're able to achieve our business goals because it does what it's intended to do. We're able to integrate our tools and applications together to create new products and revenue streams.,informatica and talend,Microsoft SQL Server, dbForge SQL Complete, SQL Server Business Intelligence Manager, MicroStrategy Analytics, Microsoft ExchangeSSIS- Mundane but DependableSSIS is utilized as a systems and data integration tool, and for performing a variety of ETL tasks. It is utilized by the IT department to support business applications, particularly where two or more systems require data exchange. It is a mature product (stable and reliable) and comes as part of standard SQL Server implementations so its fairly simple to utilize.,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 IDE (SQL BIDS) is lacking, particularly when compared to Visual Studio for .NET development. It was carried over (at least in look and feel) from the legacy DTS tool. It could use a complete redesign from scratch. Considering how superior the VS .NET IDE is, the inferior SSIS BIDS IDE is unacceptable. SSIS is very Microsoft centric. This is a strength when dealing with pure MS technologies, but becomes a weakness when dealing with disparate, distributed systems, including cloud computing. Other ETL tools for example easily integrate with everything from AWS to Google Drive to Sales Force. SSIS deployment model is clunky and non-intuitive.,7,SSIS comes with standard SQL Server implementations, so it does not cost extra to utilize. On the other hand, its inflexibility makes it inferior to some other leading ETL tools. Investments into tools other than SSIS must be weighed against the pricing model for shops already using SQL Server.,Talend Open StudioSSIS is terrific for all your ETL needs.Our laboratory uses SSIS to transfer data between servers and databases, to data warehouses, reporting services and to and from file sources in a complex SQL Server environment that includes Merge Replication and SQL Server Reporting Services. Besides internal transfer, transformation, and manipulation of data, including archiving of data, we use SSIS to export laboratory results to external systems and import scheduling and sampling information from external systems.,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.,One of the most frustrating things about SSIS, at least in its 2008R2 incarnation, is that of the annotations. Formatting annotations, getting them to wrap in the box, etc., is either extremely difficult or impossible without editing the XML of the SSIS package. You also CANNOT copy and paste annotations, so if you have several similar ones, you have to type every one of them from scratch. It makes you want to scream. In the 2008R2 incarnation, setting up tasks to pivot data is very tedious and non-intuitive. I have heard that this improved in SSIS 2012, along with performance. We are trying to upgrade our SSIS and SQL server environment to 2012 or 2014 as soon as possible. Sometimes datatype conversion doesn't work implicitly when it should and you have to do it either explicitly with a query or with a datatype conversion task.,10,SSIS has allowed us to meet a complex variety of ETL needs without having to acquire a profound knowledge of application programming and the learning curve is really not bad considering the power available. We heavily depend on SSIS every day. SSIS or SQL Server doesn't play nice with Sybase databases, and it takes some work to make ETL go smoothly between Sybase and SQL server databases, particularly since SQL Server Management Studio can't query certain Sybase formats. It is included with SQL Server, so it is a very powerful tool included at no additional cost. It has saved us a lot of money over possible alternatives.,,10,NoSSIS fits perfectly in Microsoft environmentsSSIS is our main ETL tool since we are mostly a SQL Server shop. We use it to integrate data into all of our reporting environments, transforming and massaging data dynamically and connecting to many various sources along the way. It is also a quick way to move data out of other Microsoft environments such as Excel and Access Database.,Transform data Issue Logging Event Notification,Needs more connections to environments such as Hyperion Not as robust as Informatica No built in metadata management,8,Faster implementations Integrated reporting environments Cost savings via automation,Oracle Data Integrator,Team Foundation Server, Visual Studio.NET, Oracle Data IntegratorSSIS is great if when used in the right placeEe create master data management and workflow software for oil and gas companies which involves getting and writing data to other systems. We use SSIS for all integration as it helps dealing with different systems.,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,Project deployment model to allow deployment of single packages Allowing to apply one transformation on all columns, like changing all column types from nvarchar to varchar instead of doing it one by one Mamed parameters from inline SQL statments using OLEDB command, currently they are called parameter 1, parameter 2...etc., which is confusing when you have many paramters Pass authorization tokens when calling remote processes like a console application, currently being called as anonymous,8,Comes free with SQL server so no additional license Easy to learn and material is available for free, no need for expensive training,Visual Studio.NET, Microsoft SQL ServerA Reliable Data Preparation Tool.SSIS is used by the IT department for extracting, transforming, and loading data. The most common application for SSIS would be for cleansing and restructuring data while it is being loaded into a data warehouse.,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.,While SSIS does provide a plethora of architectural options, all of these options can at times be overwhelming. Some competing products offer a more straight forward and streamlined approach. SSIS does not currently provide any templates, although this is supposed to be addressed with the upcoming release of SQL Sever 2016 Connecting to Oracle databases is not easy, SSIS still requires the installation of other tools.,8,Data preparation is the majority of the effort involved in data warehousing projects. The amount of time it takes to prepare data is the same now as it was when the product was introduced in 2005. There are more features now, but the amount of time that you are spending with the tool has still remained the same. SSIS is a very widely used tool, making talent easy to find. Because its included with SQL Server, you don't have to go through any additional effort to purchase this tool. If you own SQL Sever then you already own SSIS.,SAP Data Services,Microsoft Power BI, Microsoft BI, Microsoft SQL ServerA high-performance ETL productIn our department we use SQL Server Integration Services daily. The main use is to do data processing for several ETL processes. Thanks to the use of SQL Server Integration Services we can upload data to the data warehouse in different ways such as a bulk load or with incremental loads thanks to the use of SCD task data.,High data load speeds Many data processing modes Many data sources and destinations data possible,For some tools you need to integrate third-party packages Depending on the version you must have the right visual studio version to develop and deploy Not really intuitive,9,SSIS is integrated into SQL Server licenses Once developed your packages are very stable and it requires relatively little maintenance If used wisely, it saves a lot of working time,DataStage,Visual Studio.NET, SQL Data Warehouse, Microsoft SQL Server,4,,Import data from external sources Integration of heterogeneous data Loading the data warehouse,Creation of real-time online solutions Packages automation Communications,ETL Other data import OLAP & Big Data integrations,8,No,Product Features Product Usability,Should I decide personally whether to choose SQL Server integrationServices, I would first estimate any type of data structure and the technologies currently used in the company. I would estimate the different types of skills in the company. Based on my experience I am sure that I would definitely repeat the choice to use SSIS as tool of extraction transformation and loading of data.,Implemented in-house,No,Change management was a minor issue with the implementation,compatibility issues with software versions in use integration of third-party tools verall understanding of the product,10,8,No,I've never had need special support on this product if not at the beginning of my experience when I had to attend a training course on using Microsoft sql server integration services and sql server analisys services, of course it is advisable to follow the study using the official books .,Bulk load IMport data Data trasformation,Manage data sources structurally dynamic integrate complex scripts or .dll control the updating of records without the use of SCD,No,8SSIS is ETL's best buddy for skilled SQL DevelopersSSIS is a powerful tool to perform various ETL-like functions between homogenous and heterogeneous sources of data. It was widely used in all organizations I worked since it provides an easy way to create data transformations.,Easy connection configuration Powerful wizard for data mapping Native exception handling User-friendly interface Easy to learn Package can be deployed via Visual Studio,Requires programming experience for custom tasks Shell version of VS used for SSIS package development doesn't support C# as scripting language (needs at least professional edition) Some tasks are hard to debug, aren't they?,10,SSIS provided good value for the money SSIS comes as a feature of SQL Server Standard/Enterprise etc. editions so we don't have to spend extra money for other ETL tools,,SQL Server Business Intelligence Manager, Solarwinds Database Performance Analyzer for SQL Server, SolarWinds Server & Application Monitor, WorkfrontThe ReviewIt is been used in the entire department to support enterprise analytics and reporting,User friendly Compatible with windows Cheap and good for mid size companies,Needs to be capable to hold large data,8,Positive impact is that it is easier to train you employees and it is easy to learn Negative impact is, in a time sensitive environment it may fall, so the improvement it needs is to hold large data efficiently,Informatica
Unspecified
SQL Server Integration Services
190 Ratings
Score 8.0 out of 101
<a href='https://www.trustradius.com/static/about-trustradius-scoring' target='_blank' rel='nofollow noopener noreferrer'>trScore algorithm: Learn more.</a>

SSIS Reviews

SSIS
190 Ratings
<a href='https://www.trustradius.com/static/about-trustradius-scoring' target='_blank' rel='nofollow noopener noreferrer'>trScore algorithm: Learn more.</a>
Score 8.0 out of 101

Do you work for this company?

Show Filters 
Hide Filters 
Filter 191 vetted SSIS reviews and ratings
Clear all filters
Overall Rating
Reviewer's Company Size
Last Updated
By Topic
Industry
Department
Experience
Job Type
Role

Reviews (1-25 of 32)

Companies can't remove reviews or game the system. Here's why.
Steven Gockley, MBA, MCSA profile photo
Score 9 out of 10
Vetted Review
Verified User
Review Source
We use SSIS in a number or different use cases. 1. Traditional ETL for our data warehouse. 2. Automate reporting. We have a number of reports that go out to thousands of customers daily. We have implemented SSIS to run these. 3. Application integration. With SSIS we are able to quickly integrate from our order entry and operations software into our ERP and Financial software.
  • 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.
If you are a SQL Server shop, it is almost not even a question. The tool slides in nicely and is fairly easy to get going and implement. I have used it for Data Warehousing, Report Automation, DBA Task Automation, System Integrations and even Web Testing. It is extremely flexible and anyone working heavily with SQL Server should really look at using it.
Read Steven Gockley, MBA, MCSA's full review
No photo available
Score 10 out of 10
Vetted Review
Verified User
Review Source
We are currently using SSIS packages on two major projects:
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.
Read this authenticated review
No photo available
Score 8 out of 10
Vetted Review
Verified User
Review Source
In my work in the health care field, we use SQL Server Integration Services as our default ETL tool of choice for pulling data into SQL Server from Teradata, Oracle, flat file and other SQL Server databases, and for creating data extracts to send to our clients, typically in the form of flat files.
  • 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.
SQL Server Integration Services is extremely well built for creating packages to run ETL operations in environments where the structure of the source and/or destination data never or rarely changes, however, it tends to be difficult to maintain packages in production environments where the structure of the data changes frequently.
Read this authenticated review
No photo available
Score 10 out of 10
Vetted Review
Verified User
Review Source
The IT department at our organization uses SQL Server Integration Services. We use SSIS to perform extract, transform, and load (ETL) data operations. Our primary use is to move data from a source system or database, restructure the data to optimize it for reporting, and store it in a database instance used for reporting.
  • 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.
SSIS is well-suited for scheduled data loads, such as scraping web pages for currency rates or storm-related delivery delays and writing the results to an application database or copying transactional data from a source system, optimizing it for reporting, and writing to a reporting server. SSIS is also great in helping to combine data from disparate sources to build a deeper data analysis platform.
Read this authenticated review
No photo available
Score 10 out of 10
Vetted Review
Verified User
Review Source
Even though we are a smaller company, we use this huge army Swiss knife to accomplish a number of technical IT tasks. First and foremost we use it to integrate systems like MS CRM and SAP, constantly enhancing the data flow and making tasks easier to accomplish for the users. Secondly, I personally use it clean up data and maintaining my BI ETL scripts running out 12-year-old data warehouse for our analysis cube. The actual tool is currently only in use by the IT department by using Visual Studio (BIDS).
  • 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
Any integration tasks in a Windows environment like AD or application integration, DB integration, etc. The drag and drop workflow and easy language, for example, derived data changes are so easy to make that it reminds of Excel functions. Full flexibility to draw up any workflow and easy troubleshooting using the data viewer.
Read this authenticated review
No photo available
Score 8 out of 10
Vetted Review
Verified User
Review Source
We implement data solutions for our clients. We use SSIS frequently as an ETL and Data Integration Tool for clients that have the Microsoft enterprise licensing as it offers decent base-level capabilities for traditional database management systems, and we can justify the tool selection since the technology cost-effective under MS licensing.
  • Very good for traditional RDBMS ETL and integration.
  • Good developer community support online.
  • Good at ingesting structured flat files (CSV, TXT, Excel).
SSIS is very well suited if your project(s) involves ETL or data integration of traditional relational database systems.

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.
Read this authenticated review
Stu Teel profile photo
Score 8 out of 10
Vetted Review
Verified User
Review Source
We use SSIS to connect to API's and other DB stores/wh/marts, etc. In our projects, many different data sources and types exist, and we build BI from the disparate sources that exist within our larger clients. SSIS is used as tool to harvest those disparate sources and run jobs to populate our end DB which feeds our visualization tools.
  • 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's well suited to play a role in bring a bunch of different sources and types together into 1 single/useable location. It is less appropriate for customers on a very tight cloud DB budget. If you wanted to run nightly jobs, those tally against your consumption of data usage and jobs/procedures can add up quickly in the cloud computing world.
Read Stu Teel's full review
No photo available
Score 6 out of 10
Vetted Review
Verified User
Review Source
SSIS is used within my organization to move data from one data source to another, performing data translations, transformations, lookups and calculations during the data movement. This process often includes very complex data transformation processes including the use of APIs, external references and various class libraries. SSIS is currently used in various areas across the entire organization to solve SQL server-based data transformation issues.
  • 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
Microsoft SQL Server Integration Services is suited for development by those who are NOT very experienced developers. End-users with some database experience may find the development environment easy to use allowing development of basic ETL. Experienced developers will likely feel restricted by the "Microsoft-only" interface. Additionally, many larger organizations that have made a significant investment in databases other than SQL Servers will be unable to use SSIS against those database servers.
Read this authenticated review
No photo available
Score 8 out of 10
Vetted Review
Verified User
Review Source
It is mainly used by technical analysts with the purpose of delivering data integration and reporting projects to customers. It addresses the need for complex multi-source data ingestion, data manipulation, and integration to a MSSQL database.
  • 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.
SSIS is suitable for MSSQL related projects/works due to how well it integrates and performs data manipulation. It is suitable for moderate data input ingestion rates but not suitable for projects where a high volume of data is required to be ingested and processed rapidly.
Read this authenticated review
No photo available
Score 8 out of 10
Vetted Review
Verified User
Review Source
SSIS was being used as an ETL tool both by IT as well as business teams. It is now replaced by a competitor though. The tool was being used for basic extraction and loading purposes, with hardly any complex data transformation being done. Though there were use cases to exploit the tool, neither Microsoft nor in-house consultants really helped with the tool, with the result being it getting replaced.
  • 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
If the back end RDBMS is SQl Server and if you are migration from Oracle or DB2 to SQL Server, SSIS does the migration job very well. It's tightly integrated with SQL Server. However, issues like NULL handling etc persists. Also, if the integration platform has unsupported connectivity or drivers, then SSIS usage becomes challenging.
Read this authenticated review
No photo available
Score 9 out of 10
Vetted Review
Verified User
Review Source
SQL Server Integration Services is used for the following purposes in my organization.
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
  • Lookups
  • Performance
SQL Server Integration Services is best when we use it with Microsoft SQL server database
Read this authenticated review
Greg Goss profile photo
Score 10 out of 10
Vetted Review
Verified User
Review Source
Like most businesses, we have various sources of data that management likes to be able to compare to each other. I use SSIS primarily to move data between our source systems and data marts and warehouses that our reporting software can be pointed at. I also use SSIS to deliver scheduled file exports to external customers or to import files into one of our critical systems for use. I even tend to use it for non-SQL related things such as file system and ftp tasks. If it needs to be extracted, transformed, or loaded somewhere, I use SSIS to do it.
  • 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!
If you need to move data around or direct the workflow of a process, SSIS can do it. It is a very capable piece of software that I use heavily every day. You do need to be careful because you can over-utilize it for simple things. If you just need to run a piece of SQL every hour to update some values, just use the Agent Scheduler, it's easier. But if you need to automate things in a repeatable and consistent manner, SSIS is a very good product.
Read Greg Goss's full review
Jose Pla profile photo
Score 9 out of 10
Vetted Review
Verified User
Review Source

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
  • https://sqldusty.com/2011/11/06/data-cleansing-with-regular-expressions-in-ssis/
  • https://www.linkedin.com/pulse/using-regular-expression-file-filter-ssis-sean-werick/
  • 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.
Extracting, transforming and loading data from multiple sources with different formatting is not that easy. SSIS provides different ways to connect or import from html, json, comma separated, xml, or other databases, which makes it a very diverse tool.

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.
Read Jose Pla's full review
No photo available
February 15, 2018

Great ETL tool

Score 8 out of 10
Vetted Review
Verified User
Review Source
We currently use SSIS for imports of purchase orders into our ERP - SYSPRO. These purchase orders come in various file formats: EDI, XML and excel spreadsheets. SSIS helps us aggregate these various files into a common import platform and apply business logic such as ship date calculations, SKU availability checks, customer hold checks etc.
  • 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.
1. Great for ETL (Extract-Transform-Load) data operations.
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.
Read this authenticated review
Eddie Brady profile photo
August 10, 2017

SSIS

Score 9 out of 10
Vetted Review
Verified User
Review Source
I've used SSIS to support individual departments within an organization. Typically I use SSIS to automate migrating and transforming data from one location to another. SSIS has a diverse range of source and destination formats that makes it easy to move data between different systems. There are many add on tools for other source / destinations that are not out of the box. For example, Dynamics CRM.
  • 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.
SSIS is well suited for any processes that can be automated to move data from a source to a destination. However, I don't think SSIS can work directly with Rest API's during it's processing. If that is required than it would be necessary to build your own custom SSIS component to enable this functionality. Extending SSIS to permit this is possible.
Read Eddie Brady's full review
David Milillo profile photo
March 29, 2017

SSIS Addict

Score 8 out of 10
Vetted Review
Verified User
Review Source
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
Well suited:
  1. Full refresh loading files (Excel and Flat File) into SQL Server.
  2. Integrating .Net (VB/C#) scripting
Less suited:
  1. Incremental loading
  2. OLAP database loading
Not suited:
  1. Streaming, real-time/near real-time loading
  2. Big data loading
Read David Milillo's full review
Hung Nguyen profile photo
Score 8 out of 10
Vetted Review
Verified User
Review Source
I cannot say for the whole organization, but we use SSIS for just about all our automation processes. When managing a large data warehouse it is incredibly useful to automate the ETL process. We primarily use it for the data warehouse, but it's versatile enough to use for other automation tasks, reports, and notifications.
  • 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.
As mentioned in the pros and cons, SQL Server Integration Services is great when you're running a Microsoft stack. We're loading data from all over into our data warehouses and moving them between other SQL instances all the time. I can whip up a package and deploy it in less than 5 minutes to get our data moving between SQL servers. It integrates really well and is flexible enough that you can supplement any lacking functionality using third party plugins or building your own tools. Although this has been solved in later iterations, SQL Server Data Tools (which is used to build SSIS packages), did not have the functionality to download files from an FTP server using SFTP. I built a C# app that I could run using SSIS.
Read Hung Nguyen's full review
Eugene LaRoche profile photo
Score 7 out of 10
Vetted Review
Verified User
Review Source
SSIS is utilized as a systems and data integration tool, and for performing a variety of ETL tasks. It is utilized by the IT department to support business applications, particularly where two or more systems require data exchange. It is a mature product (stable and reliable) and comes as part of standard SQL Server implementations so its fairly simple to utilize.
  • 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 is best suited for use in a pure Microsoft environment, or where interfaces to external systems are file based. It is not ideal for integration into disparate systems that are not interfaced via flat file. SSIS is also ideal when utilizing the job scheduler built into SQL Server, as it is seamlessly integrated with SSIS. In other words, it's very easy to schedule an SSIS package to run automated using the SQL job scheduler. Running SSIS packages from other job schedulers is more problematic, unless that tool has built in SSIS support.
Read Eugene LaRoche's full review
Chris Morgan profile photo
Score 10 out of 10
Vetted Review
Verified User
Review Source
Our laboratory uses SSIS to transfer data between servers and databases, to data warehouses, reporting services and to and from file sources in a complex SQL Server environment that includes Merge Replication and SQL Server Reporting Services. Besides internal transfer, transformation, and manipulation of data, including archiving of data, we use SSIS to export laboratory results to external systems and import scheduling and sampling information from external systems.
  • 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.
Whenever you are moving data from one database to another, either on the same server or a different one, SSIS is a great tool. This is true for one-time transfers because it is so easy to set up and configure what you want to happen, and it is true for automated transfers that occur every day or every hour. If complex transformations of the data are necessary, including filtering, generation of new columns, merging datasets, whatever, SSIS is the best tool out there for ETL. If the task takes place within a database or between databases on the same server, and the task can be accomplished with a simple SQL script, it will often perform much faster than an SSIS package and require fewer server resources.
Read Chris Morgan's full review
Samir Patel, PMP profile photo
Score 8 out of 10
Vetted Review
Verified User
Review Source
SSIS is our main ETL tool since we are mostly a SQL Server shop. We use it to integrate data into all of our reporting environments, transforming and massaging data dynamically and connecting to many various sources along the way. It is also a quick way to move data out of other Microsoft environments such as Excel and Access Database.
  • 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.

Read Samir Patel, PMP's full review
Waheed Abualrous, MCP, MCTS, MCE profile photo
Score 8 out of 10
Vetted Review
Verified User
Review Source
Ee create master data management and workflow software for oil and gas companies which involves getting and writing data to other systems. We use SSIS for all integration as it helps dealing with different systems.
  • 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
if you don't have an Sql Server license or are deploying to any OS other than Windows you need to consider a different technology.
Read Waheed Abualrous, MCP, MCTS, MCE's full review
Tom Jaskula profile photo
Score 8 out of 10
Vetted Review
Verified User
Review Source
SSIS is used by the IT department for extracting, transforming, and loading data. The most common application for SSIS would be for cleansing and restructuring data while it is being loaded into a data warehouse.
  • 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.
SSIS is a good fit when you have structured data. If you're looking to prepare unstructured data for doing text analytics, this is not the right tool.
Read Tom Jaskula's full review
Luca Campanelli profile photo
Score 9 out of 10
Vetted Review
Verified User
Review Source
In our department we use SQL Server Integration Services daily. The main use is to do data processing for several ETL processes. Thanks to the use of SQL Server Integration Services we can upload data to the data warehouse in different ways such as a bulk load or with incremental loads thanks to the use of SCD task data.
  • High data load speeds
  • Many data processing modes
  • Many data sources and destinations data possible
SQL Server Integration Services is optimal for any process of data extraction, data transformation and data loading, and can be used quickly for massive loads. It can be used for scheduled and incremental processes. It is not optimal for data structures that continuously change their structure because in this case it requires continuous changes and deploys.
Read Luca Campanelli's full review
Elena Goryainova profile photo
Score 10 out of 10
Vetted Review
Verified User
Review Source
SSIS is a powerful tool to perform various ETL-like functions between homogenous and heterogeneous sources of data. It was widely used in all organizations I worked since it provides an easy way to create data transformations.
  • Easy connection configuration
  • Powerful wizard for data mapping
  • Native exception handling
  • User-friendly interface
  • Easy to learn
  • Package can be deployed via Visual Studio
I definitely recommend it. The only thing is that you have to be skilled to design a good package architecture otherwise support may be hard especially during migrations to the newer versions of SSIS engine (had that problem in the past).
Read Elena Goryainova's full review

Feature Scorecard Summary

Connect to traditional data sources (32)
9.5
Connecto to Big Data and NoSQL (25)
7.5
Simple transformations (32)
9.9
Complex transformations (32)
8.1
Data model creation (16)
7.1
Metadata management (20)
6.2
Business rules and workflow (25)
8.1
Collaboration (24)
6.1
Testing and debugging (30)
7.2
Integration with data quality tools (23)
8.3
Integration with MDM tools (23)
7.4

About SSIS

Microsoft's SQL Server Integration Services (SSIS) is a data integration solution.
Categories:  Data Integration

SSIS Technical Details

Operating Systems: Unspecified
Mobile Application:No