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]
5 Jun 2012 17:24
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 Conditio... [More]
5 Jun 2012 15:09
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 ma... [More]
5 Jun 2012 14:53
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 expres... [More]
5 Jun 2012 14:48
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 al... [More]
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.
5 Jun 2012 14:15
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 transf... [More]
5 Jun 2012 06:30
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]
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]
1 May 2009 08:40
The Trace File Source adapter is a useful addition to your SSIS toolbox. It allows you to read profiler traces stored as .trc files and read them into the Data Flow. From there you can perform filtering and analysis using the power of SSIS. There is no need for a SQL Server connection this just uses the trace file.
Cache warming for SQL Server Analysis Services
Reading the flight recorder
Find out the longest running queries on a server
Analyze statements for CPU, memory by user or some other criteria you choose
The Trace File Sour... [More]