I have a list of companies that all have the same Company Id but have formatting issues with the names. For example I have the following:

CompanyId Name
12345 Microsoft
12345 Microsoft Inc.
12345 Microsoft, Inc.

I figured I would use a simple data flow task to insert the company id and name into a company table where the primary key is the company id (I would then ignore the errors of duplicate keys). I was expecting to see the first instance of the company inserted into the company table and the dupes ignored. That's not the case - I get an error on duplicate primary keys. Can I not do this?

This is more of a transact-sql question. The observed behavior is correct. In the sense of SSIS, you can use a sort transformation to eliminate dups. Or, you can structure your source query to do more of a select companyid, max(name) from table group by companyid.

There is an option in the sort transformation (a check box) to eliminate dups. You'd have to sort only on the companyID though.

This is more of a transact-sql question. The observed behavior is correct. In the sense of SSIS, you can use a sort transformation to eliminate dups. Or, you can structure your source query to do more of a select companyid, max(name) from table group by companyid.

You are right. But, how would a sort transorm work to eliminate dupes?

There is an option in the sort transformation (a check box) to eliminate dups. You'd have to sort only on the companyID though.