SSIS Expression Editor & Tester

by Darren Green 9 Jun 2010 14:41
Published today on CodePlex is the SSIS Expression Editor & Tester project. If you want to try it just pop over to CodePlex and download it. About five years ago I developed my own expression editor control. It first got used in our custom tasks as the MS editor didn’t become available until SQL 2005 SP1, but even then it had some handy features I preferred. For example resizable panes so that if your expression result was more than two lines you could see them all. It also meant I could change the functions available in the tree view, the most obvious use being to add some handy snippets... [More]

Sorting data in the SSIS Pipeline (Video)

by Allan Mitchell 29 May 2010 20:27
In this post I want to show a couple of ways to order the data that comes into the pipeline.  a number of people have asked me about this primarily because there are a number of ways to do it but also because some components in the pipeline take sorted inputs.  One of the methods I show is visually easy to understand and the other is less visual but potentially more performant.

StreamInsight Licensing

by Allan Mitchell 28 May 2010 06:22
I cannot help but think that this is currently wrong.  There are two editions of StreamInsight (SI), Premium and Standard.  A link to their differences is here Points to note Developer Edition and Evaluation Edition represent Enterprise Edition in SQL Server but not SI where they represent Datacentre Edition.  Won’t this be confusing when people evaluating SI find that it is not the same animal when they move to Enterprise Edition? If SI Premium is the only thing you want out of the SQL Server box then it is going to be a high cost. The latency rates as well quot... [More]

Explaining the State Transitions Viewer in Sequence Clustering

by Allan Mitchell 27 May 2010 19:29
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

MSDN Article for the GetClusterCharacteristics Stored Procedure

by Allan Mitchell 27 May 2010 19:26
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

Optimising Lookups with the Cache Transform (Video)

by Allan Mitchell 14 May 2010 15:40
How to optimise doing the same lookup multiple times. [More]

The Pipeline Limiter in SQL Server Integration Services (Video)

by Allan Mitchell 14 May 2010 11:51
Not many people understand that the Integration Services pipeline can be intentionally throttled back under the covers to stop you from running out of memory.  In this video we show you how to detect this is happening and explain more about the limiter itself. More...

Data Profiling without SSIS

by Darren Green 12 May 2010 14:45
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]

Have you used the ExecutionValue and ExecValueVariable properties?

by Darren Green 4 May 2010 08:36
The ExecutionValue execution value property and it’s friend ExecValueVariable are a much undervalued feature of SSIS, and many people I talk to are not even aware of their existence, so I thought I’d try and raise their profile a bit. The ExecutionValue property is defined on the base object Task, so all tasks have it available, but it is up to the task developer to do something useful with it. The basic idea behind it is that it allows the task to return something useful and interesting about what it has performed, in addition to the standard success or failure result. The best example perh... [More]

Design Patterns for SSIS Performance (Presentation)

by Darren Green 21 Apr 2010 18:53
Here are the slides from my session (Design patterns for SSIS Performance) presented at SQLBits VI in London last Friday. Slides - Design Patterns for SSIS Performance - Darren (86KB) It was an interesting session, with some very kind feedback, especially considering I woke up on Friday without a voice. The remnants of a near fatal case of man flu rather than any over indulgence the night before I assure you. With much coughing, I did turn the off the radio mic during the worst, and an interesting vocal range, we got through it and it seemed to be well received. Thanks to ... [More]

Hosting woes

by Darren Green 21 Apr 2010 18:39
Unfortunately quite a few people have noticed our recent hosting problems, but if you are reading this they should all be over, so please accept our apologies. Our former web host decided migrate to a new platform, it had all sorts or great features, but on reflection hosting wasn’t one of them. We knew it was coming, and had even been proactive and requested several dates on their migration control panel so I could be around to check it afterwards. The dates came and went without anything happening, so we sat back and carried on on for a couple of months thinking they’d get back to us when t... [More]

User Group Meeting in Dundee (Scotland)

