Database Reference
In-Depth Information
Configure the Drop Foreign Keys Execute SQL Task
with the sQL script file added and displayed in Visual studio, it is time to use that code to configure your
Execute sQL Tasks. For each task, locate code within the file, copy the code by right-clicking it, choose Copy
from the context menu, and paste it into the appropriate ssis task.
1.
Locate the code labeled “1a)” and verify that its purpose is to drop the foreign key
constraints. The code you are looking for should look like Listing 7-3.
Listing 7-3
.
Code That Drops the Foreign Key Constraints
-- 1a) Drop Foreign Keys
Alter Table [dbo].[DimTitles] Drop Constraint [FK_DimTitles_DimPublishers]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_
DimAuthors]
Alter Table [dbo].[FactTitlesAuthors] Drop Constraint [FK_FactTitlesAuthors_
DimTitles]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimStores]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimTitles]
Alter Table [dbo].[FactSales] Drop Constraint [FK_FactSales_DimDates]
Alter Table [dbo].[DimTitles] Drop Constraint [FK_DimTitles_DimDates]
2.
Highlight this code, right-click it, and choose Copy from the context menu. in a
moment you will paste it into the Execute sQL Task called Drop Foreign Keys
Execute sQL Task.
3.
Edit the Drop Foreign Keys Execute sQL Task by right-clicking the task and selecting
Edit from the context menu.
4.
select the Connection property. A configuration dropdown box appears.
5.
Configure the Connection property by selecting the option 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-3 that you copied earlier into the Enter sQL Query dialog that
appears (Figure
7-29
).
8.
Verify that there are no sQL go statements your code. sQL go statements cause the
Execute sQL Task to fail.
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 Truncate Tables Execute SQL Task
1.
Locate the code labeled “1b)” and verify that its purpose is to truncate the tables'
data. The code you're looking for should look like Listing 7-4.
