May 29, 2008 at 11:30 am
Hi....I have to create an SSIS package that loads data into a single table from 6 tables(selected filelds only)...Pls suggest me the ways I can do this?? any help would be appreciated...thankyou
May 29, 2008 at 11:44 am
You're going to have to provide some more details than that. Do these size tables have the same layout and you want to UNION them all? or are you joining them in some way? What system is serving up the source tables? the destination?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 29, 2008 at 11:52 am
Thanks for reply....Actually I have to populate a fact table from the dimension tables....fact table consists of datetime,userid,userlocation,officeid,officelocation,insurancename,providerid,groupname,groupid,accountnumber...I have all this data in 6 different tables...so, I have to select particular fields from 6 tables and load the data into the fact table...
May 29, 2008 at 12:03 pm
You still have not given much information.
Typically, a fact table would not be populated from dimension tables, it would be populated from data in the source system(s), so I am not sure if you are getting your data warehousing terms correct.
What we need to give any assistance is the structure of your 6 source tables and which fields you want from them. You probably have to do some joins of some sort. If the source tables are in the same database or six different databases, it will make a difference.
May 29, 2008 at 12:56 pm
ok ya...they r not dimension tables and fact tables...below are the details of the 6 source tables and the destination table...they are all in 2 different databases in the same server...
Here is the structure of the destination table: it consists of the following filelds: datetime,userid,userlocation,officeid,officelocation,insurancename,providerid,groupname,groupid,accountnumber, sessionid, eventid
I ve to populate this table from the other tables.
1st table consists of fields like --groupname, groupstatus, address1,address2,groupstatusid,phone,zip,comments
2nd table consists of--officeid,officelocation,city,address1,address2,zip,phone,fax,comments,federaltaxid,accesscode
3rd table consists of--firstname,lastname,providerid,providerstatusid,imageid,email,fax,phone,birthdtm,email
4th table consists of--userid,statusid,permissions,username,password,laslogindtm,userlocation
5th table consists of--serviceid,accountnumber,grouplocationid,acctnoenddate,mhsid,mhsname
6th table consists of--eventid,sessioguid,eventguid
hope I gave all the information...thankyou
May 30, 2008 at 12:13 am
Hi,
You would probably have to use an OLEDB source of your events table (assuming that your fact is an events fact), and then perform a lookup transformation against the other 5 "dimension" tables. Another one you may consider looking at is the merge join transformation, which will allow you to do inner, left, right or full outer joins against 2 OLE DB sources.
Some things to look out for
- Lookups fail when a row is not found. What are you going to do in such a case? It is commonplace for facts to sometimes arrive before dimensional rows (aka. early arriving facts). Are you going to reject, or are you going to add these to your dimensions if allowed?
- If your dimensions are not clean (surprisingly, the last 3 shops I worked for had such a scenario - duplicates on the natural business key), you may want to look at some deduplication component (there is a reaaaaally great SSIS duplication component that ships with the samples)
Good luck
~PD
May 30, 2008 at 12:51 pm
thanks a lot....let me try it...
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply