Bài giảng Cơ sở dữ liệu nâng cao - Chapter 4: Transferring data and using service broker

CHAPTER 4 Transferring Data and using Service Broker

Agenda

• Introduction • Performing a Bulk Load • Performing a Bulk Insert task • Importing Bulk XML data • Overview of SQL Server Integration Services • Using the Import/Export Wizard • Service Broker

Introduction

importing data from other sources.

• A common task when working with a database is

data.

• One of the most frequently used methods of transferring

import files into SQL Server • bulk copy program (bcp) • the BULK INSERT Transact-SQL command • the OPENROWSET Transact-SQL function • the SQL Server Integration Services (SSIS) Import/Export Wizard

• There are different methods you can use to efficiently

Performing bcp utility

• Because bcp is a program, you do not execute it from within a query window or batch but rather from the command line.

the SQL Server process

• It is an external program, which means it runs outside of

• bcp to bulk copy data either into or out of SQL Server.

Performing bcp utility

• bcp has limited data-transformation capabilities. If the data that

you are loading needs to go through complex transforms or validations, bcp is not the correct tool to use.

• bcp has limited error handling capabilities. bcp might know that

an error occurred while loading a given row, but it has limited reaction options

• Two limitations of bcp:

Performing bcp utility

bcp {dbtable | query} {in | out | queryout | format} datafile [option]

Option

Description

Instance name

-S

-T Indicates that you wish BCP to connect using a trusted connection

-T

-U

-P

the password associated with the –U login name

-c

Use a character data file type

-w

Use a Unicode data file type

-t

Field terminator

-r

Row terminator

Performing bcp utility

• bcp Command-Line Syntax bcp {dbtable | query} {in | out | queryout | format} datafile [option,..n]

Example about the simplest command bcp FileImportDB..Exam out

"c:\FileImportPractice\ExamImportFile.txt" -T –w

bcp FileImportDB..Exam in

"c:\FileImportPractice\ExamImportFile.txt" -T -w

BULK INSERT statement

INSERT

• Closely mimics the BCP command line utility • Two of the biggest differences between bcp and BULK

BULK INSERT

bcp

can only import data

can either import or export data

Executed from the command line and runs outside of the SQL Server process space,

Run inside the SQL Server process space and is executed from a query window or query batch

BULK INSERT statement

BULK INSERT {dbtable} FROM {datafile} [WITH (option)]

BULK INSERT statement

BULK INSERT FileImportDB..Exam FROM 'c:\FileImportPractice\ExamImportFile.txt'

• Example:

Importing Bulk XML data

• SQL Server provides several options for importing XML documents. You can use the OPENROWSET Transact-SQL function to read data, including XML data, from a file.

• SQL Server also offers many ways to transfer data:

• OPENXML and XML stored procedures • the XML data type’s nodes() method • the SQLXML middle-tier API to load XML data as relational data

from a file.

• In this lesson, we will focus to OPENROWSET to read data

Importing Bulk XML data • OPENROWSET function can be used in any standard SQL

statement as a table reference.

• There are many uses of the OPENROWSET function, including using the function as a target of an INSERT, UPDATE, or DELETE query.

Importing • Example:

INSERT INTO Documents(XmlCol) SELECT * FROM OPENROWSET( BULK 'c:\XMLDocs\XMLDoc9.txt', SINGLE_BLOB) AS x

‘c:\XMLDocs\XMLDoc9.txt’ file as a SINGLE_BLOB and inserts that BLOB into the XmlCol of a single row in the Documents table.

• This statement bulk imports the contents of the

• The SINGLE_BLOB format tells the OPENROWSET function to treat the entire file as a single unit, rather than parsing it in some way.

Overview of SQL Server Integration Services

• The SQL 2000 platform used Data Transformation Services (DTS)  2005 and 2008 platforms use Integration Services.

• SSIS builds on the basic principles of DTS, but expands its capabilities to include additional, easier-to-manage, features.

integration and workflow solutions that have as their goal the extraction, transformation, and loading (ETL) of data from various sources to various destinations.

• Integration Services provides enterprise-level data

• SSIS includes a wide range of tools and wizards to assist in the creation of the workflow and data flow activities that you need to manage in these complex data-movement solutions.

Overview of SQL Server Integration Services

•

• SSIS has 4 components: Integration Services itself Integration Services object model Integrated Services runtime Integrated Services dataflow

Overview of SQL Server Integration Services

• Integration Services:

• Managed through SQL Server Management Studio • Used to handle the management and monitoring of both stored

and running packages. Packages can be stored in the file system or they can be stored in the msdb database on a running instance of SQL Server 2008.

Overview of SQL Server Integration Services

• Integration Services features:

• Starting and stopping local and remote packages. • Monitoring local and remote packages. •

Importing and exporting packages from different sources.

• Managing the package store. • Customizing storage folders. • Stopping running packages when service is stopped • Viewing the Windows Event Log. • Connecting to multiple SSIS server instances.

Overview of SQL Server Integration Services

• Integration Services Object Model:

• The managed application programming

interface (API) used to access SSIS tools, command-line utilities, and custom applications is the SSIS object model.

Overview of SQL Server Integration Services

• Integration Services Runtime Engine:

•

is responsible for saving the control flow logic and execution of SSIS packages. include packages, containers, pre-defined and custom tasks, and event handlers.

• The run time handles execution order, logging, variables, and event handling. Programming the Integration Services runtime engine allows you to automate the creation, configuration, and execution of packages through the object model.

Overview of SQL Server Integration Services

• Integration Services Runtime Engine includes:

•

