The title should actually read “8 Random Ways to Optimise SSIS”.

One of the recent project I have been working on involved building a distributed (scaled-out) SSIS environment, this means multiple VMs with a standalone SSIS (2012 in Package mode) instances installed (so no SQL Server Database Engine), all pushing massive amount of data to a staging database.

I have been brought in on this project to suggest a few techniques to improve the performance of the scaled-out SSIS environment, by basically increasing the throughput to the staging database, below I discuss some of the general approaches I have taken to achieve that goal. Some of the advice might be a bit random, and others might not be pertinent to your particular situation, but over-all you should find a gem or two on optimising SSIS  performance in there somewhere!

Read more →

The SSIS Catalog in SQL Server 2012 comes with the benefit of logging natively integrated within the service’s architecture, which is awesome considering before SSIS 2012 BI developers and admins had to go for 3rd party tools in order to bring that level of logging into their SSIS packages.

That being said, with a lot of log data comes the responsibility of maintaining and clearing out old log entries and execution messages, which are stored on the SSIS Catalog Database and drive the SSRS reports that are used to diagnose the execution of packages, as well as provide performance statistics. The currently provided SSIS Server Maintenance Job in SQL Server Job Agent that performs these routine maintenance operation can be very slow and extremely blocking, especially on an SSIS server that sees alot of operations and activities, and cannot afford the downtime caused by the heavy locking experienced by this operation.

In this post I provide an introduction to the [internal].[cleanup_server_retention_window] stored procedure executed by the SSIS Server Operation Records Maintenance step in the SSIS Server Maintenance Job, as well as provide an extremely fast truncation query to completely clear out the SSIS log tables. In a  follow-up post I will be providing a rewrite of the [internal].[cleanup_server_retention_window] stored procedure that performs much better on SSIS services with a large number of logged operations per day.

Read more →

The SSIS 2012 Catalog integration with SQL Server comes with the advantage of being able to execute SSIS packages indigenously from within T-SQL, along with a host of other functionality including improved monitoring and logging, integrated security and obviously the new deployment model.

Executing SSIS packages by calling a stored procedure can be handy in many situations, but also comes with some (albeit minor) stipulations and quirks, this post tries to go through some of the basics of executing SSIS packages through stored procedures, while also touching lightly on some of the issues I encountered while having a go at this new SSIS execution method.

Read more →