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

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


Once downloaded, run the Google Data API Setup(, 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).




You can just drag and drop them from :\Program Files\Google\Google Data API SDK\Redist  into C:\Windows\assembly.  However I had a few issues when I tried to do this.  I’m using windows vista and had User Control Access switched on, so I switched this off, restarted, and after that had no problems.  There is an excellent article about this here:

Now that we’ve installed and registered everything, we can get on with accessing our data.

Creating the spreadsheet

For this example I created a simple Google spreadsheet from a form.  There is an excellent tutorial on YouTube.  The final spreadsheet I created had a TimeStamp, FirstName, LastName and Age field.


An important piece of information that you need to remember is the spreadsheet key, you can get this from the URL, you’ll need this in order to access the spreadsheet later.


Once the spreadsheet is created and populated with some data then its time for us to build a custom source component to access this data.

Building the source component

Open BIDS and start a new project, rename the package Google.dtsx. and add a Data Flow Task. 


Add the following variables to your package:


Your Google account user name


Your Google account password


The spreadsheet key


Inside the Data Flow Task add a script component, choose Source as the component type.


Inside the script component we are going to setup the columns that are going to be output.  Go to the Inputs and Outputs tab, Select the Output node in the treeview control and click Add Column.


Add the following columns to the output:











Now you can go back to the Script tab Change the Script Language to Microsoft Visual Basic 2008, On the ReadOnlyVariables property select the variable that we just setup.


Your script component should now look something like this:


With all this in place we are now ready to edit the script component.  Click Edit Script, and paste the following code:

Imports System 
Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Google.GData.Client

Imports Google.GData.Extensions

Imports Google.GData.Spreadsheets

<Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute> _

<CLSCompliant(False)> _

Public Class ScriptMain

    Inherits UserComponent

Dim objListFeed As ListFeed

Public Overrides Sub PreExecute()


Dim objService As SpreadsheetsService
Dim objWorkSheetQuery As WorksheetQuery

Dim objWorkSheetFeed As WorksheetFeed
Dim objWorkSheet As WorksheetEntry

Dim objListFeedLink As AtomLink
Dim objListQuery As ListQuery

Dim bt(0) As Byte

'Create a connection to the google account

objService = New SpreadsheetsService("exampleCo-exampleApp-1")

Me.Log(Variables.strPassword, 0, bt)

Me.Log(Variables.strUserName, 0, bt)

objService.setUserCredentials(Variables.strUserName, Variables.strPassword)

Me.Log("Service: " + Variables.strUserName, 0, bt)

'Connect to a specific spreadsheet

objWorkSheetQuery = New WorksheetQuery(Variables.strKey, "private", "full")

objWorkSheetFeed = objService.Query(objWorkSheetQuery)

objWorkSheet = objWorkSheetFeed.Entries(0)

Me.Log("Spreadsheet: " + objWorkSheet.Title.Text.ToString, 0, bt)

'Get a list feed of all the rows in the spreadsheet

objListFeedLink = objWorkSheet.Links.FindService(GDataSpreadsheetsNameTable.ListRel, Nothing)

objListQuery = New ListQuery(objListFeedLink.HRef.ToString())     objListFeed = objService.Query(objListQuery)

Me.Log("ListFeed: " + objListFeed.Feed.ToString, 0, bt)

End Sub

Overrides Sub PostExecute()


End Sub

Overrides Sub CreateNewOutputRows() 

Dim objRow As ListEntry

For Each objRow In objListFeed.Entries

With Output0Buffer


.TimeStamp = objRow.Elements.Item(0).Value

= objRow.Elements.Item(1).Value

= objRow.Elements.Item(2).Value

= objRow.Elements.Item(3).Value

End With



End Sub


Code Exaplined:

The mainstay of the work is done in the PreExecute method.

First I created a service object that uses the username and password to connect to a Google account.

Then we connect to a specific spreadsheet, using the spreadsheet key

Once connected to the spreadsheet, we create a list feed of all the rows in the spreadsheet.

The list feed then gets used in the CreateNewOutPutRows method, we iterate across all the rows in the feed and pass each row out as a new buffer from the component.

For more information about the Google API you can go to the following place:


Converting Delimited Columns Into Rows

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

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.