Microsoft SQL Server 2000 Data Transformation Services- P15

Microsoft SQL Server 2000 Data Transformation Services- P15: Data Transformation Services in Microsoft SQL Server 2000 is a powerful tool for movingdata. It’s easy to use, especially when you’re creating transformation packages with the DTSWizard. But it’s also a flexible tool that you can customize to gain a high degree of controlover the transformation of your data.
Nội dung trích xuất từ tài liệu:
Microsoft SQL Server 2000 Data Transformation Services- P15 Extending the Power of DTS676 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 32Registering the Custom TransformationThe ATL COM wizard creates the code to register the custom transformation during the buildprocess. You can register the transformation on other computers by using the regsvr32command-line utility:regsvr32 \Average.dllUnlike the registration of custom tasks, there are no special registry entries that need to becreated for a custom transformation. 32Conclusion TRANSFORMATION CREATING ADTS provides many excellent built-in tools. One of the best characteristics of DTS, though, is CUSTOMits 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 areusing a particular ActiveX transformation script repeatedly. A INDEXaborting Transform Data task, 191accessing COM objects, 27 files, 360 package log files, 456-458AcquireConnection method, 122activating debugging, 55Active Template Library, see ATLActiveX 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 adding680 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 ...