Flat file (denormalized) into a normalized DB

[rotaxmax12]

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
rotaxmax12

 

[xlbo]

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 before you ask a question

 

[jjefferson]

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.