Flexible ETL package

Question

Flexible ETL package

sql_learner29

SSCrazy Eights

Points: 9107
More actions
July 1, 2010 at 9:31 am

#141840

Hi all,
I have a package to be designed in a flexible way, actually I have a source in which I have some column which I have to eliminate in the select query from the source, but that column may exceed values in future and I may have to modify my package to exclude more values from that column.
I can see two ways to do it:
1. put all the values that you want to exclude in a stage lookup table and use it in ETL process may be...
2. or write a select query in OLE DB SRC and exclude in where clause..
for ex: select * from table 1 where col1 not in('a','b','c')
This is what i can think of...Is there any other better way to make the package more flexible to modify in future...
Thank for your help.
[font="Comic Sans MS"]
---------------------------------------------------
Thanks [/font]

Viewing 7 posts - 1 through 6 (of 6 total)

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

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 1

priya__ (7/1/2010)
Hi all,
I have a package to be designed in a flexible way, actually I have a source in which I have some column which I have to eliminate in the select query from the source, but that column may exceed values in future and I may have to modify my package to exclude more values from that column.
I can see two ways to do it:
1. put all the values that you want to exclude in a stage lookup table and use it in ETL process may be...
2. or write a select query in OLE DB SRC and exclude in where clause..
for ex: select * from table 1 where col1 not in('a','b','c')
This is what i can think of...Is there any other better way to make the package more flexible to modify in future...
Thank for your help.

Combination of both... import into a staging table and have the list of things you want to exclude in a lookup table. Use and exclusive type of join (WHERE NOT EXISTS would probably be the best here) to form the query between the two.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 2

Thanks for your reply..

So you are saying that the better way to exclude is to use lookup tables to exclude instead of doing it on direct OLE db src query?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

I would say so, yes, otherwise you will need to change your code every time your exclusion list changes.

John

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 4

John Mitchell-245523 (7/2/2010)
I would say so, yes, otherwise you will need to change your code every time your exclusion list changes.
John

Z'actly.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sql_learner29 SSCrazy Eights Points: 9107 More actions · Answer 5

Thanks for your response,

I have not used NOT EXISTS before this...as my lookup table is to exclude values in a column,I will only have all the values that are to be excluded for a cloumn in the lookup tables so where can use the NOT EXISTS to exclude them in my query...

also I have two columns so should I put them in same lookup table or separate lookup tables?

I thought I will write select * from lookup table and then will ignore the matched records...

how to do this using NOT EXIST?

[font="Comic Sans MS"]
---------------------------------------------------

Thanks [/font]

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

I'm finding this difficult to visualise. Please will you provide table DDL, sample data and what you have tried so far so that we can picture it more easily.

Thanks

John