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'