Posts

Exam 70-443 – Overview

  In the following series of posts, I’m going to be adding my study notes and examples to help anyone trying to pass exam MCTS 70-443 Microsoft SQL Server 2008, Database Development Some resources to start with: Exam Overview: http://www.microsoft.com/learning/en/us/Exam.aspx?ID=70-433&locale=en-us#tab1 Database Developer Info Centre: http://msdn.microsoft.com/en-us/library/ms191267.aspx Tools for Performance Monitoring: http://msdn.microsoft.com/en-us/library/ms179428.aspx

SSIS - Google Spreadsheets Data Source

Image
Download Source Files: Here In this blog entry I’m going to show you how to access data from a Google spreadsheet in SSIS using the Google Data API. I’m really into  Google Spreadsheets, especially the newest release which allows for a lot of collaboration.  One of the great things about Google spreadsheets is the ability to create forms that you can Email to other or even embed in your blog. However for me there is one limitation, how to get your data out of those spreadsheets and into other applications.  Well in true Google fashion they’ve built an API that lets you access and manipulate Google Spreadsheets.  Installing the Google API Firstly you need to download and install the Google Data API SDK http://code.google.com/p/google-gdata/downloads/list Once downloaded, run the Google Data API Setup(1.4.0.2).msi, this will install the dll’s  in C:\Program Files\Google\Google Data API SDK\Redist you need to register the following dll’s in the GAC (C:\Windows\assembly). G

Converting Delimited Columns Into Rows

Image
Download Source Files: Here I’ve seen a lot of post recently asking how can I search within a field of delimited values or join to a list of delimited values.  The truth is its very difficult.  The best approach is to turn that list of values from a rows into a columns. In this post I’m going to show you a table valued udf that will do that for you.

Quick Tip 005 – SSMS Using Templates

Image
I often find that not many people truly harness the power of SQL Server Management Studio (SSMS).  In the next few quick tips, I’m going to highlight some nice functionality that can help in your everyday use of SSMS. In this tip, I’m going to talk about Templates, how to use them, how to create your own and where on your system they are being stored.

Quick Tip 004 - SQL Row Constructors

Download Source Files: Here Another excellent addition to SQL Server 2008 was the introduction of the row or table constructors.  In this quick tip I’m going to illustrate how to use the row constructor inside INSERT and SELECT statements.

SCD Using MERGE and Table Data Types in SSIS - 2 of 2

Image
Download Source Files: Here As promised in my first post , I’ve now put together an new example of how to use the the SQL MERGE statement, along with new TABLE Data Type to upsert a dimension table in a data warehouse completely within data flow.  In the previous example I used the control flow along with and recordset object to illustrate how to pass the data into a stored proc, however the call to the stored procedure was in the control flow. In this post I will use a script destination component to call the stored proc.

Generate SSRS Reports in SSIS – Control Flow – 2 of 4

Image
Download Source Code: Here In the previous post Generate SSRS Reports in SSIS – The Report – 1 of 4 we developed an SSRS report that we will now call from within our SSIS package.  We will also continue to use the same solution files as we did in the first post.