May 16, 2012 at 2:52 pm
I have 3 table. Source table, dim table and load table..
I have a source table .. In the source table there is Createddate columns. In the Dim table I have 2 more column ( LoadID, StageId - stageID as identity column. ) Like if the data is being loaded today then that date column will contain todays date. All these loading I have to incrementally everyday. Another Load table has 3 column ( LoadID,StartLoadtime,EndLoadtime ). when I inserrt new data then it should effect both table . ( means LoadID will be added in Dim table. ) and as well in Loadtable with LoadId time...
I will try to explain the scenario. Today I am loading the data from the source which has 10 rows into the Dim tables. All the rows will be inserted with the CreatedDate column of todays date for the 10 rows. Next day when I load 10 more rows , only new 10 rows will be inserted into the dimtable with the loadID and date column having Next day's date. and also store in Load table.
i want to perform this task in SSIS by creating package..
How can i perform this action ?
May 16, 2012 at 3:45 pm
can you post the Create table script for the 3 tables (DDL) and some sample data you would be loading. it most likely can be done by SSIS but depending on what you are trying to accomplish it may or may not be the best method. with out more information it is hard to say.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
May 16, 2012 at 5:07 pm
This is my LoadDetails table and value i enter in it..
Create Table DW_LoadDetails_Ronak
(
LoadID int identity(1,1),
LoadStartTime datetime,
LoadEndtime datetime,
Comments varchar(200)
)
-- 2. insert values
insert into DW_LoadDetails_Ronak (Comments)
values ('Load Status')
insert into DW_LoadDetails_Ronak (Comments)
values ('Load Status')
insert into DW_LoadDetails_Ronak (Comments)
values ('Test Load')
after adding values i am getting something like this.
LoadiD starttime EndTime status
12012-05-11 15:31:47.8402012-05-11 15:31:47.840Load Status
22012-05-11 15:31:47.8732012-05-11 15:31:47.873Load Status
32012-05-11 15:31:47.8732012-05-11 15:31:47.873Test Load
42012-05-16 15:25:14.9672012-05-16 15:25:14.967Load eBid_User
62012-05-16 16:45:10.5902012-05-16 16:45:10.590Load eBid_User
then join two table to get this for column..
select St.StatusID,
ST.StatusDescription,
ST.StatusCatID,
SC.StatusCatDescription
from TBL_eBid_Status as ST
join TBL_eBid_StatusCatagory as SC on ST.StatusCatID=SC.StatusCatID
and the values i am getting after generate this is as below..
statusid statusdescription statuscatID statuscatdescription
1 Active 1 Notes
2 Inactive 1 Notes
3 Archive 1 Notes
Now created dimstatus table with all 4 column and adding loadid and stageId
Create table DW_dimStatus
(
StatusID int,
StatusDescription varchar(50),
StatusCatID int,
StatusCatdescription varchar(50),
LoadID int,
StageID int identity(1,1)
)
so there will be 2 more column name as LoadID and StateID..but there will be no value in it.
stageID is identity so it will get the number automatic.
now when i add values in DW_dimStatus table what ever values i am getting after generating join query .
tat all values will be added in this and with stageID as it is identity. and loadid will be null. now i want to replave null with other value .. and every time i will add new value in this it will automatic update in my load id.
May 17, 2012 at 11:02 am
In SSIS, you can use a MERGE JOIN transformation task, I believe. Or you can use Slowly Changing Dimensions. It depends on your business rules, etc.
Check them out in Books Online and see what each will do for you.
May 17, 2012 at 11:08 am
Oh. Thank you So Much miss.
May 17, 2012 at 11:19 am
You are welcome.
Just an FYI, though. You posted your question in the wrong group. Try General under 2008 or Business Intelligence under 2005 (which covers both 2k5 & 2k8).
May 17, 2012 at 11:23 am
Update records in the actual ebidmanager (not dim)tables (dont forget to update LastUpdateddate for the rows you updated). ( only update the recs that you inserted on Monday)
-- Identify updated records in the source tables (original ebidmanager tbls) using lastupdateddate.
-- Initiate Load using SSIS Pkg
-- Identify records in the dim tables that are updated in the source tables (hint: using id's of the records updated in sourcetbls)
-- Update the records in the dim tables with new data accordingly. (dont forget to update the loadid to the latest loadid )
this is something i was trying to achieve... but still working on it and trying to complete this task by creating package in SSIS. i have over all idea what i am trying to do but not logically... That will be great if u can explain in details.
Thanks you,
Ronak Patel
May 17, 2012 at 11:46 am
Books Online has all the details on how to use the transformation tasks that I told you about. If you want to use SSIS, refer to that and the Microsoft tutorials for more information. The tasks don't use complete T-SQL queries.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply