Can I add a record number as data passes through

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

Looks possible to me using a custom script component to compare incoming fields to the last set coming in. Would work nicely if the data is sorted.|||

bobbins wrote:

Hello.

In SSIS, is it possible to add a record number to each row of data as I copy it from the source to the destination.

An example of my source data is below, For each MemberID want to record the number of times it occurs in the table.

MemberID

2898

2899

2899

What I want it to look like when it gets to the destination is:

MemberID RecordNumber

2898 1

2899 1

2899 2

Like an Identity column I suppose, not for the whole table but for each MemberID.

Thanks

The Rank transformation will do this for you:

Rank Transform
http://blogs.conchango.com/jamiethomson/archive/2006/09/12/SSIS_3A00_-Rank-Transform.aspx

-Jamie

|||

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

|||

mike.groh wrote:

Jamie:

That's a very cool and useful component. Thanks! Great work.

- Mike

Mike,

Its a pleasure. I've been after that sort of feedback for a long time :)

Again I have to reiterate Darren Green's contribution. It was my idea but he realised the idea.

-Jamie

|||

Thanks for the info, I haven't got round to doing this yet but I'm sure it'll work.

Thanks very much, top people!

|||

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

|||

bobbins wrote:

I've downloaded it and installed it but how do I use it in my packages? Should I now be able to see it in Visual Studio amongst all the other data flow transformation options.

Cheers

The instructions here: http://msdn2.microsoft.com/fr-fr/library/ms136125.aspx are for custom tasks but its intuitively similar for custom components. Look for the section entitled "How to use the Task in SSIS Designer"

-Jamie

|||If you just want the row number function, then use the Row Number Tx http://www.sqlis.com/default.aspx?93, as it does not require a sorted input.|||

I still can't do it because when I select 'Choose toolbox items' in the data flow designer it just list what's there already, I only have an option to browse on the COM and .NET components, is this where I should be putting it?

Thanks.

|||

You are looking at the Data Flow page in the Choose Toolbox Items?

You have scolled down to find the transform, in alphabetical order?

Double check this, and perhaps compare with some screenshots here-

Konesans - Frequently Asked Questions - How do I install a task or transform component?
(http://www.konesans.com/faq.aspx#installtask)

If this does not help, on the machine you are running Visual Studio, go to C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents, and look for the DLL file. I'm not sure which transform you are trying right now, so I cannot specify the filename for you here. If the file is not there, then it will never show up in Choose Toolbox Items, so did the install fail?

|||

Yes I am.

Yes I have, it's definately not there. I have checked against the link you provided and I cannot see the component in the list.

I am attempting to use the RankTransform component. I think it installed ok as there were no errors. I've searched for the dll ( it's full name is Conchango.SQLServer.SSIS.DataFlow.RankTransform.dll ) found that it is located in C:\Program Files\Microsof SQL Server\90\DTS\CompFldr so this looks like conformation that it has installed ok.

I've copied it to the directory that you specified and opened the 'choose items' again and it was listed, so I've added it.

I'd like to say a big thanks to yourself and Jamie for the help with this, as someone who doesn't know much about Visual Studio and dll's and stuff you've been an massive help.

I'll now try to use it (I'll have to go back to the instructions!) and let you know how it goes.

Thanks again

|||

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

|||

bobbins wrote:

Ok, the component works great but it is not putting in what I expected it to:

My source data is:

Member Number

2898

2899

2899

I added a sort task to sort it by Member Number ascending.

In the rank transform task I selected Member Number as the sort key and checked the partition box and selected row number as the output.

When the data reached the destination it looked like this:

Member Number Row Number

2898 1

2899 2

2899 3

I'd expected it to look like this:

Member Number Row Number

2898 1

2899 1

2899 2

because I assumed from what I'd selected in the rank transform task that it's essentially running this query:

SELECT [Member Number] , ROW_NUMBER() OVER (PARTITION BY [Member Number] ORDER BY [Member Number]) AS [Row Number]

FROM StatusHistory

which does give me the expected results.

Have I selected the wrong options in the rank transform task or I have I completely got the wrong end of the stick and am trying to use this task for something that it was not designed for?

It's a great thing to have anyway.

Thanks

Bobbins,

Thankyou for making me aware of this. it looks as though the partition functionality might not be working. I'll check it out.

-Jamie

|||

Had any luck with it?

Cheers