SSIS

Provision of data integration services.

Loading data warehouses with secure files for future use.

Programming integration models without coding.

No room for improvement from my experience.

Everything has been operating positively.

SSIS is good for small, simple data syncs.

SSIS is good for moving data quickly into SQL Server (good batch performance).

Very hard to diff for code review when changes are made.

Very buggy IDE (freezes in Visual Studio, doesn't always show what's in the underlying XML properly)

Lacking lots of features or more modern tools, like ability to monitor for changes to the source schema and apply them to the target.

Offers real-time, message-based capabilities

Tight integration with SQL Server and visual studio

Ease and speed of implementation

Standardized data integration

Sometimes create issues in non-windows environments.

SSIS lacks support for alternative data integration styles.

Problematic integration with other products.

Data Migration

Flexibility to choose columns and rows

Add a Pivot to our data

Fuzzy Logic sometimes doesn't work properly, It can be improved.

The column extraction can be more flexible in terms of data types

The UI sometime hangs if data is huge or processing is too large.

Easily connect with a wide variety of sources.

Provide control and flow to job execution order.

Able to extend functionality through scripting tasks.

Not flexible when source/target tables and file formats change.

Inflexible with regards to varying data types when Excel spreadsheets are used as a source or columns are added (as per previous item).

Occasional issues around mixed development and production x86/x64 run times can be frustrating.

Connect to various sources

Basic transformations are included

Connect to various destinations

We still need plugins to connect to SFTP and other sources. Many vendors in the market give this by default

It's an ETL tool and the ability to do ELT or ETLTL means spinning up more pipelines and packages

It still has compatibility issues in Visual Studio 2017

I am able to use the GIS tools to easily generate solutions without coding.

Built in tasks that speed up web developing.

SSIS enables automated data preparations.

Amazing integration with Microsoft tools.

Creation of ETL mapping.

It depends so much on the Microsoft environment and has integration issues with other formats like JSON and Excel.

Error messages are unclear, this makes us spend a lot of time figuring out what has gone wrong making the debugging process hectic.

Server Integration Services for SQL Server With the right planning of dataflows, we may aggregate information from many different sources.

The solution's package structure allows us to isolate individual functions while reusing the same set of settings everywhere else.

The capacity to test solutions in a variety of settings with varying inputs is a potent resource that facilitates the development of solutions that can be reused.

Parameter distribution into a user-selected environment has the potential to greatly enhance the current deployment procedure. It's inefficient and easy to make mistakes when you have to do everything by hand.

This is useful when a larger package is being used as a container for a set of dependent packages, and the larger package is being used to run and report on the smaller packages.

Data transformation is laborious when dealing with flat files.

Standard ETL use cases for daily loads

Loading incoming data from Vendors which is placed on FTP and adding them to the SQL Warehouse

Creating outgoing data files and writing them to Vendor FTPs

Easy Active Directory integration for seamless connections to SQL Server

CI/CD by hosting the code on visualstudio.com

API connections are not a native functionality. We use Zappysys extensions but they work only in certain cases.

Dependency executions - no simple way to create a hierarchy/chain of executions. Ex: Define if the execution of the child process should be stopped if a parent fails or a certain condition is triggered in the parent process and then redirect to a different part of the chain (think like a flowchart for executions).

Assists in connecting to several data sources and loading the resulting data.

Less expensive than other ETL tools of same stature.

Does away with the requirement for coding scripts.

There isn't a lot of room for building user-required automation.

Some activities need manual labour, such as exporting multiple tables in CSV format one at a time.

Data Mapping & Integration

Maintaining data sources as per needs

Keeping track of all activities pertaining to data sources

Speed at which data is retrieved from the data sources

Ease of connecting to various data sources

Integration with other cloud services

Encrypt files with SSIS and send them to various network locations this way we solve complex business problems.

We can migrate DTS packages to SSIS while choosing to run DTS packages using DTS runtime or incorporate DTS packages into SSIS this way we migrate DTS packages to SQL Server Integration Services.

We can transform data to make sure it complies with the rules of the database they are migrating to other servers with Integrations Services.

User-required automation needs much more scope.

Exporting numerous tables in CSV format has to be done one by one by manual.

Create Packages in SQL Server Data Tools

Reuse Control Flow across Packages by Using Control Flow Package Parts

Build Integration Services User Interface

We can improve Integration Services error messages, including a list of most Integration Services errors and their description.

Improvement to create custom event handlers for these events to extend package functionality.

Better support for tools and wizards helping with Legacy Package Deployment.

Integration with multiple data sources

Creating Schemas

Data modeling

Data cleaning

Ease of use for initial get into application

Application Loading

It's user friendly and Microsoft provides good technical support as well.

Since its from Microsoft, The Integration of other Microsoft tools are quite easy.

Can get the data from any sources and dump/land anywhere you wish as you need.

The data source other than Microsoft-based applications could be improved to a better extent.

Sometimes we need other tools for Data conversions, which could be in-build or capabilities that needs an improvement.

The user interface can be better as sometime it lags with error.

Data transfer

Automation

Programability

Scheduling

Clarity on errors

Support

Runs flawlessly after setup properly

Powerful, as mentioned

Free

Confusing operation, even with its gui

Requires special install and setup

Requires the Visual studio integration services install (separate interface from SSMS)

Could be more easily integrated into SSMS, but may also be used for other products that I'm not aware of

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.

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

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.

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.

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

Process flow.

Connection to a wide array of sources.

Built-in upsert component.

Better operability with source control systems.

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.

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.