Wednesday, October 17, 2007

More Interesting SSIS Components

I knew about the Konesans Trash Destination, but it turns out that they have a whole series of free, publicly available SSIS components.  The main product page is here, and below I have pasted in content for each of the transforms, with the link to the download page:

Checksum Transformation

 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. Checksums can reduce network contention and increase process performance by allowing you to verify data through a single value rather than transferring all data values for comparison.

Data Generator Source

This source component literally generates data. Specify how many columns you want, and how many rows, then watch the data flow out. Build demonstration and research scenarios faster with this simple source.

File Watcher Task

The File Watcher Task does what it says really, it watches a folder waiting for files. When an available file is found the task completes, returning the name of the file for onward use within the package.

Regular Expression Transformation

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. If all columns selected pass their tests then rows are passed down the successful match output. Rows that fail to pass all tests are directed down the alternate output.

Row Count Plus Transformation

The Row Count Plus Transformation can replace the stock transformation. We have recreated and extended with more functionality and a user friendly interface for faster and easier package design.

Row Number Transformation

The Row Number Transformation calculates a row number for each row. It offers ROW_NUMBER or IDENTITY like behaviour within the Data Flow. Uses include surrogate key generation or data partitioning within the pipeline.

Trash Destination

The Trash Destination Adapter is a development aid. It allows you to quickly terminate a data flow path, and does not require any configuration. It will consume the rows without any side effects, and prevents warnings or errors you may otherwise receive when executing the data flow.

Tuesday, October 16, 2007

Additional SSIS Transforms that didn't ship with the product

There are a whole series of SSIS transforms that Microsoft has made available for download.  They have differing degrees of usefulness, and have been available for a while but I am putting them out here for future reference.  Off the top of my head, it looks like Regex would be useful, UnPackDecimal if you are deal with mainframe files, calendar transform (as I recall it has a dependency on a DLL from SSAS though).


Regex Transform
Regex is an SSIS dataflow component that applies a configured regular expression against an incoming column, matching, extracting, or splitting, as configured by the user.

ConfigureUnDouble Transform
ConfigureUnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes. Configure the quote character by, on the first tab of the advanced editor, setting the "Qualifier" custom property to the desired value.

RTrimPlus Transform
RTrimPlus takes a string or unicode column and removes trailing spaces, whether ASCII or Japanese.

UnpackDecimal Transform
UnpackDecimal takes an input column formatted in packed decimal (comp-3) and generates the corresponding Decimal value.

UnDoubleOut Transform
UnDoubleOut is an SSIS dataflow component that removes qualifiers from quoted text, either in place or via the creation of a new output column.

CodePageConvert Transform
CodePageConvert is an SSIS dataflow component that translates from and to any code page or unicode character representations.

SeeBuffer Transform
SeeBuffer is an SSIS dataflow component that sits in a data flow and is provided a look at each buffer that is presented to it.

UnDouble Transform
UnDouble takes a text column and removes bracketing quotes if present, plus replaces double quotes inside the text with sinqle quotes.

NullDetector Transform
NullDetector is an SSIS dataflow component that sits astride a data flow, and, depending on whether the value of a user-indicated column is null or not, routes rows to one or the other of its outputs.

Calendar Transform
CalendarTransform is an SSIS dataflow transform component that generates standard calendar attributes