Using sql server as a data integration platform
Download
1 / 38

Using SQL Server as a Data Integration Platform - PowerPoint PPT Presentation


  • 108 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Using SQL Server as a Data Integration Platform' - dysis


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



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


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
Questions?

2008 GIS & Data Management Conference


ad