none
Using DENSE_RANK() in a DataFlow

    Question

  • In my SSIS 2010 package, I have a text file as a source, and an OLEDB connection to a SQL Server table as my destination. One of the fields at the destination table needs to receive an integer value that represents the DENSE_RANK of that row. The rank is based on parts of a persons name (from the data); PreName, FirstName, MiddleName, LastName, SuffixName. 

    The destination table is set up as a staging table. Is there a way in the DataFlow that I can calculate the DENSE_RANK before inserting into the destination stage table, or will have I have to first populate the table, and then somehow derive the DENSE_RANK?

    Thank you for your help.

    cdun2

    Friday, September 13, 2013 3:32 PM

Answers

All replies

  • SSIS 2008 you meant?

    Use an interim/staging table to apply the T-SQL only DENSE_RANK


    Arthur My Blog

    Friday, September 13, 2013 3:44 PM
    Moderator
  • Hi,

    Like Arthur said , load from text file to temp table (create #temp table using if not exists condition and then insert into it) now use Execute SQL task and then apply Dense_Rank() logic on #temp table and insert into your final destination table .

    INSERT Final_Destination_tbl
    SELECT DENSE_RANK()OVER(ORDER BY PreName, FirstName, MiddleName, LastName, SuffixName) , 
    othercolumns FROM #temp 

    Make sure to set RetainSameConnection property to true if you are using #temp table .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.


    Friday, September 13, 2013 4:59 PM
    Moderator
  • SSIS 2008 you meant?

    Use an interim/staging table to apply the T-SQL only DENSE_RANK


    Arthur My Blog

    SSIS  package built is VS 2010.
    Friday, September 13, 2013 5:24 PM
  • Yes, VS2010 is for SSIS 2008, and sure you can use the #temp table as SathyanarrayananS suggested or a permanent one will work just fine in fewer tweaks. I guess you asked because you wanted to avoid a trip to a database or creating objects in there, unfortunately the only other workaround could be mimicking this function in .Net code (C# or VB) in a Script Transformation, but that is doable, you just need to implement its specification, see an example: http://www.codeproject.com/Articles/441678/Getting-Started-With-SSIS-Script-transforms 

    Arthur My Blog

    Friday, September 13, 2013 5:55 PM
    Moderator
  • Hi Cdun2,

    Since you design the SSIS packages in Visual Studio 2010, it means that you are using SQL Server 2012 Integration Services. For SSIS 2012, the staging table or Temp table solution mentioned by Arthur and Sathya also works.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    Tuesday, September 17, 2013 3:29 AM
    Moderator