Integration Services Packages: are units of execution that are composed of a series of other elements, including containers, tasks, and event handlers. Integration Services Tasks: are the basic unit of work. Each task defines an action that will be taken as part of the execution of this package. Integration Services Containers: define one or more tasks as a unit of work. Integration Services Event Handlers: similar to packages. • One major difference, though, is that event handlers are reactionary – the tasks defined within an event handler will only be executed when a specific event occurs

Overview of SQL Server Integration Services

• Integration Services Dataflow Engine: • extract data from data files or relational

databases

• manage any and all transforms that

manipulate that data, and then provide that transformed data to the destination.

• A package may have more than one data flow task, and each task will execute its own data flow process for moving and manipulating data.

Overview of SQL Server Integration Services

• Creating SSIS package:

• Copy database wizard • Import and Export wizard • Business Intelligence Development Studio • Programmatically creating packages

Using the Import wizard

comma-separatedvalue (CSV) into the Sales.SpecialOffer table.

• In this example, you will import data from a simple

Description

DiscountPct Type

Category StartDate EndDate

President’s Day Sale

0.1

Holiday Promotion

Customer 2/16/2007

2/19/2007

Memorial Day Madness

0.25

Holiday Promotion

Customer 5/28/2007

5/28/2007

Fourth of July Sale

0.05

Holiday Promotion

Customer

7/1/2007

7/7/2007

Seasonal Discount

0.075

Seasonal Discount

Reseller

10/1/2007 10/31/2007

• Promos.csv file

Using the Import wizard

to the Database Engine.

• Start or open SQL Server Management Studio. Connect

Data. This will launch the SQL Server Import and ExportWizard.

• In Object Explorer, select server and expand Databases. • Right-click AdventureWorks2008 and select Tasks Import

• Click Next to move to the Data Source selection page.

Using the Import wizard

to the Database Engine.

• Start or open SQL Server Management Studio. Connect

Data. This will launch the SQL Server Import and ExportWizard.

• In Object Explorer, select server and expand Databases. • Right-click AdventureWorks2008 and select Tasks Import

• Click Next to move to the Data Source selection page.

Using the Import wizard

Transforming Data with SSIS

the Business Intelligence Development Studio. • BIDS is simply an instance of Visual Studio, which

includes add-ins for designing solutions for: • Integration Services • Analysis Services • Reporting Services

• Integration Services is part of a suite of tools included in

develop Integration Services solutions without having to maintain an active connection to an existing SQL Server.

• One of the benefits of using BIDS is that it allows to

Transforming Data with SSIS

Understanding the Development Environment

• Creating the Connection • Creating the Data Flow Task • Defining the Destination

• Following steps to create SSIS solution

Understanding the Development Environment

• Creating the Connection • Creating the Data Flow Task • Defining the Destination

• Following steps to create SSIS solution

Overview of Service Broker

• Service Broker is a framework and extension to T-SQL, and can create and use the components for writing queue & building reliable and scalable message-based applications

• Service Broker can take care of all the

communication and messaging, enabling the developer to focus on the core problem domain. • Service Broker is not enabled by default so the first specific step to working with Service Broker is to turn it on using the alter database command:

ALTER DATABASE AdventureWorks SET

ENABLE_BROKER;

Service Broker Architecture

• The core of Service Broker architecture is the

concept of a dialog, which ordered exchange of messages between two endpoints.

• An endpoint is the sender or receiver of a

message.

Service Broker Architecture

• Message type: is a definition of the format of a

message. • The message type is an object in a database. • Messages are the information exchanged between applications

that use Service Broker.

• The message type object defines the name of the message and

the type of data it contains.

CREATE MESSAGE TYPE message_type_name [ AUTHORIZATION owner_name ] [ VALIDATION = { NONE| EMPTY| WELL_FORMED_XML

| VALID_XML WITH SCHEMA COLLECTION schema_collection_name} ]

Service Broker Architecture

• Message type:

• message_type_name: The name of the message type is just a

SQL Server identiﬁe

• AUTHORIZATION owner_name: deﬁnes the owner of the message

type

• VALIDATION: speciﬁes how Service Broker validates the message body for messages of this type. When this clause is not speciﬁed, validation defaults to NONE.

• Example:

CREATE MESSAGE TYPE [//www.wrox.com/order/orderentry] VALIDATE = WELL_FORMED_XML

Service Broker Architecture

• Contracts: define which message type can be

used in a conversation. • When a contract is created, at least one message type needs to be marked as SENT BY INITIATOR or SENT BY ANY. Obviously, a message type must exist before you create the contract.

• In addition, the message type and direction cannot be changed

once the contract is defined

 cannot alter the contract once you create it. • If you have to change the message type, then you must first drop

the contract if you have defined one for that message type.

Service Broker Architecture

• Queue: Service Broker performs asynchronous

operations. In asynchronous processing, you send a request to do something and then you start doing something else; the system processes the request you made later. Between the time when you make the request and when the system process acts on it, the request must be stored somewhere. The place where these requests are stored is called the queue. • Service Broker implements queues via a hidden table in the

database where the queue is defined.

Service Broker Architecture

• Queue:

Service Broker Architecture

of a conversation. • A service is associated with a list of contracts that is accepted by

the service. Note that mapping a service to a contract is an optional step on the initiator.

• On the target, if you do not specify any contract, you won’t be able

to send any messages to the target

• Services: A Service Broker service identifies an endpoint

Service Broker Architecture

• A conversation is a reliable, ordered exchange of messages. The

core concept of Service Broker is the conversation.

• Two kinds of conversations:

• Dialog: This is a two-way conversation between exactly two endpoints. An endpoint is a source or destination for messages associated with a queue; it can receive and send messages. A dialog is established between an initiator and target endpoint.

• Monolog: This is a one-way conversation between a

single publisher endpoint and any number of subscriber endpoints. Monologs are not available in SQL Server 2005 or 2008, though they will be included in future versions.

• Conversation: