RegexClean Transformation

by Darren Green 25 Jul 2017 16:03
Use the power of regular expressions to cleanse your data right there inside the Data Flow. This transformation includes a full user interface for simple configuration, as well as advanced features such as error output configuration. Two regular expressions are used, a match expression and a replace expression. The transformation is designed around the named capture groups or match groups, and even supports multiple expressions. This allows for rich and complex expressions to be built, all through an easy to reuse transformation where a bespoke Script Component was previously the only alterna... [More]

Regular Expression Transformation

by Darren Green 25 Jul 2017 16:01
The regular expression transformation exposes the power of regular expression matching within the pipeline. One or more columns can be selected, and for each column an individual expression can be applied. The way multiple columns are handled can be set on the options page. The AND option means all columns must match, whilst the OR option means only one column has to match. If rows pass their tests then rows are passed down the successful match output. Rows that fail are directed down the alternate output. This transformation is ideal for validating data through the use of regular expressions... [More]

Row Count Plus Transformation

by Darren Green 25 Jul 2017 16:00
As the name suggests we have taken the current Row Count Transform that is provided by Microsoft in the Integration Services toolbox and we have recreated the functionality and extended upon it. There are two things about the current version that we thought could do with cleaning up Lack of a custom UI You have to type the variable name yourself In the Row Count Plus Transformation we solve these issues for you. Another thing we thought was missing is the ability to calculate the time taken between components in the pipeline. An example usage would be that you want to know how many row... [More]

Trash Destination Adapter

by Darren Green 25 Jul 2017 15:41
The Trash Destination and this article came from early experiences of using SSIS and community feedback at the time. When developing a package it is very useful to have a destination adapter that does nothing but consume rows with no setup requirement. You often want run a package part way through development, or just add a path so you can set a Data Viewer. There are stock tasks that can be used, but with the Trash Destination all columns are treated as selected automatically (usage type of read-only), so the pipeline knows they are required. It is also obvious that this is for development or... [More]

Row Number Transformation

by Darren Green 25 Jul 2017 00:00
The Row Number Transformation calculates a row number for each row, and adds this as a new output column to the data flow. The column number is a sequential number, based on a seed value. Each row receives the next number in the sequence, based on the defined increment value. The final row number can be stored in a variable for later analysis, and can be used as part of a process to validate the integrity of the data movement. The Row Number transform has a variety of uses, such as generating surrogate keys, or as the basis for a data partitioning scheme when combined with the Conditional Sp... [More]

SSMS copy and paste from results grid losing CR+LF - SQL Server 2016

by Darren Green 6 Jun 2016 22:37
When you start using SQL Server Management Studio (SSMS) for SQL Server 2016, you may notice a new bug feature to do with the Results Grid. If you copy and paste one or more cells from the results grid the copied text loses any carriage returns (CR) and line feeds (LF). To avoid too much head scratching, just remember there is a new setting available. You can access this via Options dialog from the Tools menu (Tools –> Options). Navigate to the Query Results –> SQL Server –> Results to Grid section as shown below. The offending item is the Retain CR/LF on copy or save, which you proba... [More]

File Watcher Task

by Darren Green 6 Jun 2016 16:07
The task will detect changes to existing files as well as new files, both actions will cause the file to be found when available. A file is available when the task can open it exclusively. This is important for files that take a long time to be written, such as large files, or those that are just written slowly or delivered via a slow network link. It can also be set to look for existing files first (1.2.4.55). The full path of the found file is returned in up to three ways: The ExecValueVariable of the task. This can be set to any String variable. The OutputVariableName when specified. T... [More]

Checksum Transformation

by Darren Green 6 Jun 2016 00:00
The Checksum Transformation computes a hash value, the checksum, across one or more columns, returning the result in the Checksum output column. The transformation provides functionality similar to the T-SQL CHECKSUM function, but is encapsulated within SQL Server Integration Services, for use within the pipeline without code or a SQL Server connection. As featured in The Microsoft Data Warehouse Toolkit by Joy Mundy and Warren Thornthwaite from the Kimbal Group. Have a look at the book samples especially Sample package for custom SCD handling. All input columns are passed through the tr... [More]

0xC0017011 and other error messages - what is the error message text?

by Darren Green 9 Nov 2012 19:37
Recently there was a bug raised against BIDS Helper which originated in my Expression Editor control. Thankfully the person that raised it kindly included a screenshot, so I had the error code (HRESULT 0xC0017011) and a stack trace that pointed the finger firmly at my control, but no error message text. The code itself looked fine so I searched on the error code but got no results. I’d expected to get a hit from Books Online with the Integration Services Error and Message Reference topic at the very least, but no joy. There is however a more accurate and definitive reference, namely the heade... [More]

Upgrading SSIS Custom Components for SQL Server 2012

by Darren Green 12 Jun 2012 05:59
Having finally got around to upgrading my custom components to SQL Server 2012, I thought I’d share some notes on the process. One of the goals was minimal duplication, so the same code files are used to build the 2008 and 2012 components, I just have a separate project file. The high level steps are listed below, followed by some more details. Create a 2012 copy of the project file Upgrade project, just open the new project file is VS2010 Change target framework to .NET 4.0 Set conditional compilation symbol for DENALI Change any conditional code, including assembly versio... [More]

Data Generator Source Adapter

by Darren Green 5 Jun 2012 14:19
This component needs little explanation. It generates random integer (DT_I4) and string (DT_WSTR) data and places them in the pipeline. You specify how many columns of each you would like and for any string columns you pass a fixed length value. You then need to specify how many rows in total you require to be generated. This component is used by us to do testing of the pipeline and components downstream. Previously we would have used a script component (as a source) to generate the rows but found ourselves rewriting the code too often so created this component. Screenshots ... [More]

Red Gate join the SSIS custom component club

by Darren Green 11 Jul 2011 08:11
I recently noticed that Red Gate have launched themselves into the SSIS component market by releasing a new Data Cleanser component, albeit in beta for now. It seems to be quite a simple component, bringing together several features that you can find elsewhere, but with a suitable level  polish that you’d expect from them. String operations include find and replace with regular expressions, case formatting and trim, all of which are available today in one form or another, but will the RedGate factor appeal to people? Benefits include ease of use, all operations in one place, versus ... [More]

SQLBits 9 goes to Liverpool

by Darren Green 21 Jun 2011 22:12
SQLBits 9 has now been announced as 29th September to 1st October 2011 at the Adelphi Hotel in Liverpool. This will follow the now familiar three day format, a training day, and two full days of concurrent sessions. Saturday 1st October will of course be the free community day. Despite growing the event quite dramatically over the past four years, this is something we are all very proud to have maintained and is a key factor when planning the events. Plenty of more info to come, but in the meantime session submission is now open so why not submit an abstract?

Using the Script Component as a Conditional Split

by Darren Green 12 Nov 2010 13:46
This is a quick walk through on how you can use the Script Component to perform Conditional Split like behaviour, splitting your data across multiple outputs. We will use C# code to decide what does flows to which output, rather than the expression syntax of the Conditional Split transformation. Start by setting up the source. For my example the source is a list of SQL objects from sys.objects, just a quick way to get some data: SELECT type, name FROM sys.objects type name S syssoftobjrefs F FK_Message_Pa... [More]

SSIS Field Notes – SQLBits 7 Presentation

by Darren Green 20 Oct 2010 22:35
Here are the slides from my session SSIS Field Notes presented at SQLBits 7 in York earlier this month - SSIS Field Notes – Darren Green.pptx On a similar theme, the video of my session Design patterns for SSIS Performance from is now available. You heard it here first! I know that this because I’ve only just finished updating the SQLBits site with all the videos from SQLBits 6. Hopefully we’ll get them released quicker for SQLBits 7.

SQL Bits 7 - 30th September - 2nd October 2010 in York

by Darren Green 12 Aug 2010 15:07
In case you haven't heard we are planning the next SQL Bits event, and today we have released the agenda for Friday & Saturday, a total of 50 sessions covering all aspects of SQL Server with a great selection of speakers. http://www.sqlbits.com/information/Agenda.aspx From our recent announcement - ...SQLBits 7 will take place over three days from Thursday September 30th to Saturday October 2nd in York. Day one will be a training day, featuring in-depth full day seminars by leading SQL Server professionals such as Chris Testa-O’Neill and Chris Webb (see http://www.sqlbits.co... [More]

A deadlock was detected while trying to lock variables in SSIS

by Darren Green 10 Aug 2010 22:19
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]

Creating packages in code - Workflow

by Darren Green 11 Jun 2010 18:08
This is just a quick one prompted by a question on the SSIS Forum, how to programmatically add a precedence constraint (aka workflow) between two tasks. To keep the code simple I’ve actually used two Sequence containers which are often used as anchor points for a constraint. Very often this is when you have task that you wish to conditionally execute based on an expression. If it the first or only task in the package you need somewhere to anchor the constraint too, so you can then set the expression on it and control the flow of execution. Anyway, back to my code sample, here’s a quick scre... [More]

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]

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]