How to handle early arrived facts . I want to generate the surrogate key in the dimension table for the record that doesn't exist in the dimension and load that surrogate key in the fact table .arun

Thanks Craig . Actually I am using the technique the link u posted . But when I am calling stored procedure "EXEC stored procedure ?" I am getting the following error Paramererizes SQL Statement Yields metdata which doesn’t match the main SQL statement Can u help me on that . Thanks once againarun

Here is my stored procedure USE [EDW] GO /****** Object: StoredProcedure [dbo].[Generate_Custkey] Script Date: 05/02/2011 18:55:06 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Generate_Custkey] @CustomerID uniqueidentifier /* The key to find a surrogate for */ AS SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE /* Prevent race conditions */ Declare @Custkey INT,@Customername nvarchar(160),@Street1 nvarchar(250),@Street2 nvarchar(250),@Street3 nvarchar(250),@City nvarchar(80),@State nvarchar(50),@Zip nvarchar(20),@Country nvarchar(255),@Modifiedon datetime ,@ETLSource nvarchar(30),@ETLInsertLogID int,@ETLInsertTimestamp datetime /* Check if we already have the key (procedure is idempotent) */ SELECT @Custkey= custkey,@CustomerID = CustomerID,@Customername = CustomerName,@street1= Street1,@street2 = Street2,@street3 = Street3,@city = City,@state = [State],@zip = Zip ,@country = Country,@modifiedon = MOdifiedon,@ETLSource = ETLSource,@ETLInsertLogID = ETLInsertLogID,@ETLInsertTimestamp = ETLInsertTimestamp FROM CustomerService.Dimcustomer WHERE Customerid = @Customerid /* The natural key was not found, generate a new one */ IF @Custkey IS NULL BEGIN INSERT CustomerService.DimCustomer(Customerid,CustomerName,Street1,Street2,Street3,City,[State],Zip,Country,ModifiedOn,ETLsource,ETLInsertLogID,ETLInsertTimestamp) VALUES (@CustomerID,@Customername,@Street1,@Street2,@Street3,@City,@State,@Zip,@Country,@Modifiedon,@ETLSource,@ETLInsertLogID,@ETLInsertTimestamp) SET @Custkey = SCOPE_IDENTITY() SET @Customername ='UNDEFINED' SET @Street1 ='UNDEFINED' SET @Street2 ='UNDEFINED' SET @Street3='UNDEFINED' SET @City ='UNDEFINED' SET @State='UNDEFINED' SET @Zip='UNDEFINED' SET @Country='UNDEFINED' SET @Modifiedon='1900-01-01' SET @ETLSource='UNDEFINED' SET @ETLInsertLogID=0 SET @ETLInsertTimestamp='1900-01-01' END /* Return the result. IMPORTANT, must return same format is the SELECT statement we replaced */ SELECT @Custkey AS Custkey, @CustomerID AS CustomerID,@Customername as CustomerName,@street1 as Street1,@street2 as Street2,@street3 as Street3,@city as City,@state as [State],@zip as Zip ,@country as Country,@modifiedon as MOdifiedon,@ETLSource as ETLSource,@ETLInsertLogID as ETLInsertLogID,@ETLInsertTimestamp as ETLInsertTimestamp GO and here is the dimension datatypes USE [EDW] GO /****** Object: Table [CustomerService].[DimCustomer] Script Date: 05/02/2011 18:56:16 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [CustomerService].[DimCustomer]( [CustKey] [int] IDENTITY(1,1) NOT NULL, [Customerid] [uniqueidentifier] NULL, [CustomerName] [nvarchar](160) NULL, [Street1] [nvarchar](250) NULL, [Street2] [nvarchar](250) NULL, [Street3] [nvarchar](250) NULL, [City] [nvarchar](80) NULL, [State] [nvarchar](50) NULL, [Zip] [nvarchar](20) NULL, [Country] [nvarchar](255) NULL, [Modifiedon] [datetime] NULL, [ETLSource] [nvarchar](30) NULL, [ETLInsertLogID] [int] NULL, [ETLInsertTimestamp] [datetime] NULL, CONSTRAINT [PK_dimcustomer_1] PRIMARY KEY CLUSTERED ( [CustKey] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO and here is the reference metadata from the lookup advanced editor <referenceMetadata><referenceColumns><referenceColumn name="CustKey" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="Customerid" dataType="DT_GUID" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="CustomerName" dataType="DT_WSTR" length="160" precision="0" scale="0" codePage="0"/><referenceColumn name="Street1" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="Street2" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="Street3" dataType="DT_WSTR" length="250" precision="0" scale="0" codePage="0"/><referenceColumn name="City" dataType="DT_WSTR" length="80" precision="0" scale="0" codePage="0"/><referenceColumn name="State" dataType="DT_WSTR" length="50" precision="0" scale="0" codePage="0"/><referenceColumn name="Zip" dataType="DT_WSTR" length="20" precision="0" scale="0" codePage="0"/><referenceColumn name="Country" dataType="DT_WSTR" length="255" precision="0" scale="0" codePage="0"/><referenceColumn name="Modifiedon" dataType="DT_DBTIMESTAMP" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="ETLSource" dataType="DT_WSTR" length="30" precision="0" scale="0" codePage="0"/><referenceColumn name="ETLInsertLogID" dataType="DT_I4" length="0" precision="0" scale="0" codePage="0"/><referenceColumn name="ETLInsertTimestamp" dataType="DT_DBTIMESTAMP" length="0" precision="0" scale="0" codePage="0"/></referenceColumns></referenceMetadata>arun

But both parameters are GUID. Where is the mismatch?arun

Craig I used your approach and used stored procedure in the OLEDB command and that metadata problem got solved . But I have 1 question using ur approach Generated key is not loaded in the first step . It will get loaded next time when the task runs . Is there a way to insert the surrogate key at the same time.arun

I am doing step 4 but from step 3 ( Key is inserted in dimension table ) but that is not returning for the lookup in the same flow. If I run the task again then it will generate the key .arun

sorry its 'Loading the key in the fact table' .My lookup is the replica of the original one except "handle the rows with no match entries " will fail the component in the original one its Rows with no match output directed to OLEDB command. And all the steps are running in a single transaction .But with Step 3 records are getting inserted in the dimension table .arun

This is my data flow Stage table -Lookup1--oledb command (Executing stored procedure and inserting the dummy records and generating key for them)---lookup2 --union all--fact table Now the problem is after Oledb command : Records get inserted in the dimesnion table . But they are not available for lookup2 in the same flow and make the lookup2 fails thats my problem. Help is appreciated. Thanksarun

Craig Here is my stored Procedure . Can you check if smthg wrong in it Do I have to return any OUTPUT parameter. USE [EDW] GO /****** Object: StoredProcedure [dbo].[Generate_Timekey] Script Date: 05/03/2011 14:26:52 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Generate_Timekey] @Calendardate datetime /* The key to find a surrogate for */ AS SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL SERIALIZABLE /* Prevent race conditions */ DECLARE @Timekey INT,@CalendarMonth tinyint,@CalendarQuarter tinyint,@CalendarYear int,@FiscalWeek tinyint,@FiscalMonth tinyint,@FiscalQuarter tinyint,@FiscalYear int,@FiscalWeekofYear tinyint,@Manufacturingyear int /* Check if we already have the key (procedure is idempotent) */ SELECT @Timekey = Timekey ,@CalendarDate = CalendarDate,@Calendarmonth = CalendarMonth,@CalendarQuarter=CalendarQuarter,@CalendarYear = calendaryear,@FiscalWeek = Fiscalweek,@FiscalMonth = FiscalMonth,@FiscalQuarter = FiscalQuarter,@FiscalYear = FiscalYear,@FiscalWeekofyear = FiscalWeekofyear,@ManufacturingYear = Manufacturingyear FROM dbo.DimTime WHERE CalendarDate=@CalendarDate SET @Calendarmonth=0 SET @CalendarQuarter=0 SET @CalendarYear=0 SET @Fiscalweek=0 SET @Fiscalmonth=0 SET @FiscalQuarter=0 SET @FiscalYear=0 SET @Fiscalweekofyear=0 SET @Manufacturingyear=0 /* The natural key was not found, generate a new one */ IF @TimeKey IS NULL BEGIN INSERT DimTime(CalendarDate,CalendarMonth,CalendarQuarter,CalendarYear,FiscalWeek,FiscalMonth,FiscalQuarter,FiscalYear,FiscalWeekofyear,Manufacturingyear) VALUES (@CalendarDate,@CalendarMonth,@CalendarQuarter,@CalendarYear,@Fiscalweek,@FiscalMonth,@FiscalQuarter,@FiscalYear,@FiscalWeekofyear,@Manufacturingyear) SET @Timekey = SCOPE_IDENTITY() END /* Return the result. IMPORTANT, must return same format is the SELECT statement we replaced */ SELECT @Timekey as Timekey, @CalendarDate as CalendarDate,@Calendarmonth as CalendarMonth,@calendarQuarter as calendarquarter,@CalendarYear as calendaryear,@FiscalWeek as Fiscalweek,@FiscalMonth as FiscalMonth,@FiscalQuarter as FiscalQuarter,@FiscalYear as FiscalYear,@FiscalWeekofyear as FiscalWeekofyear,@ManufacturingYear as Manufacturingyear GO arun

Everything is in SQL server 2008 and Max no of records are 400,000 for the fact tablearun

Craig I am using SET TRANSACTION ISOLATION LEVEL SERIALIZABLE to prevent Race conditions. And SET statements before BEGIN and END are using to give the fields default values. Can you send me your sample Stored Procedure . IF everything is working fine . Then I guess it's the stored procedure .You are right is running but its not committing before the Lookup is thsi possible to commit before the lookup in a single data flow task?arun

Sir There was something wrong with my stored procedure . I didn't change the Isolation Level but its working Now. Thanks CRAIG and SIrarun

I was wrong My problem is still there . Chandra I tried to change the transaction lsolation level to Read committed but no use . Any Suggestions arun

Craig Can you please sample stored procedure that you use to insert dummy records and will work with ur above mentioned approach . So that I can try Thanksarun

Craig can u please past sample stored procedure? that work with u approach.arun

This is my stored procedure .If any 1 can fund error that be great USE [EDW] GO /****** Object: StoredProcedure [dbo].[Generate_Timekey] Script Date: 05/04/2011 18:12:37 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[Generate_Timekey] @Calendardate datetime /* The key to find a surrogate for */ AS SET NOCOUNT ON --SET TRANSACTION ISOLATION LEVEL SERIALIZABLE/* Prevent race conditions */ DECLARE @Timekey INT,@CalendarMonth tinyint,@CalendarQuarter tinyint,@CalendarYear int,@FiscalWeek tinyint,@FiscalMonth tinyint,@FiscalQuarter tinyint,@FiscalYear int,@FiscalWeekofYear tinyint,@Manufacturingyear int /* Check if we already have the key (procedure is idempotent) */ SELECT @Timekey = Timekey FROM dbo.DimTime WHERE CalendarDate=@CalendarDate /* The natural key was not found, generate a new one */ IF @TimeKey IS NULL BEGIN SET @Timekey = SCOPE_IDENTITY() SET @Calendarmonth=0 SET @CalendarQuarter=0 SET @CalendarYear=0 SET @Fiscalweek=0 SET @Fiscalmonth=0 SET @FiscalQuarter=0 SET @FiscalYear=0 SET @Fiscalweekofyear=0 SET @Manufacturingyear=0 INSERT DimTime(CalendarDate,CalendarMonth,CalendarQuarter,CalendarYear,FiscalWeek,FiscalMonth,FiscalQuarter,FiscalYear,FiscalWeekofyear,Manufacturingyear) VALUES (@CalendarDate,@CalendarMonth,@CalendarQuarter,@CalendarYear,@Fiscalweek,@FiscalMonth,@FiscalQuarter,@FiscalYear,@FiscalWeekofyear,@Manufacturingyear) END arun

Can you tell me the command that you use ?arun

I am using this command INSERT DimTime(CalendarDate,CalendarMonth,CalendarQuarter,CalendarYear,FiscalWeek,FiscalMonth,FiscalQuarter,FiscalYear,FiscalWeekofyear,Manufacturingyear) VALUES (?,0,0,0,0,0,0,0,0,0) and mapped the parameter to the req field. Is there are any properties that I have to change ? because its still failing arun

Here is the link with the screen shots I am getting error in the second lookup/ https://picasaweb.google.com/erarun.malhotra/LOOKUPTrqnsformation?authkey=Gv1sRgCIHX1ruHtMOSOg# let me know if this worksarun

Thanks Craig Issue was with the Cache ! You really helped me a lot . Thanks a lotarun

Hey Craig 1 more thing I want to implement is that To be proactive we should have some type of exception reporting telling us that Undefined dimension key values are assigned. How Can I implement that ? arun

Craig I have another quest please go to the following link as u said I posted it new. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/afdda365-016a-4b29-9be9-6347a60e4806arun