Can I add a record number as data passes through

Can I add a record number as data passes through

• SteveLK

  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

  
  

• Davids Learning

  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

  
  

• Mateusz Rajca

  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

  
  
  

• shp21

  Had any luck with it

  Cheers

  
  

• pwhitaker

  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!

  
  

• robertje

  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.

  
  

• AboOmar

  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

  
  

• BALA SINGAM

  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

  
  
  

• Rick_in_Alpharetta

  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

  
  
  

• Kim Madsen

  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

   

   

  
  
  

• mlewus

  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.
  
  
  

• R.Tutus

  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.
  
  

• Iggy Kay

  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

  
  
  

• Ronaldlee Ejalu

  Jamie:

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

  - Mike

  
  
• Can I add a record number as data passes through