Metadata storage and intelligent use

I am contemplating moving a huge integration project (hundreds of different medical practice management systems) from a competitor's dedicated data integration tool to SSIS. One of the main questions I have about SSIS regards it's capability to maintain - and even more importantly - intelligently use metadata regarding source and target data in the integration processes.

One significant example:
There is an ETL tool that I am familiar with that allows the user to indicate the uniqueness keys of any target data structures. This is huge because now the tool has the capability to determine if a source data structure that is a candidate for an insertion represents a new row or an existing row compared to the target, and with that knowledge the GUI allows the user to tell the process whether it should automatically update a row when it already exists in the target, ignore any changed data, etc.- with one checkbox! NO CODE NEEDED. Anyone care to visualize how much code one would have to write to do this for many tables each with many columns by hand

So the obvious question is: how much of this type of metadata can SSIS maintain and use intelligently, as in the above example

Thanks
Ken

Answer this question

Metadata storage and intelligent use

popeyesailor
SOLONDE' warehouse workbench.
sansara13
Interestingly enough, the tool that was selected for our project was not the one I recommended (I recommended warehouse workbench). The product chosen was a competing product, Pervasive Data Integrator (although we are seriously now evaluating SSIS).

Having said that, the reason I, as the ETL technical lead, recommended warehouse workbench was because it has the type of functionality such as I described previously in this thread. The reason Pervasive's product was chosen over Solonde's was based more on non-technical grounds- U.S. market share, gartner reviews, existing presence in the Health Care IT community , etc.

Warehouse workbench won none of those non-technical factors in the review process.
Wenyang
The question was admittedly general, which is why I provided the one example. I probably should provide a laundry list of other examples of specific integration scenarios where support for meta-data reduces the effort required to create and maintain complex integrations.

The functionality I described - using the tool that I am familiar with - provides a very simple interface so that the target row for any transformation can be assigned the "insert or update" property based on the keys, not just dimensional data structures. I will research the wizard.

Thanks Donald!
T_Cavallari

How are you getting on with Solonde It's an interesting application that a lot of people have played with, but you are first person I have come across who may be using it for real. Are you Is it good

Donald
softketeers
Out of interest Ken, what is the ETL tool that you are familiar with

-Jamie
Dwatney

The specific scenario is a good one - more on that later.

The general question is one of those "how long is a piece of string" questions, and I'm not sure if we can really compare apples with apples. We have a oodles of metadata support and other tools have bucketloads. :-)

For your case, SSIS has a couple of ways of doing this. The Slowly Changing Dimension wizard makes this scenario very easy. You identify the uniqueness key, walk through the wizard, and the entire process can be created and managed very easily.

Donald
Metadata storage and intelligent use

Answer this question