by Allan Mitchell 5 Jan 2010 18:34
Tony Rogerson and myself are going to be in Dundee (we are going back to school) week beginning 18.1.2010.  Whilst there we are going to do a User Group meeting and it would be great to see people there.  More details can be found here -

Script Task/Component and Template Information

by Allan Mitchell 8 Dec 2009 06:46
The Script Task and Script component are often used by people developing SSIS packages because they are easy to use and now because SSIS could be perceived to be more developer friendly they are very powerful. That being said we should no be using them everywhere.  There are generally Tasks/Components already provided that will do the job it may be that we have to rethink the way we want to draw our package. I had cause last week to break out the script component in SQL Server 2008 SP1 and found that it was broken.  I don’t know when it broke as I do not use them all that often.... [More]

Change Data Capture Webinar

by Allan Mitchell 12 Sep 2009 07:37
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: 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]

Swiss SQL Server Saturday, Zurich, September 19th

by Allan Mitchell 21 Aug 2009 13:34
I am going to be speaking at the first ever SQL Server Saturday in Switzerland this autumn.  This event is taking place on Saturday 19th September in Zurich.  If you want to know more about it or are thinking of coming then head over to Charley has lined up a top list of speakers for this event and I know it is going to be a fun day.

Manchester UG Presentation Video

by Allan Mitchell 11 Aug 2009 16:01
In July I was invited to speak at the UK SQL Server UG event in Manchester.  I spoke about Excel being a good data mining client.  I was a little rushed at the end as Chris Testa-ONeill told me I had only 5 minutes to go when I had only been talking for 10 minutes.  Apparently I have a reputation for running over my time allocation.  At the event we also had a product demo from SQL Sentry around their BI monitoring dashboard solution.  This includes SSIS but the main thrust was SSAS Then came Chris with a look at Analysis Services.  If you ... [More]

Donald Farmer comes to SQLBits

by Allan Mitchell 11 Aug 2009 13:36
What do medieval archaeology, fish farming, Southwestern University of Chongqing and Microsoft Business Intelligence have in common? If you know, you should tell Donald Farmer, because he has been deeply involved in all of them at various times. Donald has worked in the Microsoft Business Intelligence team for 8 years covering many subject areas: data integration, information quality, metadata intelligence, master data management, OLAP, predictive analytics and self-service BI. He is a well-known speaker at Microsoft and other industry events, and the author of several books and articles. ... [More]

Where is my app.config for SSIS?

by Darren Green 3 Aug 2009 09:20
Sometimes when working with SSIS you need to add or change settings in the .NET application configuration file, which can be a bit confusing when you are building a SSIS package not an application. First of all lets review a couple of examples where you may need to do this. You are using referencing an assembly in a Script Task that uses Enterprise Library (aka EntLib), so you need to add the relevant configuration sections and settings, perhaps for the logging application block. You are using using Enterprise Library in a custom task or component, and again you need to add the relevant... [More]

Exploring packages in code

by Darren Green 17 Jul 2009 16:07
In my previous post Searching for tasks with code you can see how to explore the control flow side of packages, drilling down through containers, task, and event handlers, but it didn’t cover the data flow. I recently saw a post on the MSDN forum asking how to edit an existing package programmatically, and the sticking point was how to find the the data flow and the components inside. This post builds on some of the previous code and shows how you can explore all objects inside a package. I took the sample Task Search application I’d written previously, and came up with a totally pointless l... [More]

New SSIS tool on Codeplex – SSIS Log Analyzer

by Darren Green 10 Jul 2009 12:19
I stumbled across a new SSIS tool on Codeplex today, the SSIS Log Analyzer which was only released a few days ago. Whilst it is a beta release and currently only supports 2005 (2008 is promised) it looks quite interesting. It seems to be a fancy log viewer, but with some clever features and a nice looking front-end. I’ve only read the documentation so far, but it has graphs and a debug view that shows your package with the colour animations similar to when debugging in BIDS, and everyone knows, the way the pretty colours and numbers change is the best bit! I’ll quote some of the features for ... [More]


Comment RSS