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

,LowBatt
,SensorID
FROM dbo.CleanTemperature
WHERE ID Between ? And ?
Click the Parameters button and map Parameter0 and Parameter1 to the
AWCleanLoadMaxID and CleanTempLocalMaxID variables as shown in Figure
14-2 .
Figure 14-2 . Mapping the SQLAzureCleanLoadMaxID variable to Parameter0
The question marks in the source query shown in Listing 14-3 are replaced with the
values stored in the respective mapped variables. This query will only return rows
where the ID is greater than the value stored in the cloud. Why do we grab the maxim-
um ID from the source table before the load? In a word, latency. In the WeatherData
database, the latency is minimal. But think about loading highly active sys-
tems—latency can be an issue. For example, suppose several transactions per second
are entering the source table and it takes a few seconds to load the destination. If we
wait until the load is complete to capture the source table's Max ID value, that value
will likely include data we didn't load. The technical term for that is “bad.” So we
design the package to grab the Max ID value before the load starts and only load rows
between the last ID loaded into MASD and the Max ID value captured at the start of
the SSIS package. And we never miss a row.
Returning to the demo package, add an ADO.Net destination adapter and rename it
Windows Azure SQL Database ADO NET Destination . Connect a data flow path
from the OLE DB source adapter to the ADO.Net destination adapter. Why an
ADO.Net destination? MASD allows only ADO.Net and ODBC connections—there is
no support at the time of this writing for OLE DB connections to MASDs.