Consolidating multiple lookups

In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)

Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.

Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

A second unit of re-use is a custom transform (not a script transform). THe script transform has "cut and paste" inheritence, which is no inheritance at all, so a change to one implementation forks it from the original, or both from both. That kind of re-use does not sounds as relevant to your scenario.

Nevertheless, I tend to the look at script transformations (for the most part) as proof of concept for custom transforms. If and once they are converted over to Custom transforms, then there is no more cut-and-paste inheritence (i.e. you can fix bugs once, not N times) Without taking advantage of the two re-usable objects (packages and custom components), forking will likely occur.|||

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.|||

Hubajube wrote:

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.

Correct. The only easy way to "send" to a subpackage is via staging tables (either database tables or flat files)