1 / 38

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. Overview. Islands of Data & Applications SQL Server Integration Services

kelvin
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. 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

  3. Islands of Data and Applications FMSS ASMIS LCS NPS Focus NRIS Active Directory NPS Org Codes Lotus Domino … 2008 GIS & Data Management Conference

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. SSIS Architecture • Project • Package(s) • Connections • Tasks • Dataflow • ControlFlow • EventHandlers 2008 GIS & Data Management Conference

  10. Visual Studio 2008 GIS & Data Management Conference

  11. 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

  12. 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

  13. 2008 GIS & Data Management Conference

  14. 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

  15. FMSS Data Access 2008 GIS & Data Management Conference

  16. FMSS Data Access 2008 GIS & Data Management Conference

  17. FMSS Data Access Define Query - select * from <table> 2008 GIS & Data Management Conference

  18. FMSS Data Access 2008 GIS & Data Management Conference

  19. FMSS Data Access 2008 GIS & Data Management Conference

  20. FMSS Data Access 2008 GIS & Data Management Conference

  21. FMSS Data Access 2008 GIS & Data Management Conference

  22. SSIS Package 2008 GIS & Data Management Conference

  23. FMSS Data Access • Geodatabase Replication • NPSView • Simple (non-versioned) • One-way Replication 2008 GIS & Data Management Conference

  24. FMSS Data Access • Theme Table 2008 GIS & Data Management Conference

  25. FMSS Data Access • Buildings View 2008 GIS & Data Management Conference

  26. FMSS Data Access • Synonyms (in Geodatabase) CREATE SYNONYM [dbo].[FMSS_Export_syn] FOR [FMSS].[dbo].[V_FMSS_Buildings] 2008 GIS & Data Management Conference

  27. FMSS Data Access • Views using Synonyms (in Geodatabase) 2008 GIS & Data Management Conference

  28. FMSS Data Access • View - Joined using Synonyms (in GDB) 2008 GIS & Data Management Conference

  29. NPS Buildings Statistics 2008 GIS & Data Management Conference

  30. Access to SQL Server Data from ArcGIS 2008 GIS & Data Management Conference

  31. Access to SQL Server Data from ArcGIS 2008 GIS & Data Management Conference

  32. Access to SQL Server Data from ArcGIS 2008 GIS & Data Management Conference

  33. 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

  34. Access to SQL Server Data from MSAccess 2008 GIS & Data Management Conference

  35. Access to SQL Server Data from MSAccess 2008 GIS & Data Management Conference

  36. Access to SQL Server Data from MSAccess 1 2 3 4 2008 GIS & Data Management Conference

  37. Access to SQL Server Data from MSAccess 5 6 7 2008 GIS & Data Management Conference

  38. Questions? 2008 GIS & Data Management Conference

More Related