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 →

Until now, I have found working with Kerberos when setting up a SQL Server stack to be a complete nightmarish experience, mainly due to two reasons:

• Working with Kerberos usually requires access rights to Active Directory for the account setting up this authentication protocol on the stack, in order to be able to effectively diagnose the setup and also configure the Service Principal Names (SPN) for the various SQL Server and SharePoint service accounts, and setup delegation. This means SQL Server architects and Network Administrators need to collaborate in order to correctly configure the stack, which is often an unpleasant and long winded experience of trial and error.
• The lack of a centralized diagnostic and configuration tools for Kerberos setup on SQL Server makes this tasks very tedious, particularly if you follow the limited number of online resources out there to setup Kerberos, and find that they do not apply exactly to your situation, or do not work exactly as intended after following the lengthy steps, and you are left with a very limited option in terms of diagnosing exactly what went wrong.

Read more →

On one corner we have Hadoop, a massively distributed JVM-based data processing engine with a Map & Reduce API and a proven track record in handling huge data-sets. On the other corner we have SSIS, a natively non-distributed ETL engine part of the SQL Server family tool-set with .NET code extensibility features and a drag and drop UI (for the most part anyway). Two sweet technologies, probably shouldn’t be compared to each other but we’re doing it anyway, pitted head to head against a data mapping task to the death (or at least to the recycling of my test VMs)… Now FIGHT!

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 →

Recently I’ve been getting the following error message while trying to create an SSIS 2012 step in SQL Server Job Agent, and setting the Parameters or Connection Strings for that step:

The property ‘ParameterName’ contains invalid characters as an object name. Remove the invalid characters. (Microsoft.SqlServer.Management.IntegrationServices)

This error emerges after confirming the newly created step (which will run the SSIS package) in SQL Server Job Agent, and after setting the parameter.

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 →

Although the new SSIS 2012 Project Deployment Model is pretty cool, it is a completely new deployment architecture that is bound exhibit some bugs. To be honest SSIS 2012 in general seems to be less geared up towards enterprise level scale-out, and will work better in a non-24/7 load environment.

The error I am experiencing with SSIS 2012 is to do with deploying a project (from SQL Server Data Tools) to an SSIS Catalog DB, whenever a project is deployed, there seems to be a 50/50 chance that I will receive the following deployment error:

Failed to deploy project. For more information, query the operation_messages view for the operation identifier ’123456′. (Microsoft SQL Server, Error: 27203)

Read more →

If you have used Microsoft development products long enough, then you must be familiar with the error reporting nightmares some of their products exhibit, the classic “please check the error log for more details” message, even though you are in the error log, is not just frustrating, but in my opinion is a bug that shouldn’t even reach end consumer, especially when you are handing the developers a framework for them to build on and extend.

Microsoft’s SSIS and SSRS have always been shortchanged when it comes to error logging; whether they are run-time errors on the server, or even while trying to debug a package or a report in a development environment, there is no lack of confusing error messages that send you down hours of so many long winded paths that leaves you tugging on strings hoping something might eventually make sense.

Today I will go through one of SSIS 2012 new quirks, the SSIS Canceled Status (or error), and what could be the reasons that you might receive such an error.

Read more →

Without a doubt one of the most annoying and least useful errors I have ever encountered during my experience with SSIS.

This error has crept up on an SSIS packages that processes a dimension. The SSIS package is very simple and all it has is a OLE DB Source that dumps data from a query into a Dimension Processing task. The package executes fine until it reaches a particular row in the OLE DB source batch, in which it will fail with the following error message:

Dimension Processing Error: XML parsing failed at line 4, column 3391: Illegal xml character.

Descriptive!

Read more →

It has been a while since I released a post, this has mostly been due to a recent involvement in some very time consuming scale-out architecture, there is nothing like building a whole SQL Server architecture from scratch, something very powerful and enterprise-y.

Anyhow the purpose of this post is to discuss one of the many issuesam finding with the new SSIS 2012. This error relates to error logging when having a Script Component task within a Data Flow task that is being used as a data “Source”, particularly when this Script Component is followed by a Lookup task, or any task that works on results In-Stream, as shown in the image above.

Read more →