How to map data/define transformation in SSIS package?

Question

Post reply

How to map data/define transformation in SSIS package?

yingchai

SSCommitted

Points: 1669
More actions
April 12, 2010 at 10:03 am

#142538

Hi,
I'm quite new to SSIS and now in the process of creating a SSIS package to load data from a csv file.
Inside the csv file, there is a COMPANY column name which contains the company code eg: 'AAA'
Currently, the COMPANY column from csv file is mapped to ENTITYID column in my fact table (refer to screenshot attached).
When I load the csv file into my fact table, I intend to store the company code as 'BBB' in the ENTITYID column.
Meaning to say, the transformation should look like this:
COMPANY | ENTITYID
---------------------
AAA | BBB
How can I achieve that? Kindly advise...Thanks!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Add a Derived Column Transformation to your data flow.

Select "Replace 'EntityID'" in the Derived Column.

Then use the following expression:

(EntityID == "AAA") ? "BBB" : EntityID

This will replace all AAA values with BBB, but it leaves all the other Company_Codes untouched.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

yingchai SSCommitted Points: 1669 More actions · Answer 2

da-zero (4/13/2010)
Add a Derived Column Transformation to your data flow.
Select "Replace 'EntityID'" in the Derived Column.
Then use the following expression:
(EntityID == "AAA") ? "BBB" : EntityID
This will replace all AAA values with BBB, but it leaves all the other Company_Codes untouched.

Hi da-zero,

If I have more than one transformation for EntityID column, how is the expression looks like?

COMPANY | ENTITYID

---------------------

AAA | BBB

CCC | DDD

EEE | FFF

Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

Build a mapping table (OldID, NewID) and then use a Lookup transformation to lookup the new ID based on the old ID ... much easier than building a mega-complicated nested condition.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

yingchai (4/13/2010)
Hi da-zero,
If I have more than one transformation for EntityID column, how is the expression looks like?
COMPANY | ENTITYID
---------------------
AAA | BBB
CCC | DDD
EEE | FFF
Thanks.

Ah sorry, I misunderstood your question. I thought you only had to change one mapping :-).

In the case of multiple mapping, the solution of Phil Parking is the way to go. It is much much better in maintenance and it is easier to implement than a nested-structure in the one-line derived column expression editor.

A couple of extra pointers:

* use SQL code to get the data from your mapping table instead of selecting the table from the dropdown-box (this is common best practice)

* don't forget to do something with the lookup errors (e.g. if the company code was not found in the mapping table). Either ignore them or solve them by redirecting the rows to other components (see the configure error output in the lookup component).

* if you ignore the error rows, replace the null values by a dummy value (like -1 or something like that) if this is desired

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

yingchai SSCommitted Points: 1669 More actions · Answer 5

Hi Phil,

I still encounter some problems with the lookup table where by it still can't transform my data from source file into my fact table. Please refer to my screenshot for the current settings at SSIS package.

My source file contains 'GSPN' data under Sorg column. During my loading process, I intend to transform GSPN into PP2 and load PP2 into my fact table.

How can I get it right? Thanks.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 6

Sorg should be joined to source_id, not destination_id.

The rest should be ok ... as long as you do not need sorg later on in the dataflow.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yingchai SSCommitted Points: 1669 More actions · Answer 7

Hi Phil,

I had some problems here after applying the Lookup Transformation when I want to do partial mapping.

For example, if I have other few hundreds of PRODUCT code which I want to "load as-it-is" to the fact table while I want to map some product id, what is the best solution? It seems that whatever member ids that I need to load in must be included in the look-up table which is a bit troublesome to maintain.

Please refer to my mapping table below which I created in SQL and is used in the Lookup Transformation

SOURCE_ID | DESTINATION_ID

---------------------

AAA | 111

BBB | 222

CCC | 333

| No_Product

Please advise. Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 8

yingchai (4/13/2010)
Hi Phil,
I had some problems here after applying the Lookup Transformation when I want to do partial mapping.

That's why I talked about redirecting or ignoring the error output of your lookup component. If the lookup can't find a match (in other words, the company code is not included in the mapping table), then you should replace the null value returned by the lookup component by the original company_code.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246960 More actions · Answer 9

da-zero (4/14/2010)
yingchai (4/13/2010)
Hi Phil,
I had some problems here after applying the Lookup Transformation when I want to do partial mapping.
That's why I talked about redirecting or ignoring the error output of your lookup component. If the lookup can't find a match (in other words, the company code is not included in the mapping table), then you should replace the null value returned by the lookup component by the original company_code.

Yep - perfect advice.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yingchai SSCommitted Points: 1669 More actions · Answer 10

Hi da-zero,

I had tried the "redirect row" and "ignore failure" approach but still my IDs didn't transform correctly. Please find my 2 result scenarios attached.

My ultimate goal is to load the existing product codes from my source file "as-it-is" and transform the null product code in my source file into "No_Product_Line". Kindly advise.

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

There is a better way of doing this which does not require a lookup table.

Add a derived column which converts NULLs to "No_Product_Line" and leaves non-NULL data as is, something like this:

IsNull(Product_Code)?"No_Product_Line":Product_Code

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

yingchai SSCommitted Points: 1669 More actions · Answer 12

Phil Parkin (4/17/2010)
There is a better way of doing this which does not require a lookup table.
Add a derived column which converts NULLs to "No_Product_Line" and leaves non-NULL data as is, something like this:
IsNull(Product_Code)?"No_Product_Line":Product_Code

Hi Phil,

I had successfully created the derived column but my fact table still stores NULL values after load. I'm not sure whether my set up is done correctly?

Please advise.

EDIT: Phil, I managed to solved the NULL product_code transformation by changing the expression to if else:

Product_Code== "" ? "No_Product_Line" : Product_Code

Now I have another issue. If I were to load a mixtures of original and transformed product code which is the best method to use? Can we use a combination of derived column and lookup? If yes, can you advise on how to transform the product code pattern below?

SOURCE_ID | DESTINATION_ID

-------------------------------

CMP-100 | CMP_100

CMP-200 | CMP_200

CMP-300 | CMP_300

CMP1000 | CMP1000

CMP2000 | CMP2000

CMP3000 | CMP3000

| No_Product_Line

Thanks!