Using Data Transformation Services, part 2: Using the DTS Designer Objects - Configuring the Transform Data Task
(Page 5 of 6 )
Place cursor on the Transform Data Task and when it turns into an arrow pointing East<->West, right click the Transform Data Task and select properties. This brings up the following window with several tabs, starting with the Source tab. You may add a description to the Task.
Since further filtering will be carried out instead of a Table/View choice, the SQL query choice is made. This will require you to build a query. Click on the Build Query... button. This brings up the Data Transformation Services Query Builder Window. The list of tables on the left includes not only the New Table created for the MS Access connection, but also the various system tables. Drag and drop the New Table into the Query Builder design pane. Make the choices. A where clause has been added to the SQL statement, which shows up as a filter in the design pane. Also, note that fewer columns from the MS Access table has been specified by this query.
Click OK to this query Builder. This adds the SQL statement to the Source window as shown here. You may test Parse query... to verify the correctness of the query.
Click on the Destination tab, which will opens the window pertinent to the third connection, namely the MS Excel Connection. In the Table Name it comes up with the first worksheet, Sheet1$, and just one field, F1. You may also switch to other sheets, or create a new sheet (sheet is synonymous with table). We assume the default Sheet1$. Now click on the Create... button, which brings up the Create Destination Table window as shown. You can see all the choices you made in the query. If you click OK to this, you will be adding a new work sheet called New Table to your MSA.xls file.
Click OK to the above window, which now shows the meta data that is going into the Excel file as shown here.
Click on Transformations, which takes you to the next window with default transformations. This shows a one to one mapping of the source query to the Excel sheet. Click OK to this to complete the task definition. Now go to menu item Package and save. This will be saved under the same name as the previous save, namely, MSA.
Next: Executing the package and verifying the transfer >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 27 
