Database Reference
In-Depth Information
ssis has a data source view object that can be used in conjunction with your ssis packages. This may
make the concept of views seem redundant, but in practice, you will find that the ssis data source views are some-
what limited since they can only be used within an ssis project, whereas sQL views can be used by any application
that connects to sQL server.
Note
The view is a very simple and effective tool, but it does have some disadvantages. You cannot define
parameters on a view. Parameters allow you to pass in specific arguments to get back differing results. This is a
useful technique when incrementally loading a data warehouse. For example, the code in Listing 6- 23 filters out
data that was not added on today's date. Because SQL views cannot contain parameters, this query could not be
saved inside a Create View statement.
Listing 6-23. A Select Statement with a Parameter
Select
[OrderNumber]=ord_num
, [OrderDateKey]=DateKey
, [TitleKey]=DimTitles.TitleKey
, [StoreKey]=DimStores.StoreKey
, [SalesQuantity]=qty
From pubs.dbo.sales
JOIN DWPubsSales.dbo.DimDates
On pubs.dbo.sales.ord_date=DWPubsSales.dbo.DimDates.date
JOIN DWPubsSales.dbo.DimTitles
On pubs.dbo.sales.Title_id=DWPubsSales.dbo.DimTitles.TitleId
JOIN DWPubsSales.dbo.DimStores
On pubs.dbo.sales.Stor_id=DWPubsSales.dbo.DimStores.StoreId
Where pubs.dbo.sales.ord_date=@TodaysDate - This will not work in a View!
Stored Procedures
Like views, stored procedures consist of a named set of SQL statements. Unlike views, stored procedures can
contain multiple statements, work with variables, and process transaction statements. Stored procedures can
therefore be quite complex and contain hundreds of lines of SQL code, but they can also be as simple as a
saved select statement. For example, Listing 6-24 holds the same select statement as the one in the view created
in Listing 6-22. Notice that the syntax looks almost the same. The only difference is the command CREATE
PROCEDURE in place of CREATE VIEW .
Listing 6-24. Creating a Stored Procedure for ETL Processing
Create Procedure pEtlFactSalesData
as
Select
[OrderNumber]=ord_num
, [OrderDateKey]=DateKey
, [TitleKey]=DimTitles.TitleKey
, [StoreKey]=DimStores.StoreKey
, [SalesQuantity]=qty
From pubs.dbo.sales
 
 
Search WWH ::




Custom Search