Tuesday, November 22, 2011

Error Handling in SP

BEGIN CATCH
   DECLARE @ErrorSeverity INT
   DECLARE @ErrorState INT
   SELECT
    @ErrorMessage = ERROR_MESSAGE(),
    @ErrorSeverity = ERROR_SEVERITY(),
    @ErrorState = ERROR_STATE(),
    @ErrorCode = ERROR_NUMBER()
 
   RAISERROR (@ErrorCode,@ErrorSeverity,@ErrorState,@ErrorMessage)--(50327,14,1)
   RETURN 1
END CATCH

Partition Logic in ETL Design


Partition Logic

/****** Object:  PartitionFunction [partfunc_TinyInt_MOD10]    Script Date: 11/22/2011 15:33:47 ******/

CREATE PARTITION FUNCTION [partfunc_TinyInt_MOD10](tinyint) AS

RANGE RIGHT FOR VALUES (0x01, 0x02, 0x03, 0x04, 0x05, 0x06, 0x07, 0x08, 0x09)

GO

/****** Object:  PartitionScheme [partscheme_DATA_TinyInt_MOD10]    Script Date: 11/22/2011 15:34:30 ******/

CREATE PARTITION SCHEME [partscheme_DATA_TinyInt_MOD10]

AS PARTITION [partfunc_TinyInt_MOD10] TO ([DATA], [DATA], [DATA],

[DATA], [DATA], [DATA], [DATA], [DATA], [DATA], [DATA])

GO

1.       SQL Task for Tracking data load partition wise

EXECUTE [dbo].[sp_syslargevolumelog_get_isloadedsuccessful_value]

   @PackageName = 'GMODW_Datasources_GMED_IndividualNonPII.dtsx'

  ,@TempSchemaName = 'tmp'

  ,@TempTableName = 'GMED_IndividualNonPII_P01'

  ,@IsLoadedSuccessful = ? OUTPUT



2.       If loading not at completed it will go down

@IsLoadSuccessful==FALSE



3.       SQL Task for Create partition table

/****** Drop   Table [tmp].[MED_IndividualNon_P01]    ******/



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[MED_IndividualNon_P01]') AND type in (N'U'))

BEGIN

DROP TABLE [tmp].[MED_IndividualNon_P01]

END

GO



CREATE TABLE [tmp].[MED_IndividualNon_P01](

                [MedIndID] [bigint] NOT NULL,

                [MedOrgID] [bigint] NULL,

                [PIIFileID] [int] NOT NULL,

                [MOD10] [tinyint] NOT NULL,

                [DWRowInsertedDate] [smalldatetime] NOT NULL DEFAULT (getdate())

) ON [partscheme_DATA_TinyInt_MOD10]([MOD10])

WITH

(

DATA_COMPRESSION = PAGE

)

GO



4.       Data flow task for Data Load



5. SQL Task for tracking data load partition wise

EXECUTE [dbo].[sp_syslargevolumelog_set_isloadedsuccessful_value]

   @PackageName = 'MODW_Datasources_MED_IndividualNon.dtsx'

  ,@TempSchemaName = 'tmp'

  ,@TempTableName = 'MED_IndividualNon_P01'

  ,@IsLoadedSuccessful = 1



6. SQL Task for Create temporary table same as original table

/****** Drop   Table [tmp].[MED_IndividualNon]    ******/



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[tmp].[MED_IndividualNonPII]') AND type in (N'U'))

BEGIN

DROP TABLE [tmp].[MED_IndividualNon]

END

GO





CREATE TABLE [tmp].[MED_IndividualNon](

                [MedIndID] [bigint] NOT NULL,

                [MedOrgID] [bigint] NULL,

                [PIIFileID] [int] NOT NULL,

                [MOD10] [tinyint] NOT NULL,

                [DWRowInsertedDate] [smalldatetime] NOT NULL DEFAULT (getdate())

) ON

 [partscheme_DATA_TinyInt_MOD10]([MOD10])

WITH

(

DATA_COMPRESSION = PAGE

)

;



7. Switch individual partition to temporary table

ALTER TABLE tmp.MED_IndividualNon_P01

SWITCH PARTITION 1 TO tmp.MED_IndividualNon PARTITION 1



8. Create unique clustered indexes

CREATE UNIQUE CLUSTERED INDEX [IXCU_MedIndID_MOD10] ON [tmp].[MED_IndividualNon]

(

 [MedIndID] ASC,

 [MOD10] ASC

)WITH (DATA_COMPRESSION = PAGE) ON [partscheme_DATA_TinyInt_MOD10]([MOD10]);



9. Rename the temporary table as original table

-- Here is the most important part, we will drop the gmed.IndividualNon table, and replace it with the tmp.MED_IndividualNonPII table.



IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[gmed].[IndividualNon]') AND type in (N'U'))

BEGIN

                DROP TABLE [gmed].[IndividualNon]

END



ALTER SCHEMA [gmed] TRANSFER [tmp].[MED_IndividualNon]

EXEC SP_RENAME 'med.MED_IndividualNon','IndividualNon'