October 6, 2010 at 11:04 am
Hi all,
I am building a solution in SQL Server 2008 using SSIS services. I need your expert advice on this. Here what we are doing:
We are building database whose data will come from three different systems. Some fields will be matching in each files coming from different systems. Data coming from System1 will have about 22 fields. Data coming from System2 will have about 30 fields. Data coming from system3 will have 20 fields. All these data will come in three different pipe delimited (|) text files.
System2 and System3 data will come every month. But System1 data will come every week.
For the database, I will create one master table containing all the fields from three systems with multiple primary keys to make record unique.
Using SSIS pakcage, I will get the data from System1 file and put it in Master table. This will be done every week. Every month I will get the data from two other systems and would like to match the data with current master table data and merge if match. If does not match, put all the data in different table.
My question to you is to achieve this match and merge data, I have to have same unique values in each three system? Because with our current data, there are two fields that our business has defined to be criteria for matching. But with our sample data I have received from at least one system, values for these fields are duplicated.
My understanding is that I have to have same unique values in all three files to be able to match and update the record in database.
Please provide your expert advise and let me know if my understanding is correct or not.
VP.
October 6, 2010 at 11:10 am
To be able to match - it would be easiest if the keys from each system matched. Whatever you define your Primary Key as, it needs to be fields that are available in each of the systems.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 6, 2010 at 11:16 am
An exact match works nicely with a look up and this can be configured to your "key" fields.
If you are using Enterprise edition you have the option of a Fuzzy Lookup which you can configure a confidence level on and direct the rows to definate match, possible match and not a match destinations depending on the confidence level.
October 12, 2010 at 10:35 am
Find out why you have duplicates in your sample data. Is it because you of bad sample date? Will the real production data also have duplicates?
The answers to these questions could determine which path you must follow.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply