What is AggregateText SSIS component?

This component is used for aggregating mulitple rows of text columns inside an SSIS component. It allows you define keys, which act as a 'Group By' clause and concatenates rows in the data flow with identical keys.

Installation

copy AggregateText.dll "%ProgramFiles%\Microsoft SQL Server\100\DTS\PipelineComponents\"
gacutil /u AggregateText
gacutil.exe -iF "%ProgramFiles%\Microsoft Sql Server\100\DTS\PipelineComponents\AggregateText.dll"

If you do not have gacutil.exe, I think you can replace the last two steps with copying the .dll to C:\WINDOWS\assembly in Window explorer.

Configure Visual Studio

  • In the toolbar on the left, right click and select 'Choose Items...'
  • Click SSIS Data Flow Items
  • Select AggregateText
  • Click OK
You should now see 'AggregateText' show up in the control flow items.

Use

  • Input columns must be sorted in the order in which you want to aggregate the text
  • To use the AggregateText component, drag the component into a Data Flow.
  • Attach it to an upstream component
  • Right click on component and select 'Show Advanced Editor'
  • Click the 'Input Columns' tab
  • Select each column from the input that you either want to use in this component or pass through to other downstream components
  • Click 'Comonent Properties' tab
  • You will see a 'Properties' screen pop up.
  • The bottom section 'Custom Properties' is where you will configure the AggregateText'
  • There are four things to configure
  • AggregateText: Set this to the name of the column you wish to aggregate text on
  • keyColumns: Comma separated list of columns that groups records to be aggreagated. For instance if you had columns named docid and textline and wanted to concatenate all lines for a given docid, then docid would be your key column. NOTE: if you are keying on multiple columns, separate the column names with a comma and do not put any spaces in between.
  • LineBreakStyle: Windows and Linux use different line break sequences. You can pick which one you like by entering Windows or Linux. I recommend Windows unless you know you have a need for Linux style line breaks.
  • OutputColumn: The name of the new column that the aggregated text will go to.

That's really all there is to it. Please let me know if any of the documentation can be clarified. Also, how I can attach graphics. I have some screen shots that might be handy.

Last edited Dec 14, 2009 at 6:06 PM by ejust, version 2

Comments

No comments yet.