Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Flat file (denormalized) into a normalized DB

Flat file (denormalized) into a normalized DB

Flat file (denormalized) into a normalized DB

I am trying to build an SSIS package that will be used to seed a new system. I have a flat file that we have to concatinate five fields to create the primary key.

I tried to use the Aggregate tool, but as I need to load a total of 30 columns, in order to have all of the data pass through the Aggregate tool, I need to select all the columns I need. When I do the Group By in the Aggreate, I still have duplicate rows (PK). I would like to avoid creating temp tables or working tables if possible.

Essentially, how can I build (SELECT PK, Field1, Field2, Field3 FROM table WHERE PK IN (SELECT PK FROM table GROUP BY Field1, Field2, Field3 HAVING COUNT(*) > 1) out of a flat file source? But then still have the remaining data continue through the processing?

SQL: 2008 R2
Input: csv Flat File
Output: SQL Server 2008 R2

Thanks all

RE: Flat file (denormalized) into a normalized DB

not meaning to be too pedantic here but if you still have duplicate rows after you have done your concatenation, it is not a primary key

you could simply pass the data flow through a sort
If you choose PK as your sort item you can choose the option to drop duplicate rows...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244: How to get the best answers before you ask a question

RE: Flat file (denormalized) into a normalized DB

My only thought is you may want to re-think your preference to not use working tables. With no exception (to my knowledge) we always import a foreign data source into tables in a STAGING database, where we can then use stored procedures to check out and transform the data. Just makes it so much easier on the SSIS design.

I understand your application may have reasons this is not appropriate, but I would not otherwise rule this option out.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close