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

Using SQL Server as a Data Integration Platform PowerPoint PPT Presentation


  • 67 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

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

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

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

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

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

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

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

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

SSIS Architecture

  • Project

  • Package(s)

  • Connections

    • Tasks

  • Dataflow

  • ControlFlow

  • EventHandlers

2008 GIS & Data Management Conference


Visual studio

Visual Studio

2008 GIS & Data Management Conference


Typical uses of ssis

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

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


Using sql server as a data integration platform

2008 GIS & Data Management Conference


Fmss data access

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 access1

FMSS Data Access

2008 GIS & Data Management Conference


Fmss data access2

FMSS Data Access

2008 GIS & Data Management Conference


Fmss data access3

FMSS Data Access

Define Query - select * from <table>

2008 GIS & Data Management Conference


Fmss data access4

FMSS Data Access

2008 GIS & Data Management Conference


Fmss data access5

FMSS Data Access

2008 GIS & Data Management Conference


Fmss data access6

FMSS Data Access

2008 GIS & Data Management Conference


Fmss data access7

FMSS Data Access

2008 GIS & Data Management Conference


Ssis package

SSIS Package

2008 GIS & Data Management Conference


Fmss data access8

FMSS Data Access

  • Geodatabase Replication

    • NPSView

      • Simple (non-versioned)

      • One-way Replication

2008 GIS & Data Management Conference


Fmss data access9

FMSS Data Access

  • Theme Table

2008 GIS & Data Management Conference


Fmss data access10

FMSS Data Access

  • Buildings View

2008 GIS & Data Management Conference


Fmss data access11

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 access12

FMSS Data Access

  • Views using Synonyms (in Geodatabase)

2008 GIS & Data Management Conference


Fmss data access13

FMSS Data Access

  • View - Joined using Synonyms (in GDB)

2008 GIS & Data Management Conference


Nps buildings statistics

NPS Buildings Statistics

2008 GIS & Data Management Conference


Access to sql server data from arcgis

Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to sql server data from arcgis1

Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to sql server data from arcgis2

Access to SQL Server Data from ArcGIS

2008 GIS & Data Management Conference


Access to sql server data from msaccess

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 msaccess1

Access to SQL Server Data from MSAccess

2008 GIS & Data Management Conference


Access to sql server data from msaccess2

Access to SQL Server Data from MSAccess

2008 GIS & Data Management Conference


Using sql server as a data integration platform

Access to SQL Server Data from MSAccess

1

2

3

4

2008 GIS & Data Management Conference


Using sql server as a data integration platform

Access to SQL Server Data from MSAccess

5

6

7

2008 GIS & Data Management Conference


Questions

Questions?

2008 GIS & Data Management Conference


  • Login