June 20, 2010 at 11:28 am
Hello,
I am new to SSIS. I have to transfer data from OLTP to staging and from staging to Dimension tables in DW. After this whole process the staging tables should get truncated.
Could you please help me in this and tell me what control flow and what data flow transformations shall I use to accomplish this goal?
OLTP source is SQL tables and target is Dimention (SQL ) tables.
What I am thinking is (If I am correct), I will have to use Data Flow task, then I will take OLEDB Sorce to make connection with source data, then data should be loaded in Staging data..
Now when to generate surrogate key? this package will be made for incremental update.. So how would I find that there are new record in source table?
Please help me out here. I am new to SSIs and I have been assigned this task. Your help will be appreciated..Thanks in advance
June 23, 2010 at 11:56 am
Wow, that is quite a request..
I generally don't like to use a staging DB unless I need to, it depends a lot on the data and the transformations.
As far as surrogate keys I ALMOST ALWAYS use identity fields, I let the DB engine do the heavy lifting there.
As far as Incremental, I'd start with this: http://www.sqlservercentral.com/articles/62063/
CEWII
June 23, 2010 at 12:14 pm
Well, you need to have a data flow task which will load the source to staging. Map the columns and load into the staging schema.
In the second data flow, load the staging and use the SCD component to identify historical updates.
You need to use Execute SQL task to get the Max Surrogate key and store it in a variable. And then use a script component to increment the Skey for new inserts.
And then another Execute SQL task to truncate the staging.
I recommend http://www.expertmsbitraining.com
June 24, 2010 at 5:21 am
facticatech (6/23/2010)
You need to use Execute SQL task to get the Max Surrogate key and store it in a variable. And then use a script component to increment the Skey for new inserts.
Come again? Why don't you use an IDENTITY column for your surrogate key?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
July 6, 2010 at 7:10 am
Thanks all,
I could achieve my goal. I have loaded dimension successfully.
🙂
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply