Microsoft SQL Server 2000 Data Transformation Services- P16

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:5

Thêm vào BST

Báo xấu

64
lượt xem 4
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Microsoft SQL Server 2000 Data Transformation Services- P16: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for moving data. It’s easy to use, especially when you’re creating transformation packages with the DTS Wizard. But it’s also a flexible tool that you can customize to gain a high degree of control over the transformation of your data.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Microsoft SQL Server 2000 Data Transformation Services- P16

SQL statements 702 SQL statements, 506 tasks, 470 T assigning, 366 transactions, 484-485, modifying, 300-301 487-490, 493 tables objects, 503, 513-514 stored procedures, 339 Bulk Insert task, 283 properties, 294 Execute SQL task, 301 copying, 516 queries, 372-373 moving, 311 data, 89 staging area, 89-90 packages, 18-19 staging area, 89-90 properties, 546-549 Data Driven Query task, star schema 220-222 dimension tables, 85 storing databases, 310 fact tables, 78-79, 82, Called Package, 385 indexes, 584-585 92 data, 60, 72-74, 76 locking, 283-284, 578 modeling, 81-82 DTS packages, 14-15 moving, 92, 311 storage, 74 error messages, 340 sources, 131 see also dimension error records, 557-558 transformations, 251 tables meta data, 334 see also dimension starting custom trans- packages, 438, 445-451 tables formations, 664-665 strings task logs, 358-359 StartTime property, 497 data, 197 empty, 203-204 Task object, 27 status, transforma- assignments, 371 manipulating, 202-203 tions, 188, 207 collections, 624 receiving, 396 Step object, 27, 612 sending, 392 creating, 11-12, 335, collection, 624 Transformation Flags 536, 620-621 creating, 470 dialog, 145 custom, 634-637 disabling, 480 hierarchy, 614 structured storage file, executing, 458, 471, naming, 497 15 476-477 priorities, 12-13, 479 Join Transaction If Subject property, 407 programmatic identi- Present, 484 subordinate dimen- fiers, 621-623 lineage, 605-606 sions properties, 615-616, naming, 471, 474 creating, 98 619, 621 packages, 478-481 keys, 85, 88, 92 referencing, 349-350, Parent, 479, 496-497 splitting, 95-97 620 precedence constraints, subsets, 249 steps, 470 471 threads, 477 SuccessReturn prop- properties, 470, 477, transactions, 486-487 erty, 428 480, 496-497 see also custom tasks referencing, 349-350 summarization, 37 technical meta data, system information, 589 339, 627-628
Transform Data task 703 templates, 32-33, 42, 54 TransactionIsolation Transfer Jobs task, 46, terminating Level property, 465 338-339 Enterprise manager, transactions Transfer Logins task, 561-563 chaos, 483 46, 337, 623 Package manager, child packages, 379 Transfer Master Stored 561-563 Commit On option, 482 Procedure Properties packages, 454, 481, 555 connections, 480, 486- task, 45, 339, 623 processes, 428 487 Transfer MSDB Jobs testing errors, 558 task, 623 scripts, 187 isolation levels, 483-484 joining, 482, 484 Transfer Object task, transformations, 146 packages, 493 50, 317-323 text files parallel processing, Transform Data task, ActiveX scripts, 172 490-493 40, 47 bcp utility, 585 parent packages, 379 aborting, 191 binary, 427 processing, 63-64 bulk inserting, 268 connections, 115-119 rollback, 491-493 collections, 350 data, 569-570 runtime error, 487 columns, 138-143, decompression, 427 steps, 480, 484-490 159-160, 575-577 delimited, 271 troubleshooting, 490 connections, 102 destination, 116, 138 Use Transactions creating, 128-129, fixed-length, 271 option, 482 160-165, 201 OLE DB providers, 102 VBScript, 488-489 data, 90 source, 117, 131 Workflow ActiveX DataPumpTask object, storage, 72 Script, 489-490 136 threads descriptions, 129-130 Transfer Databases custom tasks, 653 destination, 137-139 task, 45, 157-159, packages, 479-480 executing, 26 336-337 tasks, 477 freestanding icon, creating, 341-342 Time field (dimension destination, 334-335 173-176 tables), 87 executing, 26 names, 129 timeouts, messages, programmatic identifier, objects, 350 399-400, 428 623 Parallel Data Pump task, properties, 341 260-263 TO keyword rowsets, source, 335 performance, 126-127, 249 157-159, 582 ToLine property, 407 Transfer Error programmatic identifier, tools, see utilities Messages task, 46, 622 340, 623 Transact-SQL language, properties, 154-159, 217 40, 57, 549-552
Transform Data task 704 records, 201 InMemoryBlobSize 2troubleshooting relational database property, 147 bcp utility, 585 source, 131-132 logic, 578-581 connections, 490 selecting, 269-270 Lowercase, 149, 169 errors, 557 source, 130-131, 236, Middle of String, 149, format files, 275 369 151, 169-170 freestanding icon, Transformation Flags Name property, 147 173-176 dialog, 144-146 Parent property, 147 packages, 444 transformations, performance, 568 transactions, 490 146-147, 573-574 properties, 147 truncation, 145 see also Data Pump Read File, 151 trusted connections, 20 task; Data Transfor- records, 206-209 mation task scripts, 185 Turn On Package Cache skipping, 188 option, 56 Transform Failure phase, 231, 233 status, 188-191, 207, 219-220 transformations, 187, 624 tables, 251 U testing, 146 Transformation Flags Trim String, 149, 151, UDL files, 104, 109-111 dialog, 144-146 169-170 UNCFile parameter, 443 Transformation object Uppercase, 149, 169 undoing scripts, 187 ActiveX scripts, 152, VBScript, 165-167 172, 183-187 VC++, 32 UnicodeFile property, ATL installation, Write File, 151-152 170 662-663 TransformationInfo Unified Modeling collections, 146-147 object, 28 Language (UML) comparing, 573-574 Model, 598 TransformationSet col- Copy Column, 149, 169 UnitializeMAPI lection, 252, 624 creating, 167-169 method, 408 custom, 152, 581, TransformationSet Options property, Unlock method, 198 664-669, 672-676 data, 49, 61-63 252-254 updating DateTimeString, TransformPhases prop- cubes, 414 148-149, 171 erty, 243 dimension tables, 94-98 errors, 555 fields, 220 triggers ForceBlobsInMemory queries, 218 copying, 316 property, 147 shared dimensions, 414 enabling, 282 ForceSourceBlobs subordinate dimensions, moving, 311 Buffered, 147 92 Trim String transforma- hierarchy, 248, 617 tion, 149, 151, 169-170, 573
Visual Basic 705 upgrading DTSRun, 53, 427 VBScript databases, 36, 502, 504 osql, 427 columns, 576-577 Meta Data Services, 588 functions, 378 converting from Visual Transfer Database task, packages, 384-386 Basic, 360-361 334-335 custom tasks, 654-655 Uppercase String trans- DTS packages, 367 formation, 149, 169, V entry functions, 361 573 GoTo command, 360 ValidateSchema line labels, 360 Use Transactions method, 669-671 lineage variables, 459 option, 482 Value property local variables, 192-193 UseAllPhases task, 242 objects, 31 GlobalVariable object, UseDSL property, 368 packages, 457-458 109-111 PrecedenceConstraint system information, UseFastLoad property, object, 474 627-628 157-158, 217 transactions, 488-489 values transformations, UseOLEDBService data, 37 165-167, 187 Components property, destination columns, 467 202 VC++ global variables, 53, DTS packages, 367 UseRepository prop- 346, 378, 380-383 transformations, 32 erty, 380 Identity field, 518 verification of data, 37 UserID property, 108 null, 203-204 VersionID parameter, UserQuery property, variables 445, 458, 465 218 declaring, 446, 450 versions, DTS, 16, 451, users global, 53, 55 467 databases, 311 lineage, 458-460 default options, 315-316 view source, 131 Post Row Transform functions, 57 phase, 237 viewing interface, 194-195, Pre Source phase, indexes, 57 257-258, 260-263 235-237 log files, 455-456 UseTransaction prop- Row Transform phase, moving, 311 erty, 465 237 objects, 596 see also global vari- properties, 596 UseTrustedConnection property, 108 ables; lineage vari- virtual cubes, 413 utilities ables; local variables Visual Basic command-line VarPersistStgOfHost ActiveX scripts, cdw, 505 parameter, 439, 443 361-363 DTSQiz, 505 Analysis Services Processing task, 420-421
Visual Basic 706 API calls, 361 packages WHERE clause, 584 Bulk Insert task, code libraries, 530 Windows, 53 287-290 code modules, 530 wizards, calling, connections, 104, creating, 31-32, 504-507 114-115 497-499 converting to VBScript, executing, 18 Workflow ActiveX 360-361 headers, 531 Script, 494-496 Copy SQL Server loading, 537-538 Write File transforma- Objects task, 320-323 Main function, tion, 151-152, 170-171 custom tasks, 653-655 531-535 writing Data Driven Query task, public variables, 531 ActiveX scripts, 52, 152 223-226 saving, 443-444, code, 228, 239-242 Data Mining Prediction 527-529, 537-538 queries, 294 Query task, 421-423 Parallel Data Pump task logs records, DTS packages, 15, 53 task, 254-257 358-359 Dynamic Properties properties, 541 task, 375-376 Send Mail task, entrance function, 408-410 244-245 SQL Namespace, X–Z errors, 361, 540-541 542-545 XML (Extensible events, 538-540 tasks, 620 Markup Language), Execute Package task, Transfer Databases task 40 387-389 (15.1), 341-342 data, 72-74, 134, 596 Execute Process task, Transfer Object task, exporting/importing, 40 429-430 321-323 integration, 56 Execute SQL task, Transform Data task, Parallel Data Pump task, 306-308 129, 160-165 44 File Transfer Protocol Visual C++, 243 task, 329-331 files, 360 For Next loop, 360 installation, 526 W loops, 542 warehousing data, Message Queue task, 37-38 400-403 multiphase data pump, Web clickstream data, 243 69 opening, 635-636 Web servers, 38 WebDrive, 326