What needs improvement with SSIS?

The product's stability needs improvement. The high prices attached to the product can be an area of concern where improvements are required.

Improvement as per customer requirements.

In upgrading SSIS, we encountered challenges fixing SQL Server and performance issues, including problems during a failover in our data warehouse. After resolving these issues and conducting load balancing tests, we proceeded with the SSIS upgrade, anticipating potential complications.

There are a lot of connectors that are not available right now. Some third-party connectors and adapters are available, but not directly from Microsoft data, which is an issue. And in terms of maturity, it is a good product. But, again, the way the industry uses these ETL tools, SSIS is not preferred because of many factors. Microsoft is no longer promoting the tool itself and is more focused on its cloud framework. That could be one of the reasons, but a smaller company prefers to SSIS because it is usually free with the SQL Server. There are a lot of things that Microsoft could improve in relation to SSIS. One major problem we faced was when attempting to move some Excel files to our SQL Server. The Excel provider has a limitation that prevents importing more than 255 columns from a particular Excel file to the database. This restriction posed a significant issue for us, and as a workaround, we had to convert the files to CSV format before importing them. While it involved some manual effort, it was not a big deal since it was a one-time task. These are some small areas of improvement. There's one tool, a very famous and popular tool among banking clients, that supports this kind of data load in SSIS. Moreover, in the case of a pivot, which is available in your Excel, there are a lot of ways an Excel file can be created, and you can have a favorite kind of data format in Excel. Importing data of that kind into SSIS can be problematic, and it cannot be done easily. It requires writing some sort of custom code. These basic issues need improvements. However, I don't think Microsoft would be willing to make any modifications to the existing capabilities provided by the tool. The reason is that there are already many third-party vendors offering similar functionality through their plugins. For example, you can install these plugins within your Visual Studio, and they provide an extra set of features integrated into Visual Studio and SSIS. Perhaps that's why Microsoft has stopped actively improving upon certain features. But, again, this introduces an overhead in terms of development cost. To access additional tools or features, I have to purchase them from another vendor and then implement them. Moreover, if something is not directly supported by Microsoft and relies on third-party tools, it can become a significant issue, as I'll have to depend on those vendors, which poses another challenge.

Sometimes, we have issues with SSIS connectors while extracting data from Excel sources. They should improve this particular area for excellent compatibility.

SSIS is cumbersome despite its drag-and-drop functionality. For example, let's say I have 50 tables with 30 columns. You need to set a data type for each column and table. That's around 1,500 objects. It gets unwieldy adding validation for every column. Previously, SSIS automatically detected the data type, but I think they removed this feature. It would automatically detect if it's an integer, primary key, or foreign key column. You had fewer problems building the model.

No big improvements are needed at this time. At one point, we did have to purchase an add-on. Since we are not using it for a whole ETL, or for a data warehouse, maybe I'm missing something.

I would like to see better technical documentation because many times information is missing.

There are a couple of areas that could be improved. For example, if I created a package and the package would work on specific data, it could work on the performance or speed of the action. If you are working on a very large amount of data, then creating this package and performing some transformations might take some time. Everything just needs to be faster. I haven't used SSIS for the last year, so I'm not sure about its new releases as of now. In terms of its performance, it could be better. That could be something that would be easy and welcomed as an upgrade. I'd like to see more dashboarding to be able to see the data through a graph. It would be great if I could also share the visualization with my clients.

SSIS sometimes hangs, and there are some problems with servers going down after they've been patched.

Connectivity to databases other than Microsoft. This is the Achilles' heel. Integration with other vendors' connectivity should be available as well as being able to read and write data to databases or data storage other than with Microsoft. There is connectivity with other databases, however, this is the most significant issue that has to be addressed.

We have faced some problems, mostly from a different provider with different language handling. We'd like the solution to increase the types of integrations on offer. We'd like more integration capabilities.

We'd like them to develop data exploration more. The support could be better.

The performance of SSIS could improve when comparing it to Oracle Database.

We would like the solution to be expanded so that it is available for other platforms than just Microsoft. We would also like to see this product improved so that the the tool can handle streaming data, and near real-time data capture.

In the next release, SSIS should include automated JSON file management.

Microsoft should offer an on-premises support warranty for those using that deployment. They seem to be withdrawing from on-premises options.

SSIS doesn't have a very good user interface, but if you can work with it, it'll provide you with almost all of the functionality.

It is an easy solution but needs some improvement. The creation of the measure in the DAC's model could be improved.

Sometimes, there are compatibility issues with some features. From time to time, I also face issues when trying to migrate. If I misconfigure things when I use Snapshot, the migration will fail.It can take a long time to migrate huge amounts of data, so it would be nice if that could be faster.

SSIS can improve by the minimum code requirements in stored procedures and exporting data is difficult. They could make it easier, it should be as easy as it is to import data.

I would like to see SSIS improve the collection of data trail servers and reporting. Future releases should improve the data lineage, as it currently is not good.

It would be nice if you could run SSIS on other environments besides Windows.

I haven't found too many successful scaling options and that's something that could be improved.

I would like to see more features in terms of the integration with Azure Data Factory.

It's difficult to refactor SSIS. It gets cumbersome to reuse the solution.

One of the problems I ran into was that the database that I had had a status. I couldn't always flip the status, so I didn't know what data I already had. I had to use SSIS to grab a bunch of data and check the database to see if I needed to insert it. It works okay, but as I plan better, I'm trying to weed it out. SSIS is a bit prickly because anytime you go and change something, it tends to break. That's because there is metadata behind the tool. I have a tool called ZappySys. I need that tool to cut down on the complexity of SSIS. That tool really helps with a quick turnaround. I can do things quickly, and I can do things accurately. I can get better reporting on errors.

We purchase an add-on called task factory primarily to allow bulk delete, update and upsert capability. I'd like to see this be part of the standard package. I believe there are ways to build a model and set variables so that it can be a generic process. In my next system, I would like to have a generic process that would handle all the logging and processing in a model that can be modified and enhanced as the need for a better process, or different statistics to be logged is discovered. I'd want this in a way that the model can be changed and all the processes, with their unique parameters, could all be changed with the model upgraded.

SSIS largest weaknesses right now are 1) Visual Studio/SSDT is only 32 bit. It needs to be 64 bit because that's the only thing that gets deployed and SSDT gets extremely unstable when your application gets large - likely because you start going over the max memory limitation. 2) it has changed very little in many years.  

Reality check: Microsoft has shifted focus to ADF. But ADF is not yet a valid replacement for SSIS (in fact not even close). ADF is far too painful to do just simple things that we can do with SSIS in our sleep. 

But that leaves us with the reality that SSIS will likely never get much attention. You can ask for improvements all you want but we are unlikely to ever see them. ADF is the future like it or not. Which is fine if you're starting from scratch but for those of us with thousands of SSIS packages we aren't converting anytime soon. Welcome to legacy software folks. I've had to abandon many great software packages over my career not because they were inferior but because the vendor simply abandoned them. One aspect of IT that I've never gotten used to.

So if you're starting out with a new project, I would seriously recommend testing your requirements against ADF and only using SSIS if ADF cannot meet your needs. And if you do need to use SSIS do it with Azure-SSIS IR or Execute SSIS Package activity.

The security could be improved, as it is more important in our context.

Sometimes we need to connect to AWS to get additional data sources, so we have to install some external LAN and not a regular RDBMS. We need external tools to connect. It would be great if SSIS included these tools. I'd also like some additional features for row indexing and data conversion.

It is an out-of-date product, that we shouldn't be using anymore, as it no longer has many positive aspects. It is an old tool. We make an effort not to use it. It's a legacy tool, that is nearing the end of its useful life. There could be some good things but we try not to get into that. We have selected to go with another tool. SSIS should not be used whatsoever anymore. There are new versions, that are cloud-based, which are much more improved and much more feature complete.

The solution could improve by having quicker release updates.

When I compare Talend and SSIS, Talend provides more features. With Talend, we can handle a large volume of data. Talend is usually used to treat a large volume of data, which makes it better than SSIS on the data side. Talend also has a very good Talend Management Console to schedule the jobs and do other things. It can also be easily connected to version control tools such as GitHub or SVN. The last time I used SSIS, it was connected through TSS for the Windows Console version. I am not sure it has been improved or not. If it is not improved, Microsoft should improve it. They should change the product to provide another console.

It runs a little bit slowly, although this may be because of our laptops. I would like to see better integration with Power BI.

The solution could improve on integrating with other types of data sources. We had issues with connecting to Oracle, it did not do as good of a job as it did with SQL servers. In my experience, more efficiency is needed when it comes to dealing with huge volumes of data. However, this is also dependant on the server capacity. In an upcoming release, they should update the features to facilitate efficient data transfers.

It should have other programming languages supported as well from a scripting perspective. Currently, only C# and VB.NET are supported, which limits it to .NET. It should have Java support as well. We couldn't explore big data related components, and this area should be up to the mark, if not already. Certain data quality checks should also be part of it. Currently, this is the biggest area that should be looked into. Data quality is an essential part of the integration process and ETL. Currently, only the data profiling task is there.

This may be done, but upgrades to new versions required re-implementing each package. This is worse for us because we have about one thousand SSIS packages. Hopefully they are now allowing backwards compatibility. Generic processes should be used instead of custom code for each table.

Tuning using this solution requires extensive expertise to improve performance.

I would like to see an auto-scheduler feature in the Developer version of this solution. I would also like to see full integration with our BI because then our full load of data will be available in our organization. They should incorporate an ATL process.

The solution should work on the GPU, graphical processing unit. There should also be a piping integration available. The design of the dashboard isn't great, visually, but the solution works, so this isn't so important to me. SSIS's competitor, Autonomous Database by Oracle, offers you, depending on the performance of the server, new hardware, should you update your software. It advises you on how to optimize your system. It's AI and works as an artificial database manager. Microsoft doesn't have anything like this yet, but it would be nice if they did.

It's at a very early stage in terms of adopting the tool, so I can't speak to if there are areas for improvement just yet. Video training would be a helpful addition, however.

The solution should offer more information online for users of SSIS. There are only a few general data sources available, but it would be helpful if there was more extensive information. The solution could also use some more intelligent discs for data manipulation, or maybe some artificial intelligence as well. It needs more integration tools, so you can connect to different sources.

My team is facing problems regarding the database connectors, which are not available. The MySQL connectors need to be purchased from outside vendors. They should provide connections for more SQL databases, free of charge. The performance of this solution is not as good as other tools in the market. Compared to the same job is running in a different tool, it will take longer using SSIS.

There are a lot of features that could make this solution better. There were some issues when we tried to connect it to data storage. It was a connection issue. There is also room for improvement in the underlying language.

More coding is needed to make it easier. It needs more development and enhancement. It also needs to offer different integration services for SQL. We had to do a lot fo the implementation ourselves, but more automating of the processes would make things easier. Data migration needs to be simplified. I've found that it is really difficult to match, especially regarding data types. The old records often have different data types than we have now have in newer databases. Converting or transferring this kind of data is very important, and right now it's very difficult. There were also not enough instructions from Microsoft in regard to this application or this technology, which can easily be improved upon.

The interface could use improvement, as well as the administrative tools. Jobs fail from time to time for different reasons. It's not a problem with Microsoft, or SSIS itself. The problems are external, but to find the problems and analyze them it takes too much time. If SSIS could make some new monitors or new features for finding the reasons for the problems in the processes and analyzing it for how to correct the issues, that would be great.

The login process needs improvement. At the moment, when you run SSIS and it's logging on SysTD within the server itself, I think that you have the option to do verbose and a couple of others. Sometimes, it is difficult to follow how executions are actually working. They are just one after another with an execution set. If you're running multiple packages at the same time, it's difficult to track which packages are running and to locate those specifically. Improving the login procedure would make our reporting easier on monitoring our ETL processes.

The synchronous processing needs to be improved. For batch processing, it works fine, but when you start to do real-time processing, I find that this solution is not strong, depending on how you use it. If you use it for short batches, micro batching, that might work, but it is not as good for queuing real-time processing. This solution needs full support for real-time processing. The solution needs better support for XML and JSON.

We're in the process of switching to Informatica, and we need to work out data lineage and data profiling and to improve the quality of our data. SSIS, however, is not that compatible with Informatica. We managed to connect it to Informatica Metadata Manager, but we don't get good lineage, so we have to redo all our ETLs using the Informatica process in order to accept the proper data lineage. As an example of what I'd like to see in SSIS is something that I think is very effective in Informatica. In Informatica PowerCenter, you define your sources and destinations once and after that, you can simply drag and drop into the designer. It's quite a nice feature. I don't know if SSIS has that feature, but if they don't, if they could allow the setup system configurations in one step and then offer drag and drop functionality, that would be extremely useful.

In terms of improvements to the solution, I don't think I have that expertise on it to recommend product enhancements right now. I've only used a few parts of it. Based on what I've used so far, I am happy with it. I come from a coding background and this tool is graphically based. Sometimes I think it's cumbersome to do mapping graphically. If there was a way to provide a simple script, it would be helpful and make it easier to use.

I have had problems in the past with this tool. The debugging could be improved because when it came to solving the errors that I've experienced in the past, I've had to look at the documentation for more information. The main difficulty that I have had is to match different data types, where there were errors that I could not fix. I would like to see better integration with the control sources.

Sometimes when we want to publish to other types of databases it's not easy to publish to those databases. For example, the Jet Database Engine. Before the SSIS supported Jet Database Engine but nowadays it doesn't support the Jet Database Engine. We connect to many databases such as Access database, SparkPros databases and the other types of databases using Jet Database Engines and SSIS now doesn't seem to support it in our databases. In future releases, it would be helpful to support new SQL databases. It would also be helpful if there was a way to use SQL with other languages like Python or ASP.NET.

The UI could use refinement. I would like to see more standard components out of the box, such as SFTP, and data compression components.

Metadata management. A change in the metadata source cripples the whole ETL process, requiring each module to be manually reopened.

Scalability of SSIS needs some improvement. Seems to get sluggish as soon as we hit a high volume of data.