SQL Newbie with some (hopefully) simple SSIS questions.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » SQL Newbie with some (hopefully) simple SSIS...

SQL Newbie with some (hopefully) simple SSIS questions.

Rate Topic

Display Mode

Topic Options

Author

Message

bobdobbs 77593

Posted Monday, December 31, 2012 4:51 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, January 08, 2013 5:47 PM
Points: 1, Visits: 12

Greetings. I hope someone can point me in the right direction as I'm new to SQL in general and SSIS in particular:

In a nutshell, I want to know the most straightforward way to gather and store data into a central db for reporting, trending, warehousing, etc. I've been researching this for a while, and it looks like SSIS with an SCD task might be the way to go. However, before I dig too deeply into it, I want to ask some experts about pitfalls.

We are a manufacturing facility and we have four machines on the production floor that record things like weights, downtime, changeovers, etc from the production line. These machines each run a proprietary software that writes to a local access 2007 database (I have budget to upgrade them to sql express if necessary).

I would like to compile this data centrally on our SQL server, and I would like it to be updated every 10-30 minutes. Ideally, I would like to be able to clean out the access databases periodically without having the deletes reflected in the sql server db.

We run SQL server 2008 r2 sp1.

Thanks for your time.

Bob

Post #1401526

Jason-299789

Posted Wednesday, January 02, 2013 1:22 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 838, Visits: 2,200

Hi Bob,

The SCD component in SSIS can have significant performance issues, especially where large data volumes are concerned and there are a lot of changes going on as the Type 1 attribute maintenance portion of the task runs a RBAR update.

I personally wouldnt recomend this for any dimension over about 10K rows, or where more than 5% of the rows change in anyone iteration.

Having said that there are a number of customised SSIS components out there, especially on Codeplex, some are better than others, the Todd McDermid component is not bad but it can be a bit of a blocking component. See http://toddmcdermid.blogspot.co.uk/2010/01/kimball-method-slowly-changing.html#!/2010/01/kimball-method-slowly-changing.html, the link to the component on codeplex is http://dimensionmergescd.codeplex.com/.

The down side is that you have to install it on each Server and development PC you aim to use.

Hope this helps.

Edit:

You might want to look at a setting up CDC enabled on the source tables so that you can just go in and get any changes since the last run.

Also with the 30 minute refresh in mind, I would recommend using TSQL SP's to do the grunt work as you will get better overall performance than with SSIS.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1401684

BarneyL

Posted Wednesday, January 02, 2013 1:54 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 3:07 AM
Points: 322, Visits: 647

It might be worth picking up a copy of 'The Microsoft Data Warehouse Toolkit' from the Kimball group. It will give you a good start on the theory side of things and options as to how best to make use of the SQL server tools available.

Post #1401691

Jason-299789

Posted Wednesday, January 02, 2013 2:28 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 10:57 AM
Points: 838, Visits: 2,200

BarneyL (1/2/2013)

+1 to that, I would also suggest the following from the same series.

ETL toook kit : http://www.amazon.co.uk/Data-Warehouse-ETL-Toolkit-Techniques/dp/0764567578/ref=sr_1_4?s=books&ie=UTF8&qid=1357118602&sr=1-4

Dimension Modelling : http://www.amazon.co.uk/Data-Warehouse-Toolkit-Complete-Dimensional/dp/0471200247/ref=sr_1_1?s=books&ie=UTF8&qid=1357118822&sr=1-1

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Post #1401705

« Prev Topic | Next Topic »

Permissions