Using Data Transformation Services, part 2: Using the DTS Designer Objects - Creating a Task to transfer query result to a table
(Page 3 of 6 )
From the Task menu item, select the Transform Data Task. The Select Source Connection pop-up text gets attached to the cursor. Place it on the SQL Server connection, and it immediately changes to Select Destination Connection; now place it on the MS Access database. The two databases get connected by an arrow pointing from the SQL Server to the MS Access as shown. The above sequence is shown in the next three pictures. The arrow represents the task that is added, and it remains to be defined.
Configuring the Transform Data Task A text annotation in green, Transform Data Task, has been added. Place the cursor on the arrow (Transform Data Task arrow), and when it changes to arrows pointing North-South, right click to show the drop down menu. Click on the properties item. The Transform Data Properties window opens up with the Table/View 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.
Choosing the SQL Query radio button, click 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.
Drag and drop the tables, Orders, Order Details, and Products and choose columns from the three tables. There is no specific reason for this choice other than illustrating the method. The Where clause was added to the SQL text as shown to get a reduced set of rows.
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 (not shown here).
Clicking now on the Destination tab pops up the Create Destination Table window as shown. The results from the query are going to be loaded into a table with the default name New Table as shown here.
You may change the name of the table into something meaningful by changing the string "New Table" to something else. Here the default name is accepted. This table structure will be created in the SQLAccessNew.mdb database when the package is executed. At this point, the entire query output is going into the Access table. When you click OK to this Create Destination Table window, you will see the following picture.
The next tab to work on is Transform. When you click on this, the following window shows up with default mappings. The six column mappings can be changed by selectively clicking on the connecting line from source to destination. We will look at transformations in another tutorial; for now, accept the default by clicking OK. We also give a name to the Transform: MapSQLACC. Now if this package were to be executed, the table will be filled with the results of the query.
Using the file menu, the package can be saved with a name. This package is saved as MSA. To save to local server, you may need to provide authentication information. The default shown here should be sufficient for this example.
Next: Creating a connection to MS Excel >>
More MS SQL Server Articles
More By Jayaram Krishnaswamy
/ 27 
