▼

Friday, 30 March 2012

Custom SSIS Component: Rownumber Transformation

Adding a rownumber in a data flow is an often seen activity. This isn't a standard feature of SSIS so you will have to use a Script Component or a Third Party Component for that. My Microsoft.Net Colleague Marc Potters and I created a custom component for that which should make thinks easier for those who have less programming experience.
Rownumber
























You can either create a new rownumber column and specify the datatype or use one of the existing columns. In the second part you can specify the start number and in the increment. The startnumber can also be specified by a variable which is for example populated by a query in an Execute SQL Task. The last block if for storing the final rownumber in a variable. However this number will only be available when the data flow task has been finished.

Disclaimer
THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

Downloads:
You can download a SSIS 2008 and 2012 version on the download page.

Version 1.1: Added the option to add one increment at the end. This makes it easier to use if you want to continue with that number in the next data flow task.

Installation
The installer registers the DLL in the GAC and copies it to the component folder of SSIS (example: C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\). After that you have to close Visual Studio/BIDS because it caches the GAC on startup.

How add the task the the toolbox
When you open a SSIS project in Visual Studio/Bids, right click the toolbox and choose "Choose Items...". After this Visual Studio will be busy for a couple of seconds!
Right click toolbox






















When the Choose Toolbox Items window appears, go to the SSIS Data Flow Items and search for the newly installed  Rownumber component and select it. Click ok to finish.
Choose Toolbox Items




















Now the new component will appear in the toolbox. Ready to use! Have fun.
New component added























Bugs or feature suggestions
Please contact me or leave a comment if you have any suggestions or found a bug in this Custom component.

8 comments:

  1. Jeff6 April 2012 at 21:35

    When saving the final row number to a variable, it looks like the RowNumber component is saving the last-used value rather than the next-available value. I think it would be more useful to save the next-available value, so that the same variable can be used as the seed for the initial value if one uses the component twice in one package.

    ReplyDelete
    Replies
    1. Joost van Rossum6 April 2012 at 23:10

      Hi Jeff,

      Thanks for your valuable feedback! It's now based on the idea of the TSQL @@IDENTITY function, which returns the last identity value that is generated. But maybe we could add an option to choose between those two options. Will discuss it with my Colleague.

      Delete
    2. Joost van Rossum12 April 2012 at 14:48

      Hi Jeff,

      We added a new version which allows you to add an increment at the end.

      Delete
  2. Jeff8 May 2012 at 17:01

    Thanks for the change! Now the component does what I needed, and is saving me a considerable amount of repetitive SSIS scripting.

    ReplyDelete
  3. Prakash14 August 2012 at 18:52

    Hi Joost,

    Is this component possible to do sort? (partiton by and order by)

    ReplyDelete
    Replies
    1. Joost van Rossum14 August 2012 at 22:55

      No that isn't supported. You could try sorting the data and then use a Script Component to do the counting like http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html.
      And combine that with something like this http://microsoft-ssis.blogspot.com/2011/04/compare-values-of-two-rows.html where I keep the values of the previous row. If the sorted column changes then you reseed.

      You could also post a Question in the msdn forum to see if there are alternatives.

      Delete
  4. Ik Emole21 March 2013 at 16:35

    Hi,

    I installed this on my machine and I found it to be very helpful. However, when deployed, my package is going to run on a different application server.

    Do I need to install this custom component on that server for my package to function well?

    ReplyDelete
  5. Joost van Rossum21 March 2013 at 17:02

    Yes, the machine that runs your packages needs this install.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.