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

Add another Execute SQL task into the Sequence container and rename it Get
CleanTempMaxID from the local table . Configure the connection to your source
database and table. For me, it's a local default instance of SQL Server hosting the
WeatherData database and the dbo.CleanTemperature table. I use the following
T-SQL to extract the current maximum value from the table, configuring a single row
result set to push this value into the CleanTempLocalMaxID SSIS variable (Int32
data type).
Select Max(ID) As CleanTempLocalMaxID
From dbo.CleanTemperature
Add a Data Flow task outside the SEQ Pre-Load Operations Sequence container
and rename it Load Windows Azure SQL Database . Connect an OnSuccess pre-
cedence constraint between the Sequence container and the Data Flow task. Open the
Data Flow Task Editor and add an OLE DB source adapter. Connect the OLE DB
source adapter to a local source database you wish to load in the cloud and write a
query to pull the latest data from the desired table. In my case, I am pulling data from
my dbo.CleanTemperature table. To accomplish the load, I use the source query
shown in Listing 14-3 .
Listing 14-3 . WeatherData Source Query
SELECT ID
,MeasDateTime
,MinT
,MaxT
,AvgT
,MinH
,MaxH
,AvgH
,ComfortZone
,MinDP
,MaxDP
,AvgDP
,MinHI
,MaxHI
,AvgHI
,LoadDate