Data Value Mapping Question
-
Hi all,
First, I am newbie in SSIS, so excuse me in advance if my question is obvious and I miss something …
My question is how you mange fixed value data mapping in SSIS transformations.
In order to be more clear … suppose that we have nomenclature tables in several system. Suppose also that nomenclature tables business meaning are one and the same; columns format in both systems are the same “Code” and “Description” … but the values vary between the system.- “Data SRC DB 1”.“Table 1” with columns ”Code” and “Description” with value ‘001’ and ‘Description 1’
- “Data SRC DB 2”.“Table 3” with columns ”Code” and “Description” with value ‘001’ and ‘Description 2’
What we need to do is to consolidate these tables into one nomenclature table and map values to target columns “Code”, “Description” : ‘1’, ‘Description 1’ ; ‘2’, ‘Description 2’
Drive column and Data Conversion with some expression is the obvious approach, but this way everything will remain in the SSIS Package Code which is accessible only to developers. Also if there is new value in the source we have to rebuild the package. So we want to based our solution on mapping table like that:
A complicate factor is that there are not only one pair of nomenclatures .. there are a lot more nomenclatures and data types between different pairs of nomens are different.
I suppose that this is common issue when building DWH, but I cannot imagine at the moment easy way to implement this.
Please share your thoughts and how you manage this.Thanks in advance.
Orlin - orlin.stoyanov - Tuesday, May 8, 2018 2:40 AMHi all,
First, I am newbie in SSIS, so excuse me in advance if my question is obvious and I miss something …
My question is how you mange fixed value data mapping in SSIS transformations.
In order to be more clear … suppose that we have nomenclature tables in several system. Suppose also that nomenclature tables business meaning are one and the same; columns format in both systems are the same “Code†and “Description†… but the values vary between the system.- “Data SRC DB 1â€.“Table 1†with columns â€Code†and “Description†with value ‘001’ and ‘Description 1’
- “Data SRC DB 2â€.“Table 3†with columns â€Code†and “Description†with value ‘001’ and ‘Description 2’
What we need to do is to consolidate these tables into one nomenclature table and map values to target columns “Codeâ€, “Description†: ‘1’, ‘Description 1’ ; ‘2’, ‘Description 2’
Drive column and Data Conversion with some expression is the obvious approach, but this way everything will remain in the SSIS Package Code which is accessible only to developers. Also if there is new value in the source we have to rebuild the package. So we want to based our solution on mapping table like that:
A complicate factor is that there are not only one pair of nomenclatures .. there are a lot more nomenclatures and data types between different pairs of nomens are different.
I suppose that this is common issue when building DWH, but I cannot imagine at the moment easy way to implement this.
Please share your thoughts and how you manage this.Thanks in advance.
OrlinTo be honest, your description of this problem makes it NOT sound like a mapping problem, but a data problem. Without understanding what the objective is, down to the most detailed level, it might be that I'm just missing your point. I suspect other may be equally confused by what they saw, given that I'm the first to respond and your post came in last week. Typically, for DWH situations, mapping is only useful when you have equivalent data, meaning that while the structure may be slightly different, the meaning of the presence of a row is the same. I'm not entirely sure that is the case here, and need more details.
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply