Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued) - Create a source text file
(Page 6 of 9 )
Create a new TextFile (source).. called FirstCopy by following the same procedure as before. In the property page of this connection, make the following entries. For file name use the ellipsis ... button to locate the Destin.txt created in the previous step. The result after processing Destin.txt will be transferred to Destin2.txt (SecondCopy).
Create a destination text file Again from the menu connection drag and drop a TextFile (Destination). Set its description attribute as SecondCopy. The file name is Destin2.txt, which is created in a convenient location. Now browse using the ellipsis button to locate this file, while configuring its properties as shown below.
With these added elements, the editor's design pane should have the following elements. The filtered data from SQL Server will be transferred to the Text File. The HelloDTS ActiveX Script Task creates a copy of the TextDts.txt in the computer. The FirstCopy is pointing to the copy. A separate text file, SecondCopy, is created to receive the filtered data from the FirstCopy.
However, there is just one problem with this arrangement. Both FirstCopy and SecondCopy must exist, even if they are empty, before the Transformation Task from FirstCopy to SecondCopy can take place. However, HelloDTS creates the FirstCopy after execution. So to start with, FirstCopy does not exist. We will resolve this after configuring the mapping from FirstCopy to SecondCopy.
Next: Create a Transform Data Task >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
