Using Data Transformation Services, part 2: Using the DTS Designer Objects - Creating an SQL Server connection

(Page 2 of 6 )

We will first start with the SQL 2000 Server. Click on the Microsoft OLEDB Provider of SQL Server in the dropdown menu after clicking on the Connection menu item, or drag and drop the item from the connection collection on the left-hand side onto the design pane. This opens up the Connection properties window as shown. Since we have chosen to work with the Northwind example database, we set the database to Northwind.

It is assumed that the SQL Server is local, and Windows Authentication is going to be used. This adds the SQL Server Connection. If needed, a text annotation can be added and placed in close proximity to the object. The text annotation can be accessed from the menu. The font properties of any added text are configurable.

Creating an MS Access Connection

Create an MS Access database in your file system. It is called the SQLAccessNew.mdb and it is located on the C:\ drive (there will be no user defined tables). Now in the DTS designer, drag and drop a MS Access connection. Browse using the ... button to locate the SQLAccessNew.mdb file on your system. The connection object now appears as shown.

Next: Creating a Task to transfer query result to a table >>
 

More MS SQL Server Articles
More By Jayaram Krishnaswamy

Comments On: Using Data Transformation Services, part 2: Using the DTS Designer Objects

· This is a good article about using the DTS Designer to create DTS packages, but it...    · Everything about DTS is covered by Microsoft, including this one. While this covers...    · have read your articles regarding DTS, they are very useful, but still i have some...    · You seem to have two different servers, and looks like you need to write a trigger...    · Hi Jayaram,I am developer, i am not getting what you exactly saying?Please can...    · I have read your articles and they are very useful,but still i have some...    · The options I suggested are just possibilities. Can they work? I need to test them...    · Hi, I found your DTS lesson very helpful; I looked for a decent tutorial for DTS in...   >>> Post your comment now!