Well here we are, I promised in my last post that I would let you know how my studying was going on and also share with everyone anything I find of interest in the course of my studies.
I have now done the first 4 chapters of the book which are all focused on SSIS before moving onto both the areas of SSAS and SSRS required by the syllabus. My overall impression of the book is very good it is split down into a series of lessons within each chapter each with a time estimate (Which I think are a little short myself) followed by an exercise or two to allow you to practice what you have learned.
I am not going to replay the entire book here but it has highlighted a few good points which are well worth I think sharing. The first is a couple of short comings with the book, well not so much short comings but more a limitation of the book being a printed object.
The first of these is the link produced in the book to the AdventureWorks2008 database installs which the book is based on. Unfortunately Microsoft have seen it fit to move the database install and it can now be found at http://msftdbprodsamples.codeplex.com/releases/view/37109 don't be tempted to use the R2 one as I have done a comparison and they are suitably different which I think may catch you out later.
The only other issue I have had is with the Exercise 1 in Chapter 3 which makes use of a pre-written SSIS package that imports a series of Excel spreadsheets into a Table in SQL. The first issue is that the table they are imported into doesn't actually exist. Maybe this is an omission in the book or the fact that it used to exist in the sample database but no longer does. After a bit of digging around in the SSIS package I managed to produce my own table which seems to work fine.
The script for which is shown below:-
USE [AdventureWorks2008]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[stg_Customer](
[FirstName] [nvarchar](255) NOT NULL,
[LastName] [nvarchar](255) NOT NULL,
[FullName] [nvarchar](511) NOT NULL
[Address1] [nvarchar](255) NOT NULL,
[City] [nvarchar](255) NOT NULL,
[State] [nvarchar](255) NOT NULL,
[Phone] [nvarchar](255) NOT NULL,
[Age] [int] NULL,
[FileName] [nvarchar](255) NOT NULL,
[LoadDateTime] [datetime] NOT NULL,
) ON [PRIMARY]
GO
This seems to fix the missing table issue. You will also find that when you run it using the sample Excel spreadsheets you get an error in the Data Flow shape, more precisely with the Data Conversion. There is a couple of ways to fix this but the simplest is to edit the shape and select Configure Error Output and set both to ignore failure.
(See Image1 In Attached Document)
This should fix it; if you want to go the step further you could add additional code to replace the resulting Null column. I used the existing Derived Column shape and added an extra derivation to cope with the Nulls. ISNULL(New_Age)?0:New_Age
(See Image2 In Attached Document)
That's it for issues I have found so far, I will of course mention any more I find as I find them in later posts.
So now onto the items I think the book does really well. The first is it explains a very good pattern for use with configuration settings allowing you to store your entire configuration details in SQL server and then reference them indirectly. I have read a few times of this technique but the book lays it out in real simple terms.
If you want to know more about this take a look at Jamie Thompson's very good blog post on the subject: - http://consultingblogs.emc.com/jamiethomson/archive/2005/11/02/SSIS_3A00_-Indirect-configurations-ROCK_2100_.aspx as he explains it a lot better than I ever could.
The other thing I think the book does well is relate the ideas it is describing to real world situations, the book is full of real world examples from the authors along with at the end of each chapter a case study which you get the chance to solve before reading the answer provided by the Authors.
A good example of this is the suggested UPSERT pattern which draws in data from a source file and current database table and then uses a Merge shape (Left Outer Join) in the Data flow to generate two flows, one of matched records and one of new records, the new records are directly inserted into the database and the matched records are passed in this instance to a staging table to be used as a data source for a subsequent update statement.
(See Image3 In Attached Document)
As I progress through the book I will blog further and try and share anything I find with others. Needless to say I am learning a lot here and sharing what I think would be of interest to others.