none
Hands-On Lab for SSIS in SQL Server 2008 R2 Training Kit has some problems

    Question

  • Hi everyone,

    I'm trying to learn SSIS and applying related hands-on labs in SQL2008R2 DevsTraining. The first lab I've applied is "Hands On Lab: Building Your First Extract-Transform-Load Process with SQL Server 2008 R2 Integration Services". At "Task 9 – Introducing the Execute SQL Task", it states that we will use "dbo.uspDeleteFactSalesQuota, but in AdventureWorksDW2008R2 datawarehouse, there is no such a stored procedure. I've created it manually to be able to go on and see the results. It is just a simple SP like:

    USE [AdventureWorksDW2008R2]

    CREATE PROCEDURE [dbo].[uspDeleteFactSalesQuota]
    @CalendarYear [smallint]
    AS
    DELETE FROM FactSalesQuota
    WHERE CalendarYear = @CalendarYear
    GO

     It passed through that step but it gave error at "Task 17 – Executing the Package" again. The error message is like that:

    [FactSalesQuota [134]] Error: SSIS Error Code DTS_E_OLEDBERROR. 
    Description: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_FactSalesQuota_DimDate". The conflict occurred in database "AdventureWorksDW2008R2", table "dbo.DimDate", column 'DateKey'.".

    I've compared the records for "DateKey" column in DimDate and my new records via Data Viewer but found nothing. I've insisted to see the result, so drop the constraint. Then my SSIS package has been started to work.

    Is there any problem at my steps? If it is not, I'll think that the training kit is not compatible with the sample db, at least at some points.

    Thanks.

    PS: I've downloaded sample db from that link: http://msftdbprodsamples.codeplex.com/releases/view/55926

    Friday, June 10, 2011 3:07 PM

Answers

  • While my previous post is indeed the underlying problem, the way to solve this is to run the 'Setup_AdventureWorksDW2008R2.sql' file in the source files (path: \Setup\scripts\Tasks\sql). This will create the missing DateKey's in the dbo.dimDate table.

    • Marked as answer by pilgrimus Thursday, July 25, 2013 1:38 PM
    Thursday, July 25, 2013 9:01 AM

All replies

  • May be you better need to mention about this error to whoever published this lab?
    Arthur My Blog
    By: TwitterButtons.com
    Friday, June 10, 2011 3:30 PM
    Moderator
  • This lab is a part of "SQL Server 2008 R2 Update for Developers Training Kit (May 2011 Update)" and it was downloaded from a site belongs to Microsoft:

    http://www.microsoft.com/downloads/en/details.aspx?FamilyID=fffaad6a-0153-4d41-b289-a3ed1d637c0d

    I mentioned the lab name at my previous post: "Hands On Lab: Building Your First Extract-Transform-Load Process with SQL Server 2008 R2 Integration Services".

     

    Friday, June 10, 2011 8:32 PM
  • I could not find the answer anywhere, but I did figure out the underlying problem, so for future reference:

    In the dbo.dimDate table, the 'DateKey's from 20080831 up to 20101101 are missing. This is what gives the error the OP states. A simple way to make it work is to adjust the formula at Task 14, from

    (@[User::Year] * 10000) + (((CalendarQuarter * 3) - 2) * 100) + 1

    to

    (@[User::Year] * 10000) + (((CalendarQuarter * 2) - 1) * 100) + 1

    This way it stays within the range of available DateKey's (atleast when you're looking at the year 2008). Ofcourse now the DateKey's are wrong, but atleast the exercise works!

    Tuesday, July 23, 2013 1:25 PM
  • While my previous post is indeed the underlying problem, the way to solve this is to run the 'Setup_AdventureWorksDW2008R2.sql' file in the source files (path: \Setup\scripts\Tasks\sql). This will create the missing DateKey's in the dbo.dimDate table.

    • Marked as answer by pilgrimus Thursday, July 25, 2013 1:38 PM
    Thursday, July 25, 2013 9:01 AM