Database Reference
In-Depth Information
Alter Table [dbo].[FactSales] With Check Add Constraint [FK_FactSales_
DimTitles]
Foreign Key ([TitleKey]) References [dbo].[DimTitles] ([TitleKey])
Alter Table [dbo].[FactSales] With Check Add Constraint [FK_FactSales_DimDates]
Foreign Key ([OrderDateKey]) References [dbo].[DimDates] ([DateKey])
Alter Table [dbo].[DimTitles] With Check Add Constraint [FK_DimTitles_DimDates]
Foreign Key ([PublishedDateKey]) References [dbo].[DimDates] ([DateKey])
2.
Highlight this code, right-click it, and choose Copy from the context menu.
3.
Edit the Add Foreign Key Execute sQL Task by right-clicking the task and selecting
Edit from the context menu.
4.
select the Connection property. A dropdown box appears.
5.
Configure the Connection property by selecting the option of DwPubssales from the
dropdown box, as shown in Figure
7-28
.
6.
select the sQL statement property. An ellipsis button appears.
7.
Configure the sQL statement property by clicking the ellipsis button and pasting the
code from Listing 7-5 that you copied earlier into the Enter sQL Query dialog that
appears.
8.
Remove any sQL go statements from your code.
9.
Click the oK button to close the Enter sQL Query dialog.
10.
Click the oK button to close the Execute sQL Task editor.
Reset the Database Objects Before Testing Your ETL Process
with the three Execute sQL tasks configured, it is important to test that they work correctly. But, before we
do so, let's make sure that the database is in its normal empty state by running the sQL code that re-creates
the database.
1.
Locate the Create DwPubssales script.sql file in Visual studio or in sQL server
Management studio. You should find this file under the DwPubssales solution folder
you created in Exercise 5-4.
2.
open this file and execute its code. You can do this by right-clicking an area in the
sQL code window to bring up the context menu and select Execute sQL. You may
remember doing this back in Chapter 2 (Figure 2-15).
3.
when the Connect to Database Engine dialog box appears, type in your server name,
and click the Connect button.
4.
in a few seconds, you receive a message stating “The DwPubssales data
warehouse is created.”
