Hi Everyone,

I'm sure i have a fairly simple question but i'm pretty new to SQL2K5 SSIS.
I'm looking to see the best approach to normalizing some SQL tables I have.
I want to extract the distinct types and then link the new type id back to my
account table.

For instance i have table as such
Account# Title Type
001 ABC Test
002 DEF Test
003 XYZ Production

I've been able to load an AccountType type by selecting distinct from the
Type field and placing that into a table. But how do I know grab the
Account#, Title, and the correct TypeID for each row so that I can load this
into a new table.

If someone could provide a little assitance of any kind I would be greatly
appreciative as my current project requires a ton of this.

Thanks alot,

-mike

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hello Michael:
This is not really SSIS or DTS related, more appropriate for general
database design, but that's OK.

Since you are using SELECT DISTINCT (Type) FROM ...to populate your lookup
table, you can be assured that every [Type] will be unique. Is the Type_ID
column specified as IDENTITY? If so, then every [Type Description] will have
a unique [Type_ID].

So in your SSIS package, have the first Data Flow do the operation above to
populate the lookup table. THen have the second Data Flow (down stream of
that) do the main data pull. In ithe pipe line, put in a Lookup Transform
that references the lookup table you populated in the earlier task. Connect
the two tables on <MainTable>.Type to <LookupTable>.[Type Description] and
grab the [Type_ID] from the lookup table to add to the data pipeline.

In the Destination adapter, make sure the [Type_ID] column gets mapped to
the correct field in the Main table.

HTH
--
Todd C

[If this response was helpful, please indicate by clicking the appropriate
answer at the bottom]


"Michael Morse" wrote:

Hi Michael,
as far as i know there isn't a stand-alone task that can entirely
fulfill your needs (ie, normalization).
Therefore, you can perform a normalization combining more tasks. But
how to do it, well this depends on your architecture.
Is this task going to be scheduled? Or are you going to execute it una
tantum? how many 2b-normalized-tables and how many attributes (eg,
type) must be decoded that is how many tables do you have to create in
order to normalize the db? Are they going to be alwalys the same
number?

For future questions about SSIS please take a look at (or post on):
microsoft.public.sqlserver.integrationsvcs

Bye,
M.