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'
