Using Data Transformation Services, part 3: Using the DTS Designer Objects (continued) - Creating a Destination text file connection
(Page 3 of 9 )
From the Connection menu item, select the TextFile (Destination)... and drag it to the design pane of the editor. The editor's design pane now contains the SQL Server Connection as well as the TextFile (Destination) connection as shown in the next picture. Additionally, create an empty text file, TextDts.txt, and place it in a suitable location on the C: drive.
The connection properties for the TextFile (Destination..) are as shown in the next picture. Browse with the ellipsis button (...) to point to the TextDts.txt created earlier. This becomes the destination for the results from the Northwind database.
Configuring the Transform Data Task As described in the previous tutorial, connect the two connections by a Transform Data task from the SQL Server to the TextFile. Place the cursor on the arrow (Transform Data Task arrow), and when it changes to arrows pointing North-South (or East-West), right click to show the drop down menu. Click on the properties item. The Transform Data Properties window opens up with the Table/View as default as shown. From this a table or view can be chosen. If you choose a table or view, it can be previewed using the Preview... button. From this menu it is also possible to execute the task (step) as well configure workflow properties.
The Transform Data Task Properties window has several tabs but opens with the Source tab to start with. While it is possible to use the Table/View as is, an SQL Query can be set up as shown. A simple select statement selects the LastName, FirstName, HireDate, City, and PostalCode from the Employees table.
It is also possible to construct the query interactively by choosing the SQL Query radio button, and clicking on the Build Query... button. This opens up the Data Transformation Services Query Designer window as shown. The DTS Query builder is not the same as the Query Analyzer, or the Query Builder in Visual Studio, but is similar in functionality.
When you click to close the window, you are returned to the
Transform Data Task window as shown. Here you can click on
Parse query.. to test whether it has the correct syntax, or
Preview.. the data, which shows up in a
View Data table as shown.
Next: Set up an ActiveX Script Task to access the text file >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
