Database Reference
In-Depth Information
Listing 7-4.
Code That Clears the Tables of Data
--1b) Clear all tables data warehouse tables and reset their Identity Auto
Number
Truncate Table dbo.FactSales
Truncate Table dbo.FactTitlesAuthors
Truncate Table dbo.DimTitles
Truncate Table dbo.DimPublishers
Truncate Table dbo.DimStores
Truncate Table dbo.DimAuthors
2.
Highlight this code, right-click it, and choose Copy from the context menu.
3.
Edit the Truncate Tables 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-4 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.
Configure the Add Foreign Key Execute SQL Task
1.
Locate the code labeled “step 3)” and verify that its purpose is to replace the foreign
key constraints that were dropped earlier. The code you're looking for should look
like Listing 7-5.
Listing 7-5.
Code That Replaces the Foreign Key Constraints
-- Step 3) Add Foreign Key s back (Will be used with SSIS Execute SQL Tasks)
Alter Table [dbo].[DimTitles] With Check Add Constraint [FK_DimTitles_
DimPublishers]
Foreign Key ([PublisherKey]) References [dbo].[DimPublishers] ([PublisherKey])
Alter Table [dbo].[FactTitlesAuthors] With Check Add Constraint [FK_
FactTitlesAuthors_DimAuthors]
Foreign Key ([AuthorKey]) References [dbo].[DimAuthors] ([AuthorKey])
Alter Table [dbo].[FactTitlesAuthors] With Check Add Constraint [FK_
FactTitlesAuthors_DimTitles]
Foreign Key ([TitleKey]) References [dbo].[DimTitles] ([TitleKey])
Alter Table [dbo].[FactSales] With Check Add Constraint [FK_FactSales_
DimStores]
Foreign Key ([StoreKey]) References [dbo].[DimStores] ([Storekey])
