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

,AvgH smallint
,ComfortZone smallint
,MinDP real
,MaxDP real
,AvgDP real
,MinHI varchar(7)
,MaxHI varchar(7)
,AvgHI varchar(7)
,LoadDate datetime
,LowBatt bit
,SensorID int)
Once the cloud tables have been created, we can begin work on an SSIS loader.
Locally, create a new SSIS solution and project named CloudLoader . Rename the
default SSIS package SimpleCloudLoader.dtsx . Add a Sequence container and re-
name it SEQ Pre-Load Operations . Add an Execute SQL task by placing it into the
Sequence container and rename it Get AWCleanTempMaxID From AndyWeather .
Set the ResultSet property to Single Row and change the ConnectionType property to
ADO.Net. Create the ADO.Net connection manager using information from your
MASD account. To acquire the latest ID from the LoadMetrics table, I use the follow-
ing query.
IF NOT EXISTS
(
Select *
From dbo.LoadMetrics
Where MetricName = 'CleanTempMaxID'
)
BEGIN
INSERT INTO dbo.LoadMetrics (LoadDate,
MetricName,MetricIntValue)
VALUES (NULL,'CleanTempMaxID',NULL);
END
Select Coalesce(MetricIntValue, 0) As CleanTempMaxID
From dbo.LoadMetrics
Where MetricName = 'CleanTempMaxID'
On the Result Set page, I store the value in an SSIS variable of an Int32 data type
named AWCleanLoadMaxID.