Loading the Cloud - SQL Server Integration Services Design Patterns - page 368

azure . Once MASD is set up and configured, create a database. In this database, cre-
ate a table named dbo.LoadMetrics using the T-SQL shown in Listing 14-1 .
Listing 14-1 . Creating the LoadMetrics Table
Create Table dbo.LoadMetrics
(ID int identity(1,1)
Constraint PK_LoadMetrics_ID Primary Key Clustered
,LoadDate datetime
Constraint DF_LoadMetrics_LoadDate Default(GetDate())
,MetricName varchar(25)
,MetricIntValue int)
The LoadMetrics table will hold the last ID loaded for each table in the cloud des-
tination. We will write this row once, and read and update it each load cycle. Accessing
this table in this manner is the simplest and least processor-intensive manner in which
to acquire the information we seek: the value of the last ID column loaded for a partic-
ular table. Why store this value in a table? Why not simply execute a Max(ID) select
statement on the data table? Again, MASD charges for reads and not writes. Billing
may change—it has in the past. What if we're billed according to cycles or execution
plans? You never know.
While connected to the MASD instance, create a table to hold your data. My data
table will hold temperature information collected from my weather station in Farm-
ville, Virginia. The table I use contains temperature- and humidity-related data and is
shown in Listing 14-2 .
Listing 14-2 . Creating the CleanTemperature Table
Create Table dbo.CleanTemperature
(ID int identity(1,1)
Constraint PK_Cleantemperature_ID Primary Key
Clustered
,MeasDateTime datetime
,MinT real
,MaxT real
,AvgT real
,MinH smallint
,MaxH smallint