Database Reference
In-Depth Information
, [PublisherKey]=[DWPubsSales].[dbo].[DimPublishers].[PublisherKey]
, [TitlePrice]=Cast( isNull( [price], -1 ) as decimal(18, 4) )
, [PublishedDateKey]=isNull( [DWPubsSales].[dbo].[DimDates].[DateKey], -1)
From [Pubs].[dbo].[Titles]
Join [DWPubsSales].[dbo].[DimPublishers]
On [Pubs].[dbo].[Titles].[pub_id]=[DWPubsSales].[dbo].[DimPublishers].
[PublisherId]
Left Join [DWPubsSales].[dbo].[DimDates] -- The "Left" keeps dates not found in
DimDates
On [Pubs].[dbo].[Titles].[pubdate]=[DWPubsSales].[dbo].[DimDates].[Date]
4.
Edit the olE dB data source as shown in Figure
8-23
. (we are using the
dwPubssales connection, but reference the original Pubs.dbo.titles table in our
code.)
Figure 8-23.
The OLE DB source for the Fill DimTitles Data Flow Task
5.
Click the Preview button to verify that the query works successfully.
6.
Click the Columns page to force the XMl to be written properly in the .dtsx file.
7.
Close the olE dB source Editor window by clicking the oK button.
8.
Add an olE dB data destination to the data Flow surface. Rename it to Fill dimTitles
olE dB destination.
9.
Connect the data flow path from the source to the destination.
10.
Edit the olE dB data destination so that it imports data to the dimTitles table
(similar to Figure
8-14
)
11.
Click the Mappings page to force the XMl to be written properly in the .dtsx file and
verify that the mappings look logically correct.
