tailieunhanh - Microsoft SQL Server 2000 Data Transformation Services- P7

Microsoft SQL Server 2000 Data Transformation Services- P7: 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. | Other Data Movement and Manipulation Tasks 276 Part III T ext File Piopeitie Specify Colman Delanitet Specfy the souice hie cdurn dekmiter type Specfy comma. Ida. semcckcn. or arothei character Sack I Firith Caned I Hi Figure Text qualifiers are needed when commas occur in the data of a comma-delimited text file. Use the Transform Data task for these files. The second way to create a format file is to use the bcp utility interactively. Open a Command Prompt and type in a bcp command. The following command could be used to generate the format file in Listing bcp out c temp -Usa The bcp utility will ask you a number of questions about the fields in this bulk copy. One of the last questions you will be asked is whether or not you want to create a format file. If you say yes you will be asked for the host filename which is used as the name of the format file that will be created. Reconciling Differences Between the Source and the Destination By default a bulk insert takes data from the fields of a source file and puts it into the same number of fields using the same order in the data destination. If you don t have the same number of fields or if the fields are in a different order you usually have three options Use a view in place of the destination table. Create the view so that its fields line up with the fields of the source text file. This is usually the easiest option to implement. Use a format file. This option is usually harder to implement but it gives the most flexibility. Change the destination table so its fields match the fields in the text file. The Bulk Insert Task Chapter 11 T71 Extra Fields in the Data Destination Table You may have fields in the destination table that do not exist in the source text file as shown in the following example. The destination is the Stores table in the Pubs database which has the following fields stor_id stor_name stor_address city state zip The source text file is missing the last three