SQLServer2k实现商业智能之数据转换服务.ppt

* So what is DTS? It’s a platform built from a series of COM objects and it uses OLE DB for all data connectivity(but can access ODBC data sources through the OLE DB for ODBC Provider – Kagera) DTS Serves many functions but the primary 3 are: 1/ Graphical Job Execution and Workflow (we call this JobFlow) 2/ Extract, Transform, Load and Management Services for Data Consolidation and DW 3/ An application development platform that allows the programmer to embed, re-use or extend the capabilities of DTS. * There are several DTS Applications that MS provides, any of which could be replaced by 3rd parties: 1/ Import/Export Wizard, ships in SQL7/2000, Office 2000 – It is used to take some metadata and all data from one OLE DB Data Source, optionally transform/filter it and move it to another OLE DB Datasource 2/ Designer, Only ships with SQL Server – Used to create new packages. Edit existing ones. Provides access to a fuller range of tasks that the wizard does. 3/ DTSRUNUI, SQL2k only, is a graphical execution utility outside of SEM, that can also be used to generate DTS command lines. 4/ SQL Agent, while not limited to DTS usage is a scheduling and execution tool that is a key component in managing the execution of DTS Packages * 1/ Build a simple work flow between 4 Ax Scripts, serialise through the first and last ones, branch from No 1 for both success and failure, steps 2 and 3 handle success/failure and on completion serialise to No4. Show W/Flow Ax Scripting, Transaction Control, Fail Package Property (new in SQL2k) 2a/ Use the Twin MSMQ Packages to show package sychronisation and data passing. Execute the second package though DTSRunUI to show this. Demonstrate the generation of command lines 2b/ Same packages but also point out the setting of the global variable via the Dynamic Properties Task, point out it could have been done as a parameter to DTSRunUI * Global Variables – Now supported on the command line * In 7.0 the OLAP Processing Task shipped as part