Error: 0xC001405C at SQL Log Status: A deadlock was detected while trying to lock variables "User::RowCount" for read/write access. A lock cannot be acquired after 16 attempts. The locks timed out. Have you ever considered variable locking when building your SSIS packages? I expect many people haven’t just because most of the time you never see an error like the one above. I’ll try and explain a few key concepts about variable locking and hopefully you never will see that error. First of all, what is all this variable locking all about? Put simply SSIS variables have to be l... [More]

Her is an article I wrote for MSDN that helps explains the excellent viewer we get for Sequence Clustering models in SQL Server Data Mining.  I show you how the numbers you see are derived and also give an explanation as to what the icons you see in the viewer mean. Link to the article

Here is an article I wrote for MSDN that introduces us to the GetClusterCharacteristics stored procedure in SQL Server Data Mining.  It gives us an insight into how the sequences within clusters are derived when using the Sequence Clustering algorithm. Link to article

Strangely enough for a predominantly SSIS blog, this post is all about how to perform data profiling without using SSIS. Whilst the Data Profiling Task is a worthy addition, there are a couple of limitations I’ve encountered of late. The first is that it requires SQL Server 2008, and not everyone is there yet. The second is that it can only target SQL Server 2000 and above. What about older systems, which are the ones that we probably need to investigate the most, or other vendor databases such as Oracle? With these limitations in mind I did some searching to find a quick and easy alter... [More]

I am going to be doing a webinar with our friends at Attunity on Change Data Capture.  Attunity have a good story around this technology and you can use it in your SSIS loads to great effect. Join Attunity and Konesans/SQLIS for a Webinar on 17 September Space is limited. Reserve your Webinar seat now at: https://www1.gotomeeting.com/register/693735512 Want increased efficiency and real-time speed when conducting ETL loads? Need lower implementation costs while minimizing system impact? Learn how change data capture (CDC) technologies can reduce ETL lo... [More]

If you use the MSDB package store in SSIS 2005, you may have noticed that the folders are not sorted in any friendly way, it is actually just the order they were added. You end up with something like the left-hand example below. The right-hand example shows the same SSIS instance after my suggested update, and even though I only have a few folders and packages just to illustrate the point, it looks much better and easier to use. Before     After     By tweaking... [More]

Here is a Live Meeting presentation I did for the Polish SQL Server User Group on Tips and Tricks I use when out and about using SSIS (It is in English).  It lasts about an hour and you can either watch it online or download the wmv to view later   Tips and Tricks in SSIS

When we are building ETL projects we need to do some planning up front to make sure we have all the things we need to make it a success. Here are some of the things we consider when embarking on an ETL Project. There are two lists. The first is around an ETL Project in general and the second is around things we think about specifically for SSIS. Review target data model Identify source systems (Owners, RDBMS types, Permissions) Analyze and profile source systems (usge patterns, Windows of opportunity) Document source data defects and anomalies (known issues, data profiling) ... [More]

You can create package folders through SSMS, but you may also wish to do this as part of a deployment process or installation. In this case you will want programmatic method for managing folders, so how can this be done? The short answer is, go and look at the table msdb.dbo. sysdtspackagefolders90. This where folder information is stored, using a simple parent and child hierarchy format. To add new folder directly we just insert into the table - INSERT INTO dbo.sysdtspackagefolders90 ( folderid ,parentfolderid ,foldername) VALUES ( NEWID() ... [More]

The DTS component of SQL Server 2000 was a redistributable client component. This mean anyone with a SQL Server Client Access Licence (CAL) could not only connect to SQL Server in the normal manner, but that could also run DTS packages on their machine. Since DTS and SSIS have no client server, or remote execution feature, this was rather useful. The DTS install was officially documented in redist.txt and expanded upon in articles like Redistributing DTS with your program . SSIS is not redistributable. If you want to run a SSIS package on a machine, you need to licence that machine with a ser... [More]

The SQL Server 2005 and SQL Server 2008 product family has quite a few editions now, so what does this mean for SQL Server Integration Services? Starting from the bottom we have the free edition known as Express, and the entry level Workgroup edition, as well as the new Web edition. None of these three include the full SSIS product, but they do all include the SQL Server Import and Export Wizard, with access to basic data sources but nothing more, so for simple loading and extraction of data this should suffice. You will not be able to build packages though, this is just a one shot deal... [More]

We see this coming up quite a bit on newsgroups and even have people eMailing about it so we though we would put up an article explaining where to find packages when saved to SQL Server. If you want to view them visually in SQL Server Management Studio then you will need to connect to the Integration Services Service for the Server on which you have stored the packages. Below is an example of us connecting to the service by first opening up Object Explorer and then connecting to the Service by choosing it from the dropdown menu and then using Integrated security (no choice here) ... [More]