DELETE FROM [EnterpriseDW].[dbo].[DimZipCounty] WHERE ZipCountySK IN ( SELECT ZipCountySK FROM [dbo].[Obsolete_DimZipCounty] obs WHERE obs.DoNotDeleteFlag = 0 )

" DELETE FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimZipCounty] WHERE ZipCountySK IN ( SELECT ZipCountySK FROM [dbo].[Obsolete_DimZipCounty] obs WHERE obs.DoNotDeleteFlag = 0 ) "

EnterpriseDW

Configuration setting in SSIS_Configurations that specifies the name of the Enterprise Data Warehouse.

N/A

If the Enterprise Data Warehouse resides on a different server from the DWStaging and DWoperations databases, a linked server must be defined and the name populated into the Var_EDWLinkedServername. If it resides on the same server, this setting must be set to ?N/A?. This is a configuration setting from SSIS_Configurations.

/* SQL INSERT into DimZipCounty from New Temp Table */ INSERT INTO [EnterpriseDW].[dbo].[DimZipCounty] (ZipCodeAK ,ZipCode ,CountyFIPS ,County ,City ,State ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT ZipCodeAK ,ZipCode ,CountyFIPS ,County ,City ,State ,RowIsCurrent ,RowStartDate ,RowEndDate FROM dbo.Staged_dbo_DimZipCounty__New

" /* SQL INSERT into DimZipCounty from New Temp Table */ INSERT INTO " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimZipCounty] (ZipCodeAK ,ZipCode ,CountyFIPS ,County ,City ,State ,RowIsCurrent ,RowStartDate ,RowEndDate) SELECT ZipCodeAK ,ZipCode ,CountyFIPS ,County ,City ,State ,RowIsCurrent ,RowStartDate ,RowEndDate FROM dbo.Staged_dbo_DimZipCounty__New "

This variable contains the string that will be used to insert any new rows into the DimZipCounty table.

/* Sample Update Type1 to Dim */ UPDATE [EnterpriseDW].[dbo].[DimZipCounty] SET ZipCodeAK = SCD1.ZipCodeAK ,ZipCode = SCD1.ZipCode ,CountyFIPS = SCD1.CountyFIPS ,County = SCD1.County ,City = SCD1.City ,State = SCD1.State FROM [EnterpriseDW].[dbo].[DimZipCounty] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimZipCounty__Type1Update] AS SCD1 ON Dim.ZipCodeAK = SCD1.ZipCodeAK

" /* Sample Update Type1 to Dim */ UPDATE " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimZipCounty] SET ZipCodeAK = SCD1.ZipCodeAK ,ZipCode = SCD1.ZipCode ,CountyFIPS = SCD1.CountyFIPS ,County = SCD1.County ,City = SCD1.City ,State = SCD1.State FROM " + (@[User::EDWLinkedServerName] == "N/A" ? "" : "[" + @[User::EDWLinkedServerName]+"]." ) + "["+ @[User::EDWDBName] + "].[dbo].[DimZipCounty] AS [DIM] INNER JOIN [dbo].[Staged_dbo_DimZipCounty__Type1Update] AS SCD1 ON Dim.ZipCodeAK = SCD1.ZipCodeAK "

The SQL string that will update any changed rows in the DimZipCounty dimension.