Cơ sở dữ liệu - Chapter 4: Transferring data and using service broker

CHAPTER 4Transferring Data and using Service Broker AgendaIntroductionPerforming a Bulk LoadPerforming a Bulk Insert taskImporting Bulk XML dataOverview of SQL Server Integration ServicesUsing the Import/Export Wizard Service BrokerIntroductionA common task when working with a database is importing data from other sources.One of the most frequently used methods of transferring data.There are different methods you can use to efficiently import files into SQL Serverbulk copy program (bcp)the BULK INSERT Transact-SQL commandthe OPENROWSET Transact-SQL functionthe SQL Server Integration Services (SSIS) Import/Export WizardPerforming bcp utilityBecause bcp is a program, you do not execute it from within a query window or batch but rather from the command line. It is an external program, which means it runs outside of the SQL Server processbcp to bulk copy data either into or out of SQL Server.Performing bcp utilityTwo limitations of bcp: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 Performing bcp utilitybcp {dbtable | query} {in | out | queryout | format} datafile [option]OptionDescription-SInstance name-T-T Indicates that you wish BCP to connect using a trusted connection-Ulogin name when not using –T-P the password associated with the –U login name-cUse a character data file type-wUse a Unicode data file type-tField terminator-rRow terminator Performing bcp utilitybcp Command-Line Syntaxbcp {dbtable | query} {in | out | queryout | format} datafile [option,..n]Example about the simplest commandbcp FileImportDB..Exam out "c:\FileImportPractice\ExamImportFile.txt" -T –wbcp FileImportDB..Exam in "c:\FileImportPractice\ExamImportFile.txt" -T -wBULK INSERT statementClosely mimics the BCP command line utilityTwo of the biggest differences between bcp and BULK INSERTBULK INSERTbcpcan only import datacan either import or export dataRun inside the SQL Server process space and is executed from a query window or query batchExecuted from the command line and runs outside of the SQL Server process space, BULK INSERT statementBULK INSERT {dbtable} FROM {datafile} [WITH (option)]BULK INSERT statementExample:BULK INSERT FileImportDB..Exam FROM 'c:\FileImportPractice\ExamImportFile.txt' Importing Bulk XML dataSQL 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 proceduresthe XML data type’s nodes() methodthe SQLXML middle-tier API to load XML data as relational dataIn this lesson, we will focus to OPENROWSET to read data from a file. Importing Bulk XML dataOPENROWSET 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 xThis statement bulk imports the contents of the ‘c:\XMLDocs\XMLDoc9.txt’ file as a SINGLE_BLOB and inserts that BLOB into the XmlCol of a single row in the Documents table.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 ServicesThe 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 Services provides enterprise-level data integration and workflow solutions that have as their goal the extraction, transformation, and loading (ETL) of data from various sources to various destinations. 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 ServicesSSIS has 4 components:Integration Services itselfIntegration Services object modelIntegrated Services runtimeIntegrated Services dataflowOverview of SQL Server Integration ServicesIntegration Services:Managed through SQL Server Management StudioUsed 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 ServicesIntegration 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 stoppedViewing the Windows Event Log.Connecting to multiple SSIS server instances.Overview of SQL Server Integration ServicesIntegration 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 ServicesIntegration 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 ServicesIntegration 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 occursOverview of SQL Server Integration ServicesIntegration Services Dataflow Engine:extract data from data files or relational databasesmanage 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 ServicesCreating SSIS package:Copy database wizardImport and Export wizardBusiness Intelligence Development StudioProgrammatically creating packages Using the Import wizardIn this example, you will import data from a simple comma-separatedvalue (CSV) into the Sales.SpecialOffer table.Promos.csv fileDescriptionDiscountPctTypeCategoryStartDateEndDatePresident’s Day Sale0.1Holiday PromotionCustomer2/16/20072/19/2007Memorial Day Madness0.25Holiday PromotionCustomer5/28/20075/28/2007Fourth of July Sale0.05Holiday PromotionCustomer7/1/20077/7/2007Seasonal Discount0.075Seasonal DiscountReseller10/1/200710/31/2007 Using the Import wizard Start or open SQL Server Management Studio. Connect to the Database Engine. In Object Explorer, select server and expand Databases. Right-click AdventureWorks2008 and select Tasks Import Data. This will launch the SQL Server Import and ExportWizard. Click Next to move to the Data Source selection page. Using the Import wizard Start or open SQL Server Management Studio. Connect to the Database Engine. In Object Explorer, select server and expand Databases. Right-click AdventureWorks2008 and select Tasks Import Data. This will launch the SQL Server Import and ExportWizard. Click Next to move to the Data Source selection page. Using the Import wizard Using the Import wizard Using the Import wizard Using the Import wizard Transforming Data with SSISIntegration Services is part of a suite of tools included in the Business Intelligence Development Studio. BIDS is simply an instance of Visual Studio, which includes add-ins for designing solutions for:Integration ServicesAnalysis ServicesReporting ServicesOne of the benefits of using BIDS is that it allows to develop Integration Services solutions without having to maintain an active connection to an existing SQL Server. Transforming Data with SSIS Understanding the Development Environment Understanding the Development EnvironmentFollowing steps to create SSIS solutionCreating the ConnectionCreating the Data Flow TaskDefining the Destination Understanding the Development EnvironmentFollowing steps to create SSIS solutionCreating the ConnectionCreating the Data Flow TaskDefining the DestinationOverview of Service BrokerService 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 applicationsService 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 ArchitectureThe 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 ArchitectureService Broker ArchitectureMessage 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 ArchitectureMessage type:message_type_name: The name of the message type is just a SQL Server identifieAUTHORIZATION owner_name: defines the owner of the message typeVALIDATION: specifies how Service Broker validates the message body for messages of this type. When this clause is not specified, validation defaults to NONE. Example:CREATE MESSAGE TYPE [//www.wrox.com/order/orderentry]VALIDATE = WELL_FORMED_XMLService Broker ArchitectureContracts: 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 ArchitectureQueue: 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 ArchitectureQueue:Service Broker ArchitectureServices: A Service Broker service identifies an endpoint 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 targetService Broker ArchitectureConversation: 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.Service Broker Architecture