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

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).

Google.GData.Client

Google.GData.Extensions

Google.GData.Spreadsheets

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.

image

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.

image

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. 

image

Add the following variables to your package:

strUserName

Your Google account user name

strPassword

Your Google account password

strKey

The spreadsheet key

image 

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

image

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.

image 

Add the following columns to the output:

Field

DataType

TimeStamp

DT_DBTIMESTAMP

FirstName

DT_STR(50)

LastName

DT_STR(50)

Age

DT_14

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.

image 

Your script component should now look something like this:

image

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()

       
MyBase.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

    Public
Overrides Sub PostExecute()

       
MyBase.PostExecute() 

   
End Sub


    Public
Overrides Sub CreateNewOutputRows() 

       
Dim objRow As ListEntry

       
For Each objRow In objListFeed.Entries

           
With Output0Buffer

                .AddRow
()

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

                .FirstName
= objRow.Elements.Item(1).Value

                .LastName
= objRow.Elements.Item(2).Value

                .Age
= objRow.Elements.Item(3).Value

           
End With

        Next


       
Output0Buffer.EndOfRowset()

   
End Sub

End
Class


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:



http://code.google.com/apis/spreadsheets/data/2.0/developers_guide_dotnet.html#RetrievingListFeeds

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.