March 21, 2007 at 9:00 pm
Hi guys,
There is any way we can automate ETL process?
Let me give you brief information.
I retrieve data from the operational database and apply aggregate function to that data and than I load data (with dimension) into the DWH. so this is like repetitive task over and over.
Recently, I read about Real-Time data warehouse so, that company achieve great result from that. In that article they fed real-time data into the warehouse.
Can you advice me, with the MS SQL 2005 BI can we achieve this?
Thanks you,
Mit
March 23, 2007 at 12:17 pm
If it's purely along the lines of:
Select data
Aggregate
Insert
You may be able to just link the servers and set up a couple of stored procs - one to pull the data, one to accept that input and aggregate/insert it.
You could also pull the data in through Integration Services (use a query as your source if the table won't work or look up some examples of Joins through the SSIS tools). Perform your aggregates (once again, either with a query or by using the built-in tools).
Point the data to a Target/Destination OLEDB object to populate the warehouse tables.
I think there's a pretty good book or two out on using SQL 2005 tools to populate warehouses, and the resources for real-time population of a warehouse are growing. I can't tell from reading your article whether you're using Analysis Services or you are just populating DB Tables with the data. Both of those would have different approaches.
-Pete
March 23, 2007 at 4:34 pm
Thanks Pete for the reply.
I am more interested in Automating the ETL process through the integration services.
I have a couple of questions:
1.) How can the SSIS package be invoked automatically.
2.) I want that the SSIS package should execute after every 30 mins. Is there a way of doing the above mentioned tasks automatically (without the user intiation).
Thanks,
Mit
March 23, 2007 at 5:49 pm
You'd use SQL Agent to schedule the package to run. You may have to save it with some form of encryption in order to save passwords, but I'm not 100% on that. I know that some people have complained of issues getting an SSIS package to run in SQL Agent, but usually configuration issues. I think if you save your package in msdb, you should be okay. (Save a copy of the package as - SSIS Store for packages or SQL Server for Maintenance IIRC.)
Schedule that in SQL Agent with the SSIS package type (at least should be something like that).
Once you've got it in SQL Agent, running every 30 minutes isn't all that hard to do - just set the schedule to run from start time to end time every 30 minutes.
March 23, 2007 at 9:00 pm
Dear Peter,
Thanks for valuable information.
Do you have any material OR link for this ETL process? If you do please send me...
Once again thanks for your informative reply.
Mit
March 26, 2007 at 10:34 am
Probably the best I can offer is to do some searching. There are two pretty good SSIS books out right now. There's http://www.sqlis.com. Jamie Thompson keeps a lot of us up to date on his blog site over @ Chonchango (don't remember exact address). I haven't really played much with the automated side of things, but have written quite a few SSIS packages that I run interactively on a regular basis (tweaking parameters/variables, etc).
-Pete
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply