Using SSIS to load data into ODM Sevilleta LTER Example

1. Using SSIS to load data into ODMSevilleta LTER Example David Tarboton Utah State University with Sevilleta Slides and data from Kristin Vanderbilt, Sevilleta LTER IM Study by: Joe Fargione, Nature Conservancy Scott Collins, UNM Will Pockman, UNM

2. The Sevilleta study: • multiple-factor global change experiment in an arid area at the boundary between shortgrass prairie and desert grassland that is undergoing shrub encroachment • simulates predicted future environmental conditions of increased nighttime temperatures, nitrogen deposition, and El Niño frequency (which increases winter precipitation by 50% at our field site).

3. Experimental Design

4. Measurements • Temperature is recorded every fifteen minutes at five locations in each plot: • Above the soil • Below the soil surface at depths of • 2 cm under grass • 7 cm under grass • 2 cm under bare areas • 7 cm under bare areas • A weather station located within the plot array collections precipitation, wind, and PAR data • Soil Moisture data is also collected

5. Raw Data

6. The Challenge • Import this data into ODM using SSIS • What the ???? is ODM • What the ???? is SSIS

7. CUAHSI Observations Data Model http://www.cuahsi.org/his/odm.html

8. http://msdn2.microsoft.com/en-us/library/ms141026.aspx

10. 6 5 7 4 1 3 2 At last … Work from Out to In And don’t forget … CUAHSI Observations Data Model http://www.cuahsi.org/his/odm.html

11. Outline • Start with Blank ODM Schema • Load small tables by hand • ISOMetadata, Sources, Qualifiers, Methods (Done in advance) • OffsetTypes, Variables (by hand with SQL Management Studio) • Load Sites table using simple SSIS script • Load Data Values table using more complex (but still simple) SSIS script • Create Series Catalog • Inspect using ODM Tools

12. Sites SSIS Script

13. Import DataValues using SSIS