November 14, 2007 at 9:53 pm
Hi there,
I have a database contains 500++ tables and stored proc. I wondered that with SSIS, is it possible to detect any changes made to a database such as new table created, new row added and pass only these newly-added information to an existing datawarehouse?
Can anyone tell me how or direct me to a good reference please? Your help will be greatly appreciate.
Regards,
Kai
November 15, 2007 at 5:42 am
You cannot point SSIS at a database and tell it to wait for any data or schema changes and then populate a data warehouse.
There is an SCD wizard that will compare data from a single table or query to a dimension table and handle the inserts and updates.
If you wanted something truly event-driven, you would need to use triggers (and possibly service broker deending on the situation).
As far as seeing schema changes, if you had a new table added, wouldn't you need to determine the appropriate structure for your data mart?
November 17, 2007 at 2:37 am
Hi Michael,
Thank you for your reply. I dont know what SCD means though. Can you tell me a bit please? I am new to SQL Server 2005. But have to build a DW for the company. Triggers on many tables are painful, i just want a short cut that's all.
Regards,
Kreecha
November 19, 2007 at 5:19 am
By SCD, I am referring to a Slowly Changing Dimension.
Before jumping in and trying to build a data warehouse, I would recommend you get some training or at least a book on the subject. Data warehousing can be very complicated. On top of that, the way data warehouses are implemented through Analysis Services can be tricky and needs to be done correctly to have any kind of good performance.
November 19, 2007 at 11:18 pm
I did have some trainings and produced DW but this time i am facing a huge db that's why i am trying to find shortcut.
November 20, 2007 at 6:51 am
Michael is right!
You have to SCD(Slowly Changing Dimension) to get your thing work. You wanted SSIS to detect the changes...........This is the only transformation that will help you. This is your short cut. Drag and drop SCD and double click to open it. Then define the table and it will also prompt you to define the business key(primary key most of the time) Then click next, there will be a pane on your right side. Click on that and point to your "changing" column or if you are expecting more changes, then click for more colums and then on the dropdown, choose changing. Then next and check for update if changing is detected and click finish. It will take at least 2 to 3 second for this to clear and now you will see 3 more tasks generated automatically by SCD. Now all you have to do is just double click on the destination OLE DB that was created by the SCD and point to your destination database.
Thats all. You are done. ;):cool:
[font="TimesNewRoman"] “I haven't failed, I've found 10,000 ways that don't work”........Thomas Alva Edison[/font]
November 20, 2007 at 10:06 am
500++ tables in a data warehouse is truely massive for a data warehouse (not for a OLTP database). I'd get someone to take a look over your shoulder at your schema to ensure that you're dealing with the optimal schema.
The SSIS Slowly Changing Dimension is a great transform. Caution though: you'll want to make sure your table layout is relatively fixed. Maintaining / updating this particular transform takes some effort and I wouldn't want to suggest you slap it together.
November 20, 2007 at 7:21 pm
Thank you for your suggestions. I am sorry i was not clear. English is not my first language.
500++ tables are not in a data warehouse, they are from a db server and produced from an external vendor. Here is the problem the vendor sometimes add new tables or rename tables without telling us. So my SSIS needs to know these changes for ETL process. For example, the vendor change or replicate 'tbInvoice' name to 'tbInvoice_2008'. Then my cube could load wrong data, right. I am too lazy to come and revise my project, with MSSQL 2005, i believe i can write script to identify those changes, is that correct?
I look into SCD and no doubt that it is a great transform.
Kreecha
November 21, 2007 at 5:03 am
Hi Kreecha,
Is it possible to have SSIS monitor data in tables that are renamed or created? The answer is "Yes". Is it optimal, no.
Based on my interpretation of what you said, I would build a staging database that sits between your vendor source and data warehouse databases. In the staging database I would set up schema-change notifications and perform some grouping of data. You an do both with SSIS.
I would use two packages. I'd first check to see if the schema has changed, and if it has I'd send some sort of notification and, at the start, I would not execute the second (Load-To-Stage) package if I detect a schema change.
Over time, you can add intelligence to both processes and automate more.
A third package (or collection of packages) would load from Stage to your data warehouse. This should rarely change.
Hope this helps,
Andy
Andy Leonard, Chief Data Engineer, Enterprise Data & Analytics
November 21, 2007 at 6:44 am
the vendor sometimes add new tables or rename tables without telling us. So my SSIS needs to know these changes for ETL process.
Hi Kreecha,
Since the vendor is making changes, you may want to look at implementing a few WMI Alerts that monitor DDL events in real time. Things like renaming, dropping, adding, truncating tables, etc can be captured as they happen and you can set up a response that will write to a table or tables. You can then use those tables to determine what has changed and create your queries accordingly.
If you think this would help here is the URL from BOL that will get you started on figuring out how the WMI Alerts work. There is one caveat with WMI Alerts and that is the server name can't be more than 14 characters. It's a bug I found and MS will have a hot fix for it in the next Cumulative Update (sometime in December)
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/b8c46db6-408b-484e-98f0-a8af3e7ec763.htm
Hope this helps
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
November 23, 2007 at 3:29 am
Hi Andy Leonard and MG,
Thank you for your replies. Hearing that schema-change notifications can be done via SSIS is such a great news. Andy, can you point me out how to do that with SSIS please? Just simple steps, tools like WMI alert that MG said or something like that? As i said i am new to SQL2005.
I want to learn as many detail of SSIS as possible also try alternative ways too.
Thank you very much for your suggestions.
Kreecha
February 3, 2012 at 2:56 pm
Hi Kreecha,
Did u received solution on how to load the 500+ tables with update/insert/delete? If yes, then could you please let me know, how did u worked out that.
Thanks and Regards,
Samit Shah
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply