SSIS

SSIS

About TrustRadius Scoring
Score 8.2 out of 100
SQL Server Integration Services

Overview

Recent Reviews

Our use of SSIS

9 out of 10
November 24, 2019
We use SQL Server Integration Services to import data into and out of our main SQL server databases. This data comes from a variety of …
Continue reading

Popular Features

View all 12 features

Simple transformations (40)

9.2
92%

Connect to traditional data sources (40)

8.6
86%

Complex transformations (39)

7.6
76%

Testing and debugging (38)

7.5
75%

Reviewer Pros & Cons

View all pros & cons

Video Reviews

Leaving a video review helps other professionals like you evaluate products. Be the first one in your network to record a review of SSIS, and make your voice heard!

Pricing

View all pricing
N/A
Unavailable

What is SSIS?

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

Entry-level set up fee?

  • No setup fee

Offerings

  • Free Trial
  • Free/Freemium Version
  • Premium Consulting / Integration Services

Would you like us to let the vendor know that you want pricing?

6 people want pricing too

Alternatives Pricing

What is Fivetran?

Fivetran replicates applications, databases, events and files into a high-performance data warehouse, after a five minute setup. The vendor says their standardized cloud pipelines are fully managed and zero-maintenance. The vendor says Fivetran began with a realization: For modern companies using…

What is Oracle GoldenGate?

Oracle GoldenGate is database management software for data integration, and availability support for heterogeneous databases.

Features Scorecard

Data Source Connection

6.3
63%

Data Transformations

8.4
84%

Data Modeling

7.1
71%

Data Governance

6.5
65%

Product Details

What is SSIS?

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

SSIS Technical Details

Operating SystemsUnspecified
Mobile ApplicationNo

Comparisons

View all alternatives

Frequently Asked Questions

What is SSIS?

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

What is SSIS's best feature?

Reviewers rate Simple transformations highest, with a score of 9.2.

Who uses SSIS?

The most common users of SSIS are from Mid-sized Companies (51-1,000 employees) and the Computer Software industry.

Reviews and Ratings

 (229)

Ratings

Reviews

(1-25 of 40)
Companies can't remove reviews or game the system. Here's why
Score 7 out of 10
Vetted Review
Verified User
Review Source
SQL Server Integration Services has been useful in implementing Extract, Load, and transform logic from various sources and destinations. we also use it to download data files from SFTP, FTP locations. We use it to refresh extract in tableau and run other 3rd party services via executing process tasks. It has been useful to address many other such challenges.
  • FTP Downloads.
  • Run 3rd party softwares.
  • Intuitive UI.
  • Need more connectors/sources/destination components.
  • Ability to support not just Microsoft services but others too.
  • More updates & new features.
More suited for Full loads, use along with other Microsoft services, ETLs Less suited for: use along with modern software/services, Near real-time integration.
Score 8 out of 10
Vetted Review
Verified User
Review Source
We use SQL Server Integration Services to consolidate data from multiple vendor products. SSIS is the engine that gathers that data and stores it on our main SQL database instance for reporting purposes. It is being used by the enterprise in our organization. It created one pain of glass using SQL Reporting Services to show what systems are in what vendor products - such as agents installed yes/no? Last checking date/time, etc...
  • If the job fails, we are alerted
  • It has a easy to use GUI to build projects
  • Does well with gathering data from different types of data sets
  • Converting flat files of data requires a data transformation that is tedious
Moving data from one location to another. Also transforming data from one source to another destination. It wouldn't be needed for operating system file management or copying of files.
Vladimir Salnikov | TrustRadius Reviewer
Score 8 out of 10
Vetted Review
Verified User
Review Source
We are using the SSIS as a major data export/import & converter between different data sources, including relatively old legacy stuff out from 90th. The beauty of SSIS is really advanced capabilities of data converts and mix up the data from different sources with all respect to keys and data relations. We use this mostly as an addition to the SQL Server Express edition (as a part of the import/export wizard) but on the sandbox I doing the tests of SQL Server 2016 Standard edition to mix up several RDBMS for data extraction into one data warehouse.
  • Extraction and convert data from various sources
  • Pipelining in the data extraction process
  • Unions between different data sources akin one meta datasource
  • Some labels in Visual Studio snap-in for MS SQL Server are collapsed on non-English (German & Russian tested) locales.
Well suited: all data extraction from file (spreadsheet-like) and RDBMS data sources, mix up them into one integrated meta-data source for future processing.
Less appropriate: big key-value data storages processed slowly, and hard to make data mining through uniting non-RDBMS and RDBMS data sources naive way. The data from non-SQL databases should be prepared accordingly to be represented in a table-like way if possible.
Score 9 out of 10
Vetted Review
Verified User
Review Source
SQL Server Integration Services is used by our department to support the entire organization. Several developers design our packages and as well our DBA team who develops, reviews, and implements these in production.
SSIS is a critical component in making data available across disparate systems from custom databases to local vendor and online applications.
  • SQL Server Integration Services design of dataflows allow us collect and merge data from a large variety of sources.
  • The structure of packages in a solution allow us to separated distinct functions yet share a common collection of parameters to ensure consistency.
  • The availability of environments to provide differing parameters is also a powerful option to allow us to design reusable solutions.
  • The deployment process could use the improvement of deploying the Parameters into an environment of choice. Having to do this manually is time consuming and error prone.
  • Propagation of values from within a package to a parent package is lacking. This is helpful when using a parent package to execute and report on a collection of smaller related packages.
There are certainly always other ways to provide integration as needed. I find SQL Server Integration Services very flexible and broad in the range of things needed to provide a robust integration solution. Vendor interfaces are possibly a key area in deciding what tools to use, and coworkers have used tools like PowerShell to help. We also use another Vendor add-on to help our SSIS implementation to ease use.
freida oliver | TrustRadius Reviewer
Score 9 out of 10
Vetted Review
Verified User
Review Source
Our department uses SQL Server Integration Services for extractions, ETL, migrations, etc.
  • Import data into tables
  • A lot of documentation/support on the web
  • Scheduling packages
  • Very easy to use
  • Error messages are could be more clear
Moving and transforming data are the best uses.
February 17, 2020

SSIS Just Works

Score 8 out of 10
Vetted Review
Verified User
Review Source
SSIS is being used for data interchange. This includes using it for traditional ETL to a corporate data warehouse, as well as for the production or intake of flat files for interchange with external sources. Sources include traditional RDBMS, web services based cloud sources, and flat files. We are beginning the process of extending SSIS to the cloud in conjunction with Azure Data Factory.
  • Process flow.
  • Connection to a wide array of sources.
  • Built-in upsert component.
  • Better operability with source control systems.
SSIS works great for 80+ percent of ETL use cases. It is particularly well suited for migrating and transforming data between traditional databases. If using the Azure cloud, it also has a number of built-in components to make that process easier. The base set of transforms are pretty robust and will perform most tasks with decent speed.
It's Microsoft support. I've found it to be generally good, once you get to the right person. The community is huge, so most of your questions will have been asked and answered on at least one of the major tech boards. MS has continually improved the product since its inception in 2005. Overall they've done a great job since the humble beginnings of DTS.
zahit bogus | TrustRadius Reviewer
Score 9 out of 10
Vetted Review
Verified User
Review Source
In my department, we design and manage ETL processes. We use SQL Server Integration Services (SSIS) to design a data warehouse. To create a data warehouse, we can make the stage layer and DWH layers with the SSIS ETL tool. We create data marts using aggregate functions in the SSIS ETL tool. In addition, we use the SSIS ETL tool to move data between databases.
  • Data migration is pretty fast.
  • SQL Server is highly compatible with the database.
  • We receive errors in moving JSON data to the database. It does not work efficiently in JSON-related data.
  • ODBC connections give a connection error after a certain period of time.
For enterprise business intelligence projects, the SQL Server Integration Services ETL tool is a logical choice to move your data to the data warehouse on a daily basis. The SSIS ETL tool has enough functions and capabilities to design the data warehouse. If you want to send your JSON data to the database, SSIS does not work here.
The SQL Server Integration Services (SSIS) ETL tool has a very strong user community. With the error codes, I get access to solutions in the forums. In addition, Microsoft products offer a wide range of consulting services.
November 24, 2019

Our use of SSIS

Score 9 out of 10
Vetted Review
Verified User
Review Source
We use SQL Server Integration Services to import data into and out of our main SQL server databases. This data comes from a variety of external sources and sometimes there is data format mismatch between source and destination. With an SSIS package, we can retain the mapping and data formats integrity from the source to the destination without having to go through the process every time.
  • Easy to manage projects and packages.
  • Ideal for repetitive tasks.
  • Can handle complex tasks consisting of multiple, diverse packages.
  • Include SSIS in the default installation of SQL.
  • The flexibility and different packages and options can make it confusing for first-time users.
  • Recommendations in the selections could make it easier to build a solution.
Well suited and designed to enable flexible data extract and transformation to and from an SQL server. It works very well for repetitive tasks and it is easy to manage (and change) packages once built. The use of templates makes the initial startup process simpler.

SQL Server Integration Services (SSIS) is not easy for new users due to the plethora of options available.
We were able to find answers to all our questions within MS KB articles and the larger online community. We have not run into any situation yet where we have had to seek specialized (paid) support, possibly because our business cases have not been very complicated. All the tools we needed to manage and use SSIS were included within SQL server installation media or freely available online.
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.
  • 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.
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.
I have never had to depend (or pay) for support on SSIS directly from Microsoft. There is a wide breadth of knowledge on SSIS in general and solving particular technical puzzles already available on the web. SSIS has been broadly adopted as the ETL tool of choice by many people and the users of its benefit from this expansive knowledgebase.
Steven Gockley, MBA, MCSA | TrustRadius Reviewer
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.
  • 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.
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.
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.
  • 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.
  • 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.
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
  • 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.
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.
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.
  • 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.
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.
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).
  • 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.
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.
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
  • 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
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.
Stu Teel | TrustRadius Reviewer
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.
  • 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.
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.
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
  • Script components are always a problem
  • Cannot debug properly inside of Script component
SQL Server Integration Services is best when we use it with Microsoft SQL server database
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.
  • 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.
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.
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
  • 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
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.
They are generally responsive and knowledgeable too
Greg Goss | TrustRadius Reviewer
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!
  • 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.
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.
Jose Pla | TrustRadius Reviewer
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.
  • 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.
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.
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.
  • 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.
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.
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.
  • 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.
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.
Score 8 out of 10
Vetted Review
Verified User
Review Source
In my current organization, SSIS is being used by the enterprise data warehouse team. This team uses SSIS to construct data warehouse, prepare data cubes, and fulfill ad-hoc data-related acquisitions.
  • Presents the flow of data processing very well, making it easy to learn/understand SSIS packages.
  • T-SQL and C# friendly.
  • Comprehensive configuration, logging, and error handling.
  • Some components are not working very well, including sorting, SCD, etc.
  • Different components could have different syntax or data type definition.
  • Not enough scripting learning materials.
For SQL Server users, SSIS would be a natural yet very wise choice. It works quite well with Microsoft bottom layer and offers an easy and more user-friendly way of manipulating data.
March 29, 2017

SSIS Addict

David Milillo | TrustRadius Reviewer
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
  • 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
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