dimension table population-out of fact table

I have a large flat file that comes to me. I first import the flat data in to a SQL table for ease of use. Then i put it into a more permanent table with the proper references to dimension tables. I want to build a dimension table out of information from my flat file. I have a dimension table with columns, [Org Client], and [Client#] where [org client] is the name of the client. Both of these columns appear in my flat file but i want to use only the client# in my permanent table. How extract distinct values of client # and [org client] into a dimension table?

My idea was to select distinct values of client# and use some type of foreach loop to go through each client# and use a query to select the TOP(1) values of [org client] where client# = x. Would this work and if so how do I go about setting this up?

I'm really hoping there is a simpler way than this. Thank you all for your time.

A sort transformation in a data flow can get rid of duplicates for you. Then from there you can go into whatever table you want.|||That is very impressive. Thank you very much. Works better than i could've imagined.sql

Different format in flat file for header and data

Hello all,

Is it possible to have two different formats for the header and the data in a flat file connection?

An example text file would look like this:

Col1,Col2,Col3

abcdefghi12345testtesttesttest

abcdeeeee12333setsetsetsetsets

where the header is delimited and the data is ragged right.

It looks like you should be able to accomplish this from the Flat File Connection Manager Editor interface, but perhaps having different delimiter dropdown boxes for the header and columns can only be used if you are using the Delimited format?

Thanks for any info you can provide!

Jessica

Do you even need the header?|||

No, but it would save a lot of time if we didn't have to type in hundreds of column names

I was more interested in knowing if this functionality is supposed to work and I'm missing some check box... or if it isn't supposed to work.

|||I don't think this is supported currently.