The first thing we're going to do is add a SQL Server connection to the designer sheet. Find it in the connections section
and either double click on it or drag it into the designer. Figure 1.5 shows the connection properties that we must fill in for the SQL
Figure 1.5 SQL Server Connection Properties.
There's a few things to explain on this screen
1. New Connection /Existing Connection. If you choose New connection then you will need to enter all the connection details for
the connection. If you choose to base your new connection on an existing one by choosing Existing Connection then you can choose from a
connection already in Designer and the details will be entered automatically.
2. Data Source. In some Microsoft products this means the name of the server or database file that we want to connect to. Here though it
means what is seen elsewhere as the Provider. We can choose from a selection including but not limited to Text Files, dBase and Microsft Excel.
3. Server. In this instance because we've chosen a SQL Server connection then this is the name of the server we want to connect to. If we choose a different
Data Source then you will see this section of the screen change and prompt for the equivalent property for the Data Source. Don't worry though it's
obvious from the boxes what information DTS is asking for.
4. Authentication. Here we need to provide Logon information to the server. This can either be through Trusted (Windows) or SQL Server authentication.
4. Database. From the dropdown here we provide the name of the database we want to connect to.