slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Module 11: Data Transport PowerPoint Presentation
Download Presentation
Module 11: Data Transport

Loading in 2 Seconds...

play fullscreen
1 / 14

Module 11: Data Transport - PowerPoint PPT Presentation


  • 132 Views
  • Uploaded on

Module 11: Data Transport. Overview. Tools and functionality in Oracle and their equivalents in SQL Server for: Data transport out of the database Data transport into the database

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

Module 11: Data Transport


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
overview
Overview
  • Tools and functionality in Oracle and their equivalents in SQL Server for:
    • Data transport out of the database
    • Data transport into the database
  • Tools and functionality in SQL Server for data transport into, out of, and within a database and across multiple databases, multiple file formats, and other data sources and destinations
data out overview
Data Out Overview

SQL Server ToolsSQL Server Integration Services (SSIS)

BCP

Sqlcmd.exe

PowerShell

SELECT or PRINT

Linked Servers

Oracle Tools

Data Pump Export Utility

SQL*Plus

DBMS_OUTPUT

UTL_FILE

Oracle 10g

SQL Server 2008

data in overview
Data In Overview

SQL Server ToolsSQL Server Integration Services (SSIS)

BCP

Bulk Insert

Sqlcmd.exe

PowerShell

SELECT INTO

Linked Servers

Oracle Tools

Data Pump Import Utility

SQL*Plus

CREATE TABLE

SQL Server 2008

demonstration 1 exporting data using powershell
Demonstration 1: Exporting Data Using PowerShell

In this demonstration you will learn to:

  • Manage data export with PowerShell
sql server data transport
SQL Server Data Transport
  • SQL Server offers several Bulk Copy commands and tools:
    • BCP, the Bulk Copy utility, is used to transfer large volumes of data in and out of a database
      • To export—data is dumped into a flat file in a delimited format
      • To import—data is first exported from the source database or program and then imported into SQL Server database
    • BULK INSERT command can be used within Transact-SQL programs to insert large volumes of data from a flat file
    • Bulk copy API can be used in ODBC, OLE DB, SQL-DMO and SMO applications
    • OPENROWSET (BULK…) can also be used to bulk import large object data in SQL Server
demonstration 2 bulk copy and bulk insert
Demonstration 2: Bulk Copy and Bulk Insert

In this demonstration you will learn to:

  • Extract the data with the Bulk Copy Program (bcp)
  • Use bcp and Bulk Insert to import data into new tables
sql server integration services ssis
SQL Server Integration Services (SSIS)
  • SQL Server Integration Services offers several tools to extract, transform and load data from multiple data sources into SQL Server databases and vice versa
  • Provides control over data flow, looping and conditioning
  • Packages can be built using
    • SSIS designer in Business Intelligence Development Studio
    • Import / Export Wizard
    • Database Export Utility
elements of integration services
Elements of Integration Services
  • SQL Server Integration Services Service (MsDtsSrvr.exe)
  • Package
    • Control Flow Elements
      • Containers
      • Tasks
      • Precedence Constraints
    • Data Flow Elements
    • Event Handlers
    • Variables
    • Configurations
ssis tools
SSIS Tools
  • SSIS tools are used to create, modify and execute Packages
  • SSIS Import / Export Wizard
    • Assists in building simple Packages to import, export, and transform data or to copy database objects
  • SSIS Designer inside Business Intelligence Development Studio
    • Graphical application that lets us build and debug packages containing complex workflows, multiple connections to heterogeneous data sources, and event-driven logic
  • SSIS Package Execution Utilities
    • dtexecui.exe
    • dtexec.exe
  • dtutil.exe is a tool used to manage SSIS packages
demonstration 3 ssis data flow
Demonstration 3: SSIS Data Flow

In this demonstration you will learn to:

  • Extract conditional table from one source to multiple destinations
copy database wizard
Copy Database Wizard
  • Copy Data Wizard (CDW) can be used to copy or move databases across SQL Server instances
  • Provides two methods:
    • Attach / Detach
    • Using SQL ServerManagement Objects (SMO)
  • A proxy account must be created in order to run SSIS package created by CDW
demonstration 4 database import export wizard
Demonstration 4: Database Import/Export Wizard

In this demonstration you will learn to:

  • Move infrequent data transformations with a wizard
review
Review
  • We reviewed tools used in Oracle for transferring data out of a database — Data Pump Export utility, SQL*Plus, SBMS_UTILITY and UTL_FILE packages and their SQL Server equivalents — bcp utility, sqlcmd.exe tool, SSIS, print and SELECT statements
  • We have seen tools used in Oracle for transferring data into a database — Data Pump Import utility, SQL*Plus, SQL*Loader and command, SSIS, OPENROWSET
  • We discussed in detail the Bulk Copy commands in SQL Server
  • We discussed SSIS in detail, including:
    • SSIS Package components such as tasks, transformations and workflow constraints
    • SSIS tools such as Import//Export Wizard, SSIS Designer
  • Copy Database Wizard