Aggregate/Concatenate Strings in a Script Component?

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice

1 a

1 b

2 c

3 d

3 e

3 f

I would like an output that looks like this:

CustNumb Invoice

1 a, b

2 c

3 d, e, f

I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...

Hilary

If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.

|||

Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:

Code Snippet

Dim intCustNumber as Integer

intCustNumber = Row.CustNumber

With Row.NextRow

Dim intCustNumber1 as Integer

intCustNumber1 = Row.CustNumber

If intCustNumber = intCustNumber1 then

Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?

Thank you,

Hilary

|||

ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.

Code Snippet

Public Class ScriptMain

Inherits UserComponent

Private intCustomer As Integer

Private strString As String

Private blnFirstRow As Boolean = True

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.CustNumber = intCustomer Then

'Same customer

strString = strString + Row.ColumnVal

Else

If blnFirstRow Then

blnFirstRow = False

Else

'new customer - output the current values

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

End If

'now reset the variables

intCustomer = Row.CustNumber

strString = Row.ColumnVal

End If

End Sub

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)

MyBase.Input0_ProcessInput(Buffer)

If Row.EndOfRowset Then

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

Output0Buffer.SetEndOfRowSet()

End If

End Sub

|||

Thank you, that is just what I needed-- a bit of a map for how to approach it.

Hilary

|||

There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:

http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx

I've also edited the script above to correct the issue.

|||

Thank you so very much. That is just above and beyond, you know? I really appreciate the help.

Hilary

Aggregate/Concatenate Strings in a Script Component?

Hi--

I am uncertain how to do this. I am thinking it could be done in a script component, but after a day of experimentation, I'm not getting any closer. I'd also like to know if there is another component I may be able to use for this.

I have data coming from an Excel Spreadsheet that looks like this:

CustNumb Invoice

1 a

1 b

2 c

3 d

3 e

3 f

I would like an output that looks like this:

CustNumb Invoice

1 a, b

2 c

3 d, e, f

I am not even sure if I should be trying to do this in the subroutine that looks at each row or the one that looks at the entire buffer.

Thanks for any help or ideas...

Hilary

If you know how the maximum number of values you might have, you might be able to do this in a pivot transform. However, I would probably do it in a script. You'd need to set up the output as asynchronous (make the SynchronousOutputID = None on the output). In the ProcessInputRow method, you'll need to out a row on the async output each time the CustNumb changes from the previous value using the Output0Buffer.AddRow method.

|||

Thank you for your reply. When I was trying to do this in script earlier, that was one of the things I had trouble understanding--how to find out when the CustNumb changed. I thought that ProcessInputRow was looking at one row at a time, so I tried grabbing the customer number and then checking the next row to see if it matched... something like:

Code Snippet

Dim intCustNumber as Integer

intCustNumber = Row.CustNumber

With Row.NextRow

Dim intCustNumber1 as Integer

intCustNumber1 = Row.CustNumber

If intCustNumber = intCustNumber1 then

Etc.... not surprisingly, I lost all the rows that didn't have a repetition of the customer number. How do I look at each row and know when the number changes?

Thank you,

Hilary

|||

ProcessInputRow does look at a row each time. You need to declare a variable at the instance level (or a static variable, but I prefer instance) to hold the customer number. I freehanded the code below, so it may not compile, but hopefully it gives you an idea. You'll need to make sure it handles the first row and last row properly - I haven't tested it.

Code Snippet

Public Class ScriptMain

Inherits UserComponent

Private intCustomer As Integer

Private strString As String

Private blnFirstRow As Boolean = True

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Row.CustNumber = intCustomer Then

'Same customer

strString = strString + Row.ColumnVal

Else

If blnFirstRow Then

blnFirstRow = False

Else

'new customer - output the current values

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

End If

'now reset the variables

intCustomer = Row.CustNumber

strString = Row.ColumnVal

End If

End Sub

Public Overrides Sub Input0_ProcessInput(ByVal Buffer As Input0Buffer)

MyBase.Input0_ProcessInput(Buffer)

If Row.EndOfRowset Then

Output0Buffer.AddRow()

Output0Buffer.CustNumber = intCustomer

Output0Buffer.NewCol = strString

Output0Buffer.SetEndOfRowSet()

End If

End Sub

|||

Thank you, that is just what I needed-- a bit of a map for how to approach it.

Hilary

|||

There was a bug in the script above, where the last row could be skipped. I found it when when I was writing this problem up to post on my blog. Here's the full sample:

http://agilebi.com/cs/blogs/jwelch/archive/2007/09/14/dynamically-pivoting-rows-to-columns.aspx

I've also edited the script above to correct the issue.

|||

Thank you so very much. That is just above and beyond, you know? I really appreciate the help.

Hilary

AGGREGATE doesn't do MIN/MAX on textual columns

Hi,

Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?

Thanks

Jamie

Anyone?

|||

Thanks Jamie.

Frankly, it did not seem like a common request for our core data warehousing scenarios - so it was not coded in from day one. During beta, a couple of customers did request it, but they were able to work around the issue using a script component. And, as I remember, becuase they had some additional processing to do once they had found the max string value, a script would have been needed at some point anyway.

Always interested to hear scenarios of course. Meanwhile, this would be an interesting DCR, but so far we have not had much demand.

Donald

However, as my Aunt once said to a salesman who suggested there was "no demand" for something she was seeking - "There is a demand standing right in front of you, young man!"

|||

OK thanks Donald. Sommeone on this forum was indeed asking for it and when he asked why it wasn't there I couldn't answer him. Eventually he used, as you say, a script component.

-Jamie

|||

That someone was me

I am implementing a DW/DM, where I collect data from ca. 25 different source systems / DW's. One case where I need max on varchar: In some cases, as I collect data on invoice row level, one invoice row is allocated to more than one cost center (1-n), and I somehow have to collect only one of the cost centers. The cost center data is in a varchar column, and thus I need some way to collect one of the many choices. For the sake of plausible validation, I always want to take value using same method (max or min, since they are easy to write into sql).. I know the proper way of collecting this kind of data would be at the cost center level, but let's not get into that.

In earlier DW implementations I have made (using Ascential/IBM Datastage), I had tens of cases where I had to take max/min of a varchar column.

It could easily be so that even if the data itself is numeric, it is stored in a varchar column, and I hate strong type casts, since I can never be sure if there could sometimes be text information as the column allows it. I have seen that also.

Not implementing max/min on varchar columns seems as a silly limitation in SSIS. In my opinion, this kind of features should be included in the basic transforms, so that there would be no need to always write short scripts - thats what was used in DTS.

IMHO Datastage / Informatica are much more user friendly than DTS in sense for not needing programming skills, I would really like to see SSIS to evolve more into that direction (which it already has, when comparing to DTS).

Markus

|||If you consider adding min/max aggregation of text values to the aggregate transform, while MS in the code consider adding first and last. Occasionally they are very helpful.|||

Guys,

You're more likely to get this functionality if you ask for it thru the proper channels. Click through here and vote, and add a comment

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131210

Anecdotes of why you need it are a huge help as well.

-Jamie

|||How about including it because it's valid transact-sql to use it on non-numeric fields?

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation. It's also better to use an aggregate transform in my package rather than having to query outside of the datastream to do something that should've been included in the first place.

For a given group of records, I want to, for instance, find the minimum text value in a column -- I shouldn't need a script to tell me that.|||

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil|||

Phil Brammer wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Phil Brammer wrote:

Does it? Then I stand corrected.

I personally think that's a bad idea because of the reasons elucidated in my last post in this thread, but hey!

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil

true!

-J

|||

All,

The original feedback item was posted under the wrong category so I've re-posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=246223

Thanks to Phil for pointing it out.

-Jamie

AGGREGATE doesn't do MIN/MAX on textual columns

Hi,

Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?

Thanks

Jamie

Anyone?

|||

Thanks Jamie.

Frankly, it did not seem like a common request for our core data warehousing scenarios - so it was not coded in from day one. During beta, a couple of customers did request it, but they were able to work around the issue using a script component. And, as I remember, becuase they had some additional processing to do once they had found the max string value, a script would have been needed at some point anyway.

Always interested to hear scenarios of course. Meanwhile, this would be an interesting DCR, but so far we have not had much demand.

Donald

However, as my Aunt once said to a salesman who suggested there was "no demand" for something she was seeking - "There is a demand standing right in front of you, young man!"

|||

OK thanks Donald. Sommeone on this forum was indeed asking for it and when he asked why it wasn't there I couldn't answer him. Eventually he used, as you say, a script component.

-Jamie

|||

That someone was me

I am implementing a DW/DM, where I collect data from ca. 25 different source systems / DW's. One case where I need max on varchar: In some cases, as I collect data on invoice row level, one invoice row is allocated to more than one cost center (1-n), and I somehow have to collect only one of the cost centers. The cost center data is in a varchar column, and thus I need some way to collect one of the many choices. For the sake of plausible validation, I always want to take value using same method (max or min, since they are easy to write into sql).. I know the proper way of collecting this kind of data would be at the cost center level, but let's not get into that.

In earlier DW implementations I have made (using Ascential/IBM Datastage), I had tens of cases where I had to take max/min of a varchar column.

It could easily be so that even if the data itself is numeric, it is stored in a varchar column, and I hate strong type casts, since I can never be sure if there could sometimes be text information as the column allows it. I have seen that also.

Not implementing max/min on varchar columns seems as a silly limitation in SSIS. In my opinion, this kind of features should be included in the basic transforms, so that there would be no need to always write short scripts - thats what was used in DTS.

IMHO Datastage / Informatica are much more user friendly than DTS in sense for not needing programming skills, I would really like to see SSIS to evolve more into that direction (which it already has, when comparing to DTS).

Markus

|||If you consider adding min/max aggregation of text values to the aggregate transform, while MS in the code consider adding first and last. Occasionally they are very helpful.|||

Guys,

You're more likely to get this functionality if you ask for it thru the proper channels. Click through here and vote, and add a comment

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131210

Anecdotes of why you need it are a huge help as well.

-Jamie

|||How about including it because it's valid transact-sql to use it on non-numeric fields?

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation. It's also better to use an aggregate transform in my package rather than having to query outside of the datastream to do something that should've been included in the first place.

For a given group of records, I want to, for instance, find the minimum text value in a column -- I shouldn't need a script to tell me that.|||

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil|||

Phil Brammer wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Phil Brammer wrote:

Does it? Then I stand corrected.

I personally think that's a bad idea because of the reasons elucidated in my last post in this thread, but hey!

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil

true!

-J

|||

All,

The original feedback item was posted under the wrong category so I've re-posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=246223

Thanks to Phil for pointing it out.

-Jamie

AGGREGATE doesn't do MIN/MAX on textual columns

Hi,

Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?

Thanks

Jamie

Anyone?

|||

Thanks Jamie.

Frankly, it did not seem like a common request for our core data warehousing scenarios - so it was not coded in from day one. During beta, a couple of customers did request it, but they were able to work around the issue using a script component. And, as I remember, becuase they had some additional processing to do once they had found the max string value, a script would have been needed at some point anyway.

Always interested to hear scenarios of course. Meanwhile, this would be an interesting DCR, but so far we have not had much demand.

Donald

However, as my Aunt once said to a salesman who suggested there was "no demand" for something she was seeking - "There is a demand standing right in front of you, young man!"

|||

OK thanks Donald. Sommeone on this forum was indeed asking for it and when he asked why it wasn't there I couldn't answer him. Eventually he used, as you say, a script component.

-Jamie

|||

That someone was me

I am implementing a DW/DM, where I collect data from ca. 25 different source systems / DW's. One case where I need max on varchar: In some cases, as I collect data on invoice row level, one invoice row is allocated to more than one cost center (1-n), and I somehow have to collect only one of the cost centers. The cost center data is in a varchar column, and thus I need some way to collect one of the many choices. For the sake of plausible validation, I always want to take value using same method (max or min, since they are easy to write into sql).. I know the proper way of collecting this kind of data would be at the cost center level, but let's not get into that.

In earlier DW implementations I have made (using Ascential/IBM Datastage), I had tens of cases where I had to take max/min of a varchar column.

It could easily be so that even if the data itself is numeric, it is stored in a varchar column, and I hate strong type casts, since I can never be sure if there could sometimes be text information as the column allows it. I have seen that also.

Not implementing max/min on varchar columns seems as a silly limitation in SSIS. In my opinion, this kind of features should be included in the basic transforms, so that there would be no need to always write short scripts - thats what was used in DTS.

IMHO Datastage / Informatica are much more user friendly than DTS in sense for not needing programming skills, I would really like to see SSIS to evolve more into that direction (which it already has, when comparing to DTS).

Markus

|||If you consider adding min/max aggregation of text values to the aggregate transform, while MS in the code consider adding first and last. Occasionally they are very helpful.|||

Guys,

You're more likely to get this functionality if you ask for it thru the proper channels. Click through here and vote, and add a comment

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131210

Anecdotes of why you need it are a huge help as well.

-Jamie

|||How about including it because it's valid transact-sql to use it on non-numeric fields?

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation. It's also better to use an aggregate transform in my package rather than having to query outside of the datastream to do something that should've been included in the first place.

For a given group of records, I want to, for instance, find the minimum text value in a column -- I shouldn't need a script to tell me that.|||

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil|||

Phil Brammer wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Phil Brammer wrote:

Does it? Then I stand corrected.

I personally think that's a bad idea because of the reasons elucidated in my last post in this thread, but hey!

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil

true!

-J

|||

All,

The original feedback item was posted under the wrong category so I've re-posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=246223

Thanks to Phil for pointing it out.

-Jamie

Aggregate Component very slow?

Does anyone know of a way to speed up an aggregation? I am pulling in a flat file that goes to an aggregate component. The flat file has approx 10m records and I am grouping by the first 14 columns, the input is sorted in that order. There are no more than 2 rows for each grouping and most have one. The problem is that it takes about 2 minutes to read in the flat file, but 30 min later the aggregation has only put out about 30k rows. I know that there will be around 9m rows after grouping so obviously it is going to take forever to get through all of them. Any ideas would be appreciated. We are doing a POC for SSIS and judging from the posts that I've seen sorting and aggregation seem to have a lot of perfomance issues, which surprizes me in an ETL tool. We are running on a dedicated Xeon box with two 3GHZ cpus and 4GB of ram, so the problem is not there.

Thank You!
Harry
GuideOne InsuranceHello.

Strange. I have had stunning performance on everything I have tried. Haven't tried dual 64-bit, though. Does the same happen on a 32-bit workstation? Have you tried to set processor affinity on the process so that it only uses one processor?

A humble suggestion: Minimize size of data flow tasks. Move functionality to SQL tasks.

I believe I have about the same amount of data you have (10M records, 10-20 columns of text which I pull via FTP and from some in-house systems, I have been running it on SQL 2005 for a years time)

I have found it to make sense to move much of my SSIS Data Flow based functionality into control flow SQL tasks, which I mirror as stored procedures for reference and testing.

This has a number of advantages
1. Any changes in structure is easier.
2. Documentation is easier (I find it easier to print stored procedures than data flow components)
3. Testing and iterativ development is easier.
4. Special needs are easier met. I have found writing CLR code to run inside the server much, much easier to develop than script-based tasks (I have used both). Writing custom data flow components were a real nightmare on the beta I tried it. An extra bonus has been the user defined aggregates, which makes my life easier.
5. Trust. I trust SQL "GROUP BY" more than data flow aggregate. Same goes for sorting.

Disadvantages with moving functionality from data flow to control flow has been
1. Keeping two identical sources is error prone (stored procedures that mimics SQL Server tasks)
2. Paralellism. Since a task needs to be finished before the next one can start, this should teoretically take more time. Still, the new engine is extremely performant, so I have met my requirements easily. Getting the data via FTP has been the bottleneck.
3. Storage. Obviously, saving temporary data along the way puts extra pressure on the storage system. I actually used to run defrag automatically before my dts package, don't know if it helped, but I don think it can hurt.

Just wanted to share some experiences.

Hope this is helpful.|||Gorm,

Thank you for the insights, they are very helpful. The data that I am trying to aggregate is from a flat file that has been ftp'ed from a z/os box. What I ended up doing is creating a "scratch" table, loading the flat file to there and grouping it with a query from the scratch table. That ran reasonably quick. I agree that SQL 2005 seems to do a great job on most things that I have tried. I does still bother me that SSIS has performance issues with sorts, aggregates and merges. I would think those would be the best performers in an ETL tool. That is going to hurt them in a lot of shops. I have managed to find work arounds for most of them doing it, as you it sounds like you are, in the SQL. We are currently running 32 bit. We originally had the 64 bit stood up, but there were a lot of issues there so we backed off. We are just evaluating the product for our warehousing which currently runs on DB2 7.2 on a z/os box. I really haven't tried much with the stored procedure route yet, but that is great thinking. Do you know of some good resource manual/tutorials for SP devlopment?

Once again thank you for your help and suggestions!!
Harry
GuideOne Insurance

aggegrate component

Hi,

i have 2.5million records which i have to aggregate on a couple of columns, then add those to another table.

i have created ole-db-source -> aggegrate

is this wise?

or should i use ole-db-source with a sql-query that aggregates for me...

will this increase my performance?

Try to use the power of the SQL engine to do this if you can. It has the benefit of indexes and statistics, which are not available to the Aggregate Tx, so it should be faster. However if the source is a high volume transactional system it may be unacceptable to have this type of query running, in which case the Agg Tx is probably a better way for you to go.

As to which is faster, I can guess, but just test it and find out the real answer.