H

I want to add rank to a column in ssis in a derived column actually.

Can u please tell how to add it and the syntax for adding it in a derived column.

This cannot be achieved in Derived column as it does not retain the precious record info. You would have to do it in Script component as a transform after sorting the data. Do you want rank based on groups or sequence number for each record?

Do you mean a counter with rank?

Here is an Script Component example: http://microsoft-ssis.blogspot.com/2010/01/create-row-id.html

or you can do that with a third party component: http://www.sqlis.com/post/Row-Number-Transformation.aspx

or if your source is Ms SSQL you can do that with row_number: http://msdn.microsoft.com/en-us/library/ms186734.aspx

 

Hi,

I want to apply rank component for a of mine which is having duplicate values.

For example I have a column 'A'

I want to apply rank to all the rows of 'A' and than compare them with one another and remove the duplicated rows.

Please tell me the syntax for giving rank to a column.

I am very new to ssis. please teke time and share your knowledge with as it is very important for me at this time.

Thanks. Please tell me if there is any other way to do it.

Thanks in advance.

 

What is the source data? if it is SQL server you can use query at the source to include rank number.

my source is SQL  Server 2005

Please tell me the query and the way  to implement it.

Thanks Sudeep.

Check the link in my last post.

hey Sudeep,

i was asking you about the implementation in SSIS

Thanks

hey Sudeep,

i was asking you about the implementation in SSIS

Thanks

It would be easy and better for you to implement through t-sql code using rank funcitons. In SSIS it is not possible out of box. You need to write some code use script component or use some third party component to achieve the ranks. You already mentioned in one of your previous post that you want to remove the duplicates so why not delete the deplicates and then bring only the required data in SSIS.

Thanks,

 

Can you please give the script for the same.

I am unable to write the script for my logic to implement in SSIS.

I will be very thankful if some one could help me with it.

Thanks in Advance.

Thanks,

 

Can you please give the script for the same.

I am unable to write the script for my logic to implement in SSIS.

I will be very thankful if some one could help me with it.

Thanks in Advance.

Sam thats too generic there would be several ways to solve one problem ... 

can u tell us what is ur source [i mean table view THE DATA which records tuples you suspect is having duplicates ] 

on what basis u decide duplicate ? and what row qualifies to go into destination ... for eg  say ur source is 

select id,name, date from source_table  

duplicacy could mean id and name being repeated multiple times and may be business rule is like pick up rec with latest date vale if thats the case go for source as : 

select id, name,max(date) from sourcetable 

group by id,name 

if all the columns are repeating in your source table may be use DISTINCT ... 

would be great if u give ur destination, source and business requirement ..

if you have got primary key violations and any record qualifies then may be simply at destination component set Ignore failure property and ur job is done ...

 

may be removing duplicate at source itself ... might solve ur problem 

check out more on duplicates : http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

i hope u got the idea ...

Hi Kunal,

I have a requirement that my source file contains duplicates as given below.

Id    Name    ModDate

12    abc        01-Jan-15

12    xyz        02-Jan-15

My requirement is to process the latest modified date record and load the first record into error log for business to analyse the duplicate data in their source systems. Please advise me the best way in SSIS. My target is also Flat File. Kindly let me know if you need any further details.

Thanks.

Hi Kunal,

I have a requirement that my source file contains duplicates as given below.

Id    Name    ModDate

12    abc        01-Jan-15

12    xyz        02-Jan-15

My requirement is to process the latest modified date record and load the first record into error log for business to analyse the duplicate data in their source systems. Please advise me the best way in SSIS. My target is also Flat File. Kindly let me know if you need any further details.

Thanks.

For that there's no need of rank

See how to do deduplication within file itself without using a staging table approach here

http://visakhm.blogspot.in/2014/01/ssis-tips-deduping-flat-file-data.html