Database Reference
In-Depth Information
In the next exercise, you start the process of configuring and executing the various tasks that make up the
SSIS package that fills the DWPubsSales data warehouse. If at any point you find that you are receiving errors
that are difficult to resolve and you suspect this may be because your database is out of sync with the current
execution of the package objects, keep the option of resetting the data warehouse database in mind!
eXerCISe 7-3. CONFIGUrING aND teStING taSKS
in this exercise, you continue to work on the ssis package outlined in Exercise 7-2.
in the previous exercise, you outlined all of the tasks and organized them within sequence containers. now
you focus on creating two ssis connection managers, configuring three Execute sQL tasks, and testing
that they run successfully. You also add an existing sQL code file containing all the sQL code you need to
complete this exercise to the project.
Important:
Before you begin, remember that in Chapter 6, Exercise 6-1, you created a sQL script file that
contained ETL code and saved it to C:\_Bisolutions\Publicationsindustries\PublicationindustriesETLCode.sql.
You need this file in that location for the current exercise. if you do not have this file for some reason, you can
use the one that comes with the downloadable book files. You will find it at C:\_BookFiles\Chapter07Files\
PublicationindustriesETLCode.sql.
1.
if the solution from the previous exercise is closed, open Visual studio by clicking
the windows start button and selecting All Programs
➤
Microsoft sQL server
2012
➤
Data Tools. Then follow the instructions from Exercise 7-1 to open
C:\_Bisolutions\Publicationsindustries\Publicationsindustries.sln.
Remember to right-click the menu item, select Run as Administrator, and answer Yes to close the UAC dialog!
Create Your Connections
Before you start to configure your ssis tasks, you should create its connections. You need two connections:
one to the Pubs database and another to the DwPubssales database. Let's set those up now!
1.
At the bottom of the ssis package designer, locate the Connection Managers tab;
then right-click its background, and select new oLE DB Connection from the context
menu. The page is similar to what you already saw in Figure
7-25
. The oLE DB
Connection Manager dialog appears (Figure
7-26
).
2.
in the oLE DB Connection Manager dialog, click the new button to create a new
connection. The Connection Manager dialog appears (Figure
7-27
).
3.
in the Connection Manager dialog, type in your sQL server's name, typically the
word
(local)
will suffice unless you are using a named instance of sQL server
(Figure
7-27
). (For more information on connecting to your local server or a named
instance of your server, see Chapter 5.)
4.
on this same dialog, select or type
Pubs
into the “select or enter a database name”
textbox (Figure
7-27
).
5.
Repeat this process to create a new oLE DB connection to the DwPubssales database.
6.
Rename the connections managers to match the naming conventions used in
Figure
7-39
.
