Microsoft SQL Server 2000 Data Transformation Services- P15

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

Thêm vào BST

Báo xấu

75
lượt xem 10
download

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

Microsoft SQL Server 2000 Data Transformation Services- P15: 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- P15

Extending the Power of DTS 676 PART VI LISTING 32.4 Continued //Destination column type is 8-byte real. *pdAverage = (double) cint; for (j = 0; j < m_iDecimals; j++) { *pdAverage = *pdAverage / 10; } memcpy(pDestData, pdAverage, *pulLength); break; default: *pdAverage = (double) cint; for (j = 0; j < m_iDecimals; j++) { *pdAverage = *pdAverage / 10; } memcpy(pDestData, pdAverage, *pulLength); break; } *pulStatus = DBSTATUS_S_OK; //END ADDED CODE---------------------------------------------- return NOERROR; } Adding the Project’s Error Definitions If you want to return specific error codes for conditions that occur in your custom transforma- tion, add them in the *.idl file following this line: import “ocidl.idl”; The code that defines the errors for the AvgXform task is shown in Listing 32.5. LISTING 32.5 The Error Definitions for the Custom Task //-----------Error codes for the Average Transform--------------------- typedef [helpstring( “Error codes generated by the average transform”)] enum DTSAvgTransformError { DTSAvgXform_Error_WRONG_NUM_DEST_COLS = 0x80041001, DTSAvgXform_Error_NOT_CORRECT_TYPE = 0x80041002 } DTSAvgXformError, *LPDTSAvgXformError; //---------------------------------------------------------------------
Creating a Custom Transformation with VC++ 677 CHAPTER 32 Registering the Custom Transformation The ATL COM wizard creates the code to register the custom transformation during the build process. You can register the transformation on other computers by using the regsvr32 command-line utility: regsvr32 \Average.dll Unlike the registration of custom tasks, there are no special registry entries that need to be created for a custom transformation. 32 Conclusion TRANSFORMATION CREATING A DTS provides many excellent built-in tools. One of the best characteristics of DTS, though, is CUSTOM its extensibility. You can build new tools within the DTS environment to handle new data trans- formation situations more effectively. Build a custom transformation when you need the best possible performance, or when you are using a particular ActiveX transformation script repeatedly.
A INDEX aborting Transform Data task, 191 accessing COM objects, 27 files, 360 package log files, 456-458 AcquireConnection method, 122 activating debugging, 55 Active Template Library, see ATL ActiveX Script task, 40, 50 constants, 42 creating, 346-348, 362-363 Data Driven Query task, 214, 221-222 debugging, 558-561 destination columns, 202 empty strings, 203-204 errors, 555 executing, 182-183 failure precedence constraints, 476 full outer joins, 206 global variables, 196-197 lookups, 199-200 null values, 203-204 objects, 42 Package Object Browser, 47, 49 programmatic identifier, 622 properties, 172, 347, 496 strings, 202-203 Transform Data task, 260-263 transformations, 152, 172, 183-187 Workflow, 494, 495 writing, 52
adding 680 adding, 624 arguments, DTSRun bindings table, 214 assignments, 370 utility, 20-21 browsing meta data, data, 89-90, 220 AS keyword rowsets, 591, 593 fields, 277-278, 280 249 bulk copy, 426 global variables, 347, assignments Bulk Insert task, 50, 496 constants, 374 268 joins, 91 data files, 373 bcp command line lookups, 200 Dynamic Properties utility, 268 objects, 29, 317-320 task, 367-368, connections, 102 properties to custom 370-371 creating, 270, 287-290 transformations, environment variables, data, 89 665-667 374 delimited text files, 271 records, 201 global variables, 372 destination files, 270 ADO recordset, 355-356 INI files, 373-374 executing, 26 AllowIdentityInsert SQL queries, 372 fixed-length text files, property, 158, 217 asynchronous 271 Analysis Services processing, 392 format files, 52, Processing task, 40, ATL transformation, 271-276 44, 93, 413-417, 662-663, 677 programmatic identifier, 420-421 622 Attributes field analyzing properties (dimension tables), errors, 554-555 Check Constraints, 85-87 Internet clickstream 281-282 AutoCommit Code Page, 284-285 data, 39-40 Transaction property, Data File Type, 285 API calls, 361 465, 482 Enable Identity appending automating Insert, 283-284 files, 152 filters, 415-416 Keep Nulls, 282-283 hierarchical rowsets, global variables, 55 Maximum Errors, 249 286 avoiding dimension Application object, 28 tables, 94-98 Rows, 286 creating, 446 Sorted Data, 284 hierarchy, 625-628 selecting, 269-270 log files, 21 Transform Data task, properties, 626 B 268 referencing, 350 business analysis data, batch files, 228, 427 architecture, DTS pack- 64-69, 589 bcp command line ages, 8, 10 utility, 268, 427, 585 archiving data, 38, 60 binary files, 427
Commit On option 681 C CloseConnection Properties, 625 property, 496 referencing, 350 cache, DTS packages, closing connections, transformations, 56 122-123, 480 146-147 calculating values, 37 clustering data, 67 columns, 159 Called Package, 385, creating, 167-169 code 386 destination, 139-141, events, 42 143 calling libraries, 530 identity, 283 functions, 535 pages, 284-285 lineage, 600-601 wizards, 504-507 wizards, 506-507 mappings, 519-520 capturing rowsets, writing phases, 228 names, 575-577 299-300 Batch Complete, 228 ordinal numbers, On Batch Complete, case sensitivity, 575-577 240-241 197-198 prediction queries, 419 On Insert Catalog property, 108 properties, 159-160 Failure/Success, referencing, 183 categories, objects, 612 228, 239-240 rowsets, 249 CCLine property, 407 On Transform scripts, 187 Failure, 228 cdw command-line source, 139-141, 143 Post Source Data, utility, 505 Transform Data task, 228, 241-242 changing Server 138 Pre Source, 228 property, 368 Pump Complete, COM objects chaos, 483 228, 242 accessing, 27 Row Transform, 228 creating, 243 Check Constraints references, 546 property, 281-282 collation, 314 command-line utilities CheckingDurationIn collections cdw, 505 Secs property, 656 Add method, 624 DTSQiz, 505 CheckingIntervalInSecs Count property, 624 DTSRun, 53, 427 property, 656 CustomTask interface, osql, 427 639-640 child packages, 378-383 wizards, 505-506 Insert method, 624 child rowsets, 249 Item method, 624 CommandProperties cleansing data, 89, 91 methods, 625 property, 294 clearing objects, New method, 624 commands, 21-22 317-320 Package2 object, 445 CommandTimeout Parallel Data Pump property, 294 clickstream data, 39-40, task, 252 60 Commit On option, 482 Parent property, 466, 624
Commit Transaction option 682 Commit Transaction properties, 104-114, 294 DTS packages, 511-512 option, 485 referencing, 348-349 location, 508-509 CommitSuccess Server property, 368 objects, 510-511 property, 496 text files, 115-119 packages, 17 transactions, 486-487 Copy SQL Server comparing transfor- Transform Data task, Objects task mations, 573-574 102 collation, 314 Component Object troubleshooting, 490 CopySchema, 313 Model, see COM trusted, 20 data, 313-314 components, 370 Visual Basic, 104, databases, 310-311 configuration 114-115 DropDestinationObjects Connection object, consolidation, data, First, 313 113-115 36-37 executing, 26 packages, 479-481 constants IncludeDependencies, transactions, 484 ActiveX scripts, 42 313 Connected property, Data Driven Query task, objects, 312-315 112 219-220 performance, 310 Dynamic Properties programmatic identifier, ConnectImmediate task, 374 622 property, 112 properties, 317 Connection object, 27 constraints script file directories, Bulk Insert task, 102 copying, 316 316-317 Called Package, 385 disabling, 281 user default options, closing, 122-123, 480 enabling, 281 315-316 collections, 109, 624 failure precedence, 476 Visual Basic, 320-323 creating, 512-513, 633 monitoring, 281-282 precedence, 471-476 copying data, 37, 102, 109-110 collation, 314 DTS Designer, 10, conversion commands, 21 103-104 binary files to text, 427 constraints, 316 Execute SQL task, 102 Visual Basic to data, 313-314 methods, 122 VBScript, 360-361 databases, 52, 310-311, modifying, 121-122 Copy Column transfor- 316 OLAP data source, 119 mation, 149, 168-169, FOREIGN keys, 316 OLE DB providers, 102, 573 indexes, 316 114-115, 119, 121 Copy Database Wizard, logins, 316 opening, 123 33-34, 45, 52 objects, 314-315, 502 Oracle DB Provider, calling, 504-507 permissions, 316 121 databases, 502-503, PRIMARY keys, 316 packages, 628-631 508-509 tables, 516 Parallel Data Pump task, triggers, 316 102
custom tasks 683 Copying in ActiveX DTS Designer, 103-104, tasks, 11-12, 335, 536, Script transformation, 534 620-621, 634-635 573 Dynamic Properties templates, 32-33 CopySchema property, task, 375-376 Transfer Databases task, 313 environment variables, 341-342 Count property, 624 374 Transform Data task, Execute Process task, 128-129, 160-165, 201 covering indexes, 585 407-408, 428-429 transformation scripts, Create Process task, 50 Execute SQL task, 185 CreateCustomToolTip 292-294, 306-308 transformations, method, 645 files, 151-152 167-169 CreateProcess object, FTP task, 327-331 UDL files, 111 428 global variables, 55, utility packages, 194-197, 372 384-386 creating INI files, 373-374 variables, 360 ActiveX Script task, local variables, 192 Write File transforma- 346-348, 362-363 LocalCubeTask, 657 tion, 170-171 Analysis Services lookups, 199-200 CreationDate property, Processing task, loops, 352-355, 542 466 420-421 Message Queue task, Application object, 446 CreatorComputerName 400-403 Bulk Insert task, 270, property, 466 multiphase data pump, 287-290 CRM (Customer 243 columns, 167-169 Relationship objects, 31, 312-313, COM object, 243 Management), 60 360 connections, 512-513, packages, 31-32, cubes 553 497-499, 613 creating, 656-657 constants, 374 Parallel Data Pump fact tables, 84 Copy Column transfor- task, 254-257 processing, 413-414 mation, 168-169 parameter queries, 217 updating, 414 cubes, 656-657 precedence constraints, CurrentPhase property, custom transformations, 473-474 232 664 Read File transforma- Data Driven Query task, CurrentSource tion, 170-171 216-219, 223-226 property, 232 source columns, data files, 7-8, 111, 373 custom tasks 159-160 Data Mining Prediction creating, 634-635 SQL queries, 372-373 Query task, 421-423 Description property, steps, 470 destination columns, 639 subordinate dimensions, 159-160 98
custom tasks 684 errors, 650-652 D processing, 502 events, 650-652 queries, 253, 514-515 Execute method, data reading, 483 640-645 adding, 89-90, 220 returning, 464-465 interface, 636-637 Analysis Services sources, 102, 191, 419, log files, 650-652 Processing task, 93 460 Name property, 637-638 archiving, 38, 60 storage, 60, 72, 74, 76 Properties collection, business analysis, 64-69 summarization, 37 639-640 cleansing, 89, 91 transactions, 63-64 referencing, 635, clustering, 67 transformations, 7-8, 49, 653-654 connections, 37 61-63, 228 registering, 652-653 consolidation, 36-37 types, 311 threads, 653 copying, 313-314 warehousing, 37-38 VBScript, 654-655 Data Mining Prediction Web clickstream, 69 Visual Basic, 653-655 Query task, 93 see also Meta Data custom transforma- decision trees, 67 Services tions, 152 destination tables, Data Driven Query task creating, 664 277-278 ActiveX scripts, 214 error definitions, 676 duplication, 37 bindings table, 214 PreValidateSchema Dynamic Properties connections, 102 method, 667-669 task, 370-371, 373 creating, 216-219, ProcessPhase method, enterprise data flow, 61 223-226 672-676 errors, 554-555 executing, 26 properties, 665-667 exporting, 427, 596 hierarchy, 617 registering, 677 history, 68 parameter queries, starting, 664-665 homogenization, 37 214-216 ValidateSchema Import/Export Wizard, performance, 215, 582 method, 669-671 519-520 programmatic identifier, Customer Relationship Information Model, 598 622 Management (CRM), Internet, 60 source queriesevents, 60 loading, 569-572 214 marts, 37-38 tables, 220-222 CustomTask property, merging, 206 transformation status, 615-616, 619 messages, 396-399 219-220 CustomTaskUI inter- mining, 45, 67-68, 93, Data File Type prop- face, 645-649 419, 421-423 erty, 285 missing, 205 Data Lineage Fields modifying, 201, 503 field, 82, 85 moving, 60 OLTP systems, 68 Data Link files, 109-111
destination files 685 Data Pump task, DataPumpOptions queries, 218 661-662 property, 158 records, 221 Data Transform task, DataPumpTask object, rows, 518 47 136, 612 tasks, 645 Data Transformation DataSource property, delimited text files, 271 Services, see DTS 108, 415 Description property Data Transformation DataType property, ActiveX Script task, 347 task, see Transform 159, 197 Connection object, 112 Data task CustomTask interface, DateTime in ActiveX 637, 639 databases Script transformation, Data Mining Prediction copying, 52, 310-311, 573 Query, 419 316 DateTime String trans- Dynamic Properties creating, 341-342 formation, 148-149, task, 367 destination, 311, 508 171, 573 Execute Package task, information models, DB providers, 102 380 598-599 debugging, 55 Execute Process task, location, 508-509 ActiveX scripts, 428 moving, 502-503 558-561 Package object, 368, objects, 310 Enterprise Manager, 436-438, 465 properties, 419 561-563 PackageInfo object, 445 queries, 294 Package Manager, Send Mail task, 407 replication, 585 561-563 Step object, 497 repository, 598 Decimals property, Task object, 619 source, 311, 508 666-667 Transform Data task, tables, 310 129-130 transferring, 336-337 decision trees, 67 transformations, 599 DesignerSettings prop- declaring variables, upgrading, 36, 502, 504 erty, 626 192-193, 446, 450, 531 users, 311 destination files, 116 decompression, text databases servers Bulk Insert task, 270 files, 427 Bulk Insert task collections, 252 default settings columns, 139-141, 143 executing, 26 configuration, 311 creating, 159-160 Copy SQL Server Transformation Flags databases, 311, 508 Objects task, 26 dialog, 145 error rows, 49 Execute SQL task, 26 Transfer Databases task, deleting Import/Export Wizard, 26 DTS packages, 21 516-518 messages, 399 objects, 312-313 DataDrivenQueryTask packages, 443 referencing, 183 object, 218
destination files 686 rows, 517-518 dividing, see splitting Query Designer, source files, 276-281 documentation, pack- 132-133 strings, 370 age connections, replication, 521 Transfer Databases task, 628-631 reports, 406 335 retrieving, 14 DropDestination Transform Data task, saving, 14, 521, 523 ObjectsFirst, 313 137-139 scheduling, 24-25, values, 202 DTS packages (Data 511-512, 521-522 Transformation Send Mail task, 406 DestTranslateChar Services), 16, 40, 42, steps, 534 property, 317 460 storing, 14-15 DestUseTransaction Application hierarchy, structured storage file, property, 317 613, 625-628 15 development errors, architecture, 8, 10 templates, 54 554 Browser, 591 Transact-SQL code, Dimension Keys field bulk copy, 426 549-552 (fact tables), 82 caches, 56 Transformation Status connections, 10, value, 188 dimension tables, 79 103-104 versions, 16, 467 fields, 85 data transformations, Visual Basic, 15, 53 loading, 90-92 7-8 see also packages records, 84-85 deleting, 21 size, 84 DTSIsoLevel transac- Execute Package task, subordinate, 92, 95-98 tions, 483-484 42, 44 updates, 94-98 DTSLineage variables, Execute Process task, directories 407-408, 426-429 602 manipulating, 357 executing, 23-24, 367, DTSMQMessage object, moving, 326 511-512, 521 395 script files, 316-317 FTP task, 42 DTSMQType properties, dirty read, 483 Import/Export Wizard, 394 disabling 34-36, 504 DTSPackageDSO, 460 constraints, 281 Information Model, 599 DTSRun utility, 427 steps, 480, 496 logs, 56 arguments, 20-21 Message Queue task, Disconnected Edit tool, Dynamic Properties 42, 44 29-30, 54 task, 366 multiphase data pump, displaying Execute Process task, 228-230 commands, 21 378 passwords, 21 objects, 318-320 packages, 22-23 performance, 122-123 packages, 457-458 Windows, 53 precedence constraints, 473-474
errors 687 DTSStepScriptResult editing environment variables, scripts, 495 objects, 29-30 374 DTSTransformPhaseInfo overwriting, 31 ErrorIfFileNotFound object, 232-233 properties, 29-30 property, 170 DTSTransformStat val- email 406 ErrorIfReceiveMessage ues, 188-191, 219-220 empty strings, 203-204 TimeOut property, DTSWiz command-line enabling 400 utility, 505 constraints, 281 errors duplication, data, 37 lineage variables, ActiveX, debugging, 602-604 558-561 DWGuide tool, 599-600 multiphase data pump, administration notifica- Dynamic Properties 228-230 tion, 558 task, 40, 42-43, 326 properties, 283 custom tasks, 650-652 assignment, 367-368, triggers, 282 definitions, 676 370-371 development, 554 encryption constants, 374 Enterprise Manager, commands, 22 creating, 375-376 561-563 packages, 443-445 data files, 373 format files, 275 parameters, 20 DTSRun utility, 366 generating, 49-50 environment variables, enterprise data flow, global variables, 195 374 61 handling, 154-156, 361, files, 366 Enterprise Manager 540-541, 548-549, 554 global variables, 372 packages, 17 Import/Export Wizard, INI files, 373-374 debugging, 561-563 523 object models, 28-29 wizards, 505 incorrect data, 554-555 packages, 366 entrance functions, local variables, 193 programmatic identifier, 244-245 lookups, 557 623 maximum, 286 entry function, 361 properties, 367-370 On Transform Failure queries, 372-373 EnumPackageInfos subphase, 238-239 statements, 366 method, 447 Package Manager, EnumPackageLineages dynamic values, 383 561-563 method, 608 packages, 481 EnumPackageLogRecor raising, 555-557 ds method, 458 records, 557-558 E EnumStepLogRecords rows, 49, 556 E-commerce, 60 method, 458 storing, 340 EnumTaskLogRecords transactions, 558 Edit method, 645, 647 method, 458 troubleshooting, 557
events 688 events programmatic identifier, Data Driven Query task, code, 42 622 26 custom tasks, 650-652 properties, 426, 428 DTS packages, 23-24, handling, 538-540 Visual Basic, 429-430 367, 427, 511-512, ExceptionFileName Execute SQL task, 40, 521, 549-552 property, 154 50 DTSRun utility, 22-23 connections, 102 Execute Package task, exceptions in rows, 191 creating, 292-294, 26, 379-380 Execute method context, 25-26 306-308 CustomTask interface, FTP processes, 427 data, 90 640-645 Import/Export Wizard, executing, 26 Lookup objects, 200 523 input parameters, Execute Package task, 294-296 Insert Failure/Success 40 output, 51 phase, 231, 233 creating, 379-380 output parameters, On Batch Complete DTS packages, 42, 44 296-298 phase, 231 executing, 26 programmatic identifier, packages, 16-22, global variables, 622 458-460, 477-480, 380-383 properties, 294 542-545 NestedExecutionLevel queries, 50 Parallel Data Pump task, property, 383-384 recordsets, 51 26 packages, 378, 380 remote servers, 301-306 Post Row Transform programmatic identifier, rowsets, 299-300 phase, 231 622 SQL statements, Post Source data phase, properties, 380 300-301 231, 233 RepositoryDatabase stored procedures, 301 Pre Source phase, 230, Name, 380 233 ExecuteCommandBy ServerUserName, 380 Pump Complete phase, ID method, 506 utility functions, 378, 231, 233 384-386 ExecuteInMainThread queries, 220 Visual Basic, 387-389 property, 480, 496 remote servers, 378 Execute Process task executing Row Transform phase, bcp commands, 427 Batch Complete phase, 230, 233 bulk copy, 426 233 scripts, 182-183, 186 creating, 407-408, bcp commands, 427 Send Mail task, 406 428-429 Bulk Insert task, 26 steps, 471, 476-477 DTS packages, 426, 427 Called Package, 385 text file decompression, DTSRun utility, 378 Copy SQL Server 427 executing, 26 Objects task, 26 transaction steps, 487-490
FTP task 689 Transform Data task, 26 FailPackageOnTimeout Find File task, 655-656 Transform Failure property, 428 FinishTime property, phase, 231, 233 failure precedence con- 497 execution context, straints, 476 FirstRow property, 156 25-26 Fast Load, 570 fixed values, 383 ExecutionResult FastLoadOptions prop- fixed-length text files, property, 497 erty, 157-159, 217, 271 ExecutionStatus 570 Flags parameter, 160, property, 477, 497 fetching 439-440, 442 ExecutionTime buffer size, 156, 578 flattened mode, 253 property, 497 rows, 189 Folder property, 656 Explicit Global fields Variables tool, 55, 465 adding, 277-278, 280 For Next loop, 360 exporting dimension tables, 85 FOR XML statement, data, 427, 596 fact tables, 82-84 40, 464-465 XML, 40 sorting, 278-279 ForceBlobsInMemory updating, 220 property, 147 extended objects, 618, 622 File Transfer Protocol ForceSourceBlobs task, see FTP task Buffered property, Extensible Markup Language, see XML FileAttachments 147 property, 407 FOREIGN keys, 316 FilePath property, 170 format files F files auto-generating, 52 accessing, 360 Bulk Insert task, fact tables appending, 152 271-272 cubes, 84 creating, 151-152 error messages, 275 fields, 82-84 finding, 655-656 generating, 274-276 loading, 92-93 format, 271-276 structure, 272-274 properties, 415 location, 508-509 troubleshooting, 275 records, 82-83 manipulating, 357 freestanding icon, star schema, 78-79, 92 moving, 151, 326 173-176 failing packages, 481, naming, 380, 509, 656 FTP task, 44 555 packages, 442-443, creating, 327-331 FailPackageOnError 450-451 data, 89 property, 466, 555 text, 271 directories, 326 FailPackageOnLog filters DTS packages, 42 Failure property, 466 automating, 415-416 Dynamics Properties messages, 398-399 task, 326 package log files, 457 files, 326
FTP task 690 processes, 427 GetUIInfo method, 645 shape language, 249 programmatic identifier, global variables, 27, subsets, 249 623 624 transformations, 248 properties, 327-329 case sensitivity, 197-198 hierarchy source location, 327 child packages, 380-383 Data Driven Query task, full cube updating, 414 creating, 55, 194-197 617 full outer joins, 206 Dynamic Properties dimension tables, 86 task, 372 DTS Application object, FunctionEntry property, messages, 397-399 625-628 172 methods, 198 objects, 613-618 FunctionName prop- packages, 21, 194 Parallel Data Pump task, erty, 347, 496 Pre Source phase, 617 functions 235-237 Task object, 614 calling, 535 referencing, 194, 196, Transformation object, entry, 361 349 617 Pump Complete data sending, 392 history of data, 68 phase, 234-235 values, 53, 346, 368, HOLAP (Hybrid OLAP), user-defined, 57 378 76 GoTo command, 360 homogenization of GROUP BY clause, 584 data, 37 G HTML files, 102 generating error files, 49-50 H format files, 52, handling I 274-276 errors, 361, 540-541, Get function, 666-667 icons, freestanding, 554 173-176 GetDefaultProfileName events, 538-540 method, 408 ID property, 104-107 headers, packages, 531 GetDTSVersionInfo identity columns, 283, Help method, 645, 649 method, 467 518 hierarchical rowsets, GetExpandedProcess Import/Export Wizard, 249-250 CommandLine, 428 502, 568 child rowsets, 249 calling, 504-507 GetObjectForTransfer keywords, 249 column mappings, method, 317 mode, 252-253 519-520 GetRootItem method, Parallel Data Pump connections, 512-513 506 task, 251 data, 503, 519-520 parent rowsets, 249 GetSQLNamespace databases, 504 queries, 251 Object method, 506
lineage variables 691 DTS packages, 521-523 InsertQuery property, J–K error messages, 523 218 executing, 523 installation JITDebug property, 626 packages, 17 ATL transformation, jobs, transferring, 338- rows, 156 662-663 339 SQL Server Objects, MSMQ client, 392 Join Transaction If 503, 513-514 Visual Basic, 526 Present option, 484 tables, 516-518 instantiating OLE DB joins Transform Data task, components, 467 adding, 91 129 XML, 40 integer data types, 197 packages, 392 integration transactions, 482, 496 IncludeDependencies property, 313 bulk copy and DTS JScript, 187 packages, 426 Just-In-Time debug- incremental cube DTS packages with ging, 55, 559-560 updating, 414-416 Send Mail task, 406 indexes Dynamics Properties Keep Nulls property, copying, 316 task, 326 282-283 moving, 311 XML, 56 numbers, 451 keys, 92 interface, tasks, objects, 317 636-637, 661-662 tables, 584-585 Internet views, 57 clickstream data, 39-40 L information models, data, 60 Language property, 172 598-599 InTransaction property, LastOwnerTaskName inherited transactions, 112 property, 112 493 InUse property, 112 LastRow property, 156 INI files, 373-374 IsNTService property, Level Members field Initialize method, 408, 408 (dimension tables), 506, 645, 648 isolation levels, 85-86 InMemoryBlobSize 483-484 property, 147 limiting IsOwner property, 446 rows, 351-352 input parameters, 171, IsPackageDSORowset tasks, 477 294-296, 419 property, 496 line labels, 360 inserting Item method lineage variables batch size, 578 collections, 624 column-level, 600-601 commit size, 159, 217 SavedPackageInfos col- enabling, 602-604 Insert Failure/Success lection, 451 phase, 231, 233 methods, 624 ItemType property, 413
lineage variables 692 meta data, 593, 606-608 Dynamic Properties packages packages, 458-460 task, 375-376 connections, 628-631 row-level, 602-605 Edit method, 647 creating, 18 saving, 605 entrance function, events, 650-651 LineageFullID parame- 244-245 lineage variables, ter, 458, 608 errors, 540-541, 676 459-460 Execute method, log files, 452-453, LineageOptions prop- 641-645 457-458 erty, 465, 604-605 Execute Package task, naming, 448-449 LineageShortID para- 388-389 objects, 652 meter, 608 Execute Process task, properties, 532 linked servers, 463-464 429-430 saving, 537-538 listings Execute SQL task, storing, 440-441 ActiveX scripts, 302-308 Parallel Data Pump task, 211-212, 362-363 fields, 278-281 254-257 ADO recordset, File Transfer Protocol phases, 234-235 355-356 task, 329-331 Post Source Data Phase, Analysis Services format files, 273, 279, 242 Processing task, 357 Pre Source phase, 420-421 functions, 535, 536 236-237 Bulk Insert task, Help method, 649 PreValidateSchema 287-290 hierarchical rowsets, method, 668-669 column names/ordinal 249-250 ProcessPhase method, numbers, 576-577 Import/Export Wizard, 672-676 Copy Column 507 public variables, 531 Transformation, Initialize method, 648 Pump Complete Phase, 168-169 lineage data, 606-608 243 custom tasks, 654-655 log files, 452-453 records, 208-209 Data Driven Query task, loops, 354-355 rows, 352 221-226 Main function, 535 sources, 184-187 Data Mining Prediction Message Queue task, SQL Namespace, Query, 421-423 400-403 543-545 Decimals property, New method, 646 steps, 497-499 666-667 objects, 318-320 text files, 72 directories, 357 On Batch Complete Transact-SQL Code, DTS connections, Phase, 240-241 549-552 114-115, 533-534 On Insert/Transform transactions, 492-493 DTSTransformPhase Failure subphase, Transfer Databases task, Info object, 232-233 238-239 341-342 OnQueryCancel event, Transfer Object task, 539 321-323
meta data 693 Transform Data task, lookups, 50, 624 merging 161-165, 260 ActiveX scripts, 200 data, 206 ValidateSchema method, creating, 199-200 records, 210-212 670-671 data, 201 messages, 40, 392 VBScript, 627-628 errors, 557 asynchronous process- Visual Basic code, methods, 200 ing, 392 408-410 null values, 204 boxes, 386 Workflow ActiveX properties, 199 creating, 400-403 Script, 489-490 tables, 91 data files, 395 XML, 73 loops, 352-355, 496, deleting, 399 loading 542 DTS packages, 42, 44 data, 569-572 Lowercase transforma- ErrorIfReceiveMessage dimension tables, 90-92 tion, 149, 169 TimeOut, 400 fact tables, 92-93 filtering, 398-399 packages, 537-538 global variables, 395 local execution, 26 programmatic identifier, M 623 local variables, 192-193 Main function, 531-535 properties, 394 LocalCubeTask, 656-657 queues, 394 location, databases, manipulating receiving, 392, 395-397, 508-509 directories, 357 400 strings, 202-203 sending, 392, 394-395 Lock method, 198 log files MAPI (Microsoft strings, 395 custom tasks, 650-652 Messaging API), 406 text, 407 packages, 451-458 mapping, 139-141, 143, timeouts, 399-400 saving, 452 184-187 meta data, 70 servers, 454-455 marts, data, 37-38 browsing, 591, 593, 595 viewing, 455-456 business, 589 MaxCacheRows prop- LogFileName property, erty, 199 column-level lineage, 466 600-601 MaxConcurrentSteps DWGuide, 599-600 logging DTS packages, property, 465, 583 information models 56 Maximum Errors prop- databases, 598-599 logins erty, 286 lineage, 593, 606-608 copying, 316 moving, 334 MaximumErrorCount methods, 408 packages, 595, 608 property, 156 moving, 311 repository databases, passwords, 20 MDX queries, 133-134 598 transferring, 337 Measures field (fact retrieving, 605-608 LogServer properties, tables), 82-84 row-level lineage, 466 602-605
meta data 694 saving, 590 Microsoft Excel 2000, MOLAP scanning, 600-601 102 (Multidimensional Software Development Microsoft Repository, OLAP), 76 Kit (SDK), 599 see meta data monitoring storing, 334, 449-450 constraints, 281-282 Microsoft SQL Server, technical, 589 Send Mail task, 406 102 upgrading, 588 Microsoft Visual Basic MonthLongName prop- variables, 602-605 Scripting Edition, see erty, 171 methods VBScript moving Add, 624 Microsoft Messaging data, 60, 311 collections, 625 API (MAPI), 406 databases, 310-311, Connection object, 122 502-503 CustomTask interface, Middle of String, 149, defaults, 311 640-645 151 directories, 326 CustomTaskUI inter- Middle of String trans- files, 151, 326 face, 645-649 formation, 169-170 indexes, 311 GlobalVariable2 object, mining models, 413, logins, 311 198 419 meta data, 334 Insert, 624 missing data outer objects, 317, 510-511 Item, 624 joins, 205 permissions, 311 Logoff/Logoff, 408 referential integrity Lookup object, 200 modeling, 80-82 constraints, 311 New, 624 modifying rules, 311 objects, 547, 612 connections, 121-122 SQL Server Objects, Package object, 467 data, 201, 503 503, 513-514 ResolveName, 408 Description property, stored procedures, 311 SavedPackageInfos col- 368 tables, 311 lection, 451 packages, 568 transformation logic, Send Mail task, 408 Parallel Data Pump 578-581 ShowAddressBook, 408 task, 248 triggers, 311 SQL Namespace, 506 properties, 43, 54, 348, views, 311 Transfer Objects task, 541 317-320 MSMQ client, 392 SourceQuery property, UnitializeMAPI, 408 369 multidimensional data- Microsoft Access, 36 SQL statements, base schema, 74 Microsoft Access 2000, 300-301 multidimensional mod- 102 Transform Data task, eling, 80-82 236 multiphase data pump, 228-230, 243-245
On Batch Complete phase 695 Multiple Phases in the NumericScale property, referencing, 350-351, Transform Data task, 160 612 42 NumRetriesOnSource retrieving, 317, 451 multipump data pump, property, 328 SavedPackageInfo, 28 230, 231, 233-239 ScriptingLanguageInfo, 28 selecting, 413 O SQL Namespace, 506 N Steps, 27 objects Task object, 27, 615-616 naming ActiveX scripts, 42 TransformationInfo, 28 ActiveX Script task, 347 adding, 317-320 variables, 193-194, 360 columns, 159, 575-577 Application, 28 viewing, 596 CustomTask interface, categories, 612 637-638 clearing, 317-320 OEMFile property, 170 files, 509 collections, 625 OLAP objects, 317, 622 COM, 27 Analysis Services packages, 436-438, 443, Connections, 27 Processing task, 44 445, 447-449, 465 copying, 314-315, 502 data, 64-67, 119 properties, 106, 367, creating, 31 servers, 76 407, 419, 428 databases, 310 Services cubes, 133-134 steps, 470-471, 497 destination, 312-313 OLE Automation Task object, 619 displaying, 318-320 packages, 18-19 Transform Data task, DTS Application, stored procedures, 129 625-628 545-549 Transformation object, Dynamic Properties OLE DB provider, 109 147 task, 368-370 connections, 102, NestedExecutionLevel editing, 29-30 114-115, 119, 121 property, 383-384, 465 extended, 618, 622 data sources, 102 global variables, 27, New method, 624, instantiating, 467 197 645-646 OLTP (On Line hierarchy, 613-618 non-repeatable read, indexes, 317 Transaction 483 methods, 547, 612 Processing), 472 normalized database models, 28-29 data, 68, 427 schemas, 74 moving, 503, 510-511 reports, 38 naming, 317, 622 Web interface, 38 null values, 203-204, 282-283 Package, 27 On Batch Complete processing, 413-414 phase, 231, 240-241 Nullable property, 159 properties, 547-548, 612