Using sql server as a data integration platform
Sponsored Links
This presentation is the property of its rightful owner.
1 / 38

Using SQL Server as a Data Integration Platform PowerPoint PPT Presentation


  • 76 Views
  • Uploaded on
  • Presentation posted in: General

Using SQL Server as a Data Integration Platform. Allen Sparks Enterprise GIS Program Resource Information Management Division National Information Services Center Office of the Chief Information Officer. Overview. Islands of Data & Applications SQL Server Integration Services

Download Presentation

Using SQL Server as a Data Integration Platform

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Using SQL Server as a Data Integration Platform

Allen Sparks

Enterprise GIS Program

Resource Information Management Division

National Information Services Center

Office of the Chief Information Officer

2008 GIS & Data Management Conference


Overview

  • Islands of Data & Applications

  • SQL Server Integration Services

  • How the EGIS Program Currently Leverages SQL Server

  • Integration with GeoDatabases

  • Accessing SQL Server Data from Clients

2008 GIS & Data Management Conference


Islands of Data and Applications

FMSS

ASMIS

LCS

NPS

Focus

NRIS

Active

Directory

NPS

Org Codes

Lotus

Domino

2008 GIS & Data Management Conference


We Need Bridges (or a boat)

LCS

Active

Directory

FMSS

ASMIS

NPS

Focus

NRIS

NPS

Org Codes

My Favorite

Application

2008 GIS & Data Management Conference


The Ideal (Future) Solution

ASMIS

FMSS

LCS

NPS

Focus

NPS

Org Codes

NRIS

“Enterprise Service Bus”

SOA … Web Services

IRMA

My Favorite

Application

2008 GIS & Data Management Conference


An Interim “Bridge” Solution

ASMIS

FMSS

LCS

NPS

Focus

NPS

Org Codes

NRIS

SQL Server Integration Services

Web Services

My Favorite

Application

2008 GIS & Data Management Conference


SQL Server Architecture

  • Database Engine

    • core service for storing, processing, and securing data

  • Analysis Services

    • supports data analysis, online analytical processing (OLAP) and data mining

  • Reporting Services

    • comprehensive data reporting

  • Integration Services

    • a platform for building enterprise-level data integration and data transformations solutions

2008 GIS & Data Management Conference


SQL Server Integration Services (SSIS)

  • a rich set of built-in tasks and transformations;

  • tools for constructing packages;

  • Integration Services service

    • running and managing packages.

  • Graphical tools & Wizards

    • No code writing required;

  • Object Model API

    • custom tasks & other package objects.

2008 GIS & Data Management Conference


SSIS Architecture

  • Project

  • Package(s)

  • Connections

    • Tasks

  • Dataflow

  • ControlFlow

  • EventHandlers

2008 GIS & Data Management Conference


Visual Studio

2008 GIS & Data Management Conference


Typical Uses of SSIS

  • Merging Data from Heterogeneous Data Stores

  • Populating Data Warehouses and Data Marts

  • Cleaning and Standardizing Data

  • Automating Administrative Functions and Data Loading

2008 GIS & Data Management Conference


EGIS Use of SQL Server

  • FMSS Data Access

    • FMSS Data Replication

      • Oracle SQLNet Client

      • ODBC

      • SQL Server Import/Export Wizard

    • Synonyms

    • Views

2008 GIS & Data Management Conference


2008 GIS & Data Management Conference


FMSS Data Access

  • Oracle SQLNet

    TNSNames.ora

    FMSS=(DESCRIPTION = (ADDRESS_LIST =

    (ADDRESS=(PROTOCOL=TCP)(HOST=__)(PORT=1521)))

    (CONNECT_DATA = (SID = MAXREPT)))

  • ODBC

    • Oracle Driver references SQLNet Service Name FMSS

  • SQL Server Import / Emport Wizard

    • DTSWizard.exe

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


FMSS Data Access

Define Query - select * from <table>

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


FMSS Data Access

2008 GIS & Data Management Conference


SSIS Package

2008 GIS & Data Management Conference


FMSS Data Access

  • Geodatabase Replication

    • NPSView

      • Simple (non-versioned)

      • One-way Replication

2008 GIS & Data Management Conference


FMSS Data Access

  • Theme Table

2008 GIS & Data Management Conference


FMSS Data Access

  • Buildings View

2008 GIS & Data Management Conference


FMSS Data Access

  • Synonyms (in Geodatabase)

    CREATE SYNONYM [dbo].[FMSS_Export_syn] FOR [FMSS].[dbo].[V_FMSS_Buildings]

2008 GIS & Data Management Conference


FMSS Data Access

  • Views using Synonyms (in Geodatabase)

2008 GIS & Data Management Conference


FMSS Data Access

  • View - Joined using Synonyms (in GDB)

2008 GIS & Data Management Conference


NPS Buildings Statistics

2008 GIS & Data Management Conference


Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to SQL Server Data from MSAccess

The ODBC Data Source Administrator is used to create and manage ODBC connections.

2008 GIS & Data Management Conference


Access to SQL Server Data from MSAccess

2008 GIS & Data Management Conference


Access to SQL Server Data from MSAccess

2008 GIS & Data Management Conference


Access to SQL Server Data from MSAccess

1

2

3

4

2008 GIS & Data Management Conference


Access to SQL Server Data from MSAccess

5

6

7

2008 GIS & Data Management Conference


Questions?

2008 GIS & Data Management Conference


  • Login