Updating Data Warehouse

Question

Updating Data Warehouse

carlend92

Valued Member

Points: 73
More actions
July 26, 2017 at 7:08 am

#402362

Hello guys!
I ask for your help as a "novice" in the branch.I have this kind of problem ... I created a very simple data warehouse from a DB.To populate the tables, I created an Integration Services process that merely makes a "copy" of the data from the source DB and populates the DW.Since the source DB is populated by a SW ... I need to update my DW timely.I looked a bit on the internet but between stored procedures etc. I do not get out of it anymore because I do not know if the road is the right one.In sects:DB_SOURCE.TABLE1 -> SSIS Process -> DW.TABLE1If it stops and restarts the SSIS process it clearly makes a copy of the entire table duplicating the data and is NOT what I want.The aim would be to:NEW DATA LOADING ON DB_ORIGINE.TABELLA1 -> "I DON'T KNOW" -> UPDATE OF "NEW" rows on DW.TABLE1.
I need to use a stored procedures? SSIS Process?
Thanks all for reply

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 247180 More actions · Answer 1

Your attachment / image is blank.
It's not clear what you are asking for ... perhaps the image will make it clear, but we might need more information.

carlend92 Valued Member Points: 73 More actions · Answer 2

I Have:
SOURCE DB TABLE1
Col1 Col2 Col3 Col4 Col5
Test Test Test Test Test

I create a DW with an integration Services process and now i have
DW.TABLE1
Col1 Col2 Col3
Test Test Test
That is a "Copy" of SOURCE DB TABLE1

If i insert into SOURCE DB TABLE1 e new rows
Col1 Col2 Col3
Test2 Test2 Test2

How can i UPDATE the DW.Table1 and schedule it?

I hope I have explained myself better

Phil Parkin SSC Guru Points: 247180 More actions · Answer 3

Does your source table have a primary key?
Does it have DateCreated and DateModified columns?

carlend92 Valued Member Points: 73 More actions · Answer 4

Does your source table have a primary key? Yes, Col1 for example it's primary key
Does it have DateCreated and DateModified columns? No

Phil Parkin SSC Guru Points: 247180 More actions · Answer 5

carlend92 - Wednesday, July 26, 2017 7:31 AM
Does your source table have a primary key? Yes, Col1 for example it's primary key
Does it have DateCreated and DateModified columns? No

If you cannot add DateCreated / DateModified columns to your source table, it's probably simplest to make your job do this:
1) Truncate target table
2) Reload from source
because it is not easy to detect only new and modified source rows (for an incremental load) without this information.

carlend92 Valued Member Points: 73 More actions · Answer 6

so how can i do this? stored procedure?
If yes what's the best way and code for do that?
Thank you and apologize for these questions but I am just a novice 😛

ZZartin SSC-Dedicated Points: 30921 More actions · Answer 7

If this is an SSIS package just add a SQL step to truncate the target table before loading it.

carlend92 Valued Member Points: 73 More actions · Answer 8

Oh sound's great! I just added a OLE DB comand with TRUNCATE TABLE between DB.Table1 and DW.Table1 and it work 😛
Thanks 😉

carlend92 Valued Member Points: 73 More actions · Answer 9

I just need to understand how to automatically schedule (every day for example) the integration services process

Phil Parkin SSC Guru Points: 247180 More actions · Answer 10

carlend92 - Wednesday, July 26, 2017 8:26 AM
Oh sound's great! I just added a OLE DB comand with TRUNCATE TABLE between DB.Table1 and DW.Table1 and it work 😛
Thanks 😉

This is not quite how I would do it. In your SSIS package
1) Step 1 is an ExecuteSQL task to perform the TRUNCATE
2) Step 2 is your data flow from DB to DW.

Phil Parkin SSC Guru Points: 247180 More actions · Answer 11

carlend92 - Wednesday, July 26, 2017 8:30 AM
I just need to understand how to automatically schedule (every day for example) the integration services process

Add a SQL Agent task to do this for you.