March 10, 2008 at 11:58 am
I've got a Merge transformation that I'm trying to set up sorting on. 1 input has two derived column tasks which scrubs LastNames & FirstNames. I want to sort on the scrubbed data rather than on the original input data, but since the scrubbed data doesn't exist in the OLE DB source, I can't assign sort keys to it.
Does anyone have any ideas of how I can do this? I've searched the metadata for the Derived Columns tasks but can't find a place where I can set the sort keys later.
Thoughts appreciated. Thanks in advance.
March 10, 2008 at 3:13 pm
Hey Brandie, unfortunately I think you have to go with a sort transform before the merge transform in this case.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 11, 2008 at 7:46 am
Jack,
Sort transform? Not sure what you mean by that. I don't see a Sort transform task.
As far as precedence goes, I have my Source (where it requests for sort keys), then the first derived column task, then the second derived column task and then the merge transformation. So technically I do have the "sort" before the merge.
My question is, can I put the sort on one of the derived column tasks instead of the Source?
Does that make sense?
March 11, 2008 at 7:55 am
Brandie,
I don't see a way to sort on a derived column as the derived column task does not allow you to set the IsSorted property or the Sort Keys. In the Data Flow Transformations section of the designer there is a Sort task that will allow you to sort on your derived columns.
Sometimes I wonder if it wouldn't be easier to just dump a copy of the source data "as is" into SQL Server and manipulate it there. I am sooo much more comfortable in that environment.:satisfied:
I am hoping to make it up to the Jacksonville SQLSaturday and saw that you were presenting. Hope to meet you there.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 12, 2008 at 5:11 am
Fortunately, some of my source data is already in SQL Server. Unfortunately, I have to scrub it, merge it with mainframe data and try and pull the unique records out (because I just know there are millions of duplicates).
Hmm. Maybe my problem is that I'm creating new columns in the Derived column task instead of replacing the current columns.
If you do manage to make it to SQL Saturday, definitely stop by to say hi. I'll be the crazy girl going around pulling my hair out by the roots. @=)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply