Synchroniser vos données a travers le composant SSIS Jointure de fusion

Bonjour

Auparavant, j’ai écrit un article sur la conception et la mise en œuvre d’un UPSERT LOOKUP qui concerne la mise à jour des enregistrements existants et l’insertion de nouveaux enregistrements. Aujourd’hui, je souhaite également étendre cela en ajoutant l’opération de suppression  Ainsi, la méthode utilisée dans cet article peut être utilisée pour trouver des enregistrements INSERTED / UPDATED / DELETED à partir de la table source et appliquer ces modifications à la table de destination.

Dans cet exemple, j’ai utilisé la transformation de jointure de fusion, le fractionnement conditionnel et la transformation de commande OLE DB pour implémenter la solution

Tout d’abord, nous appliquons une jointure externe complète sur la table source et de destination dans la ou les colonnes clés avec la transformation Fusion de jointure. Nous utilisons ensuite une division conditionnelle pour déterminer le type de modification (enregistrements supprimés, nouveaux ou existants). Les enregistrements existants nécessiteront un autre traitement pour savoir s’il y a eu des changements ou non? Nous utilisons un autre fois le fractionnement conditionnel pour comparer la valeur des colonnes équivalentes dans la source et la destination

Je montrerai donc comment synchroniser ces données entre serverA et serverB en utilisant une jointure de fusion dans SSIS

Voici à quoi ressemble mon package SSIS. Je vais discuter de chacune des étapes

J’ai deux sources OLE DB, une pour serverA (source) et l’autre pour serverB (destination). Voici comment ces connexions sont configurées

Lors de l’application d’une jointure par fusion, les données doivent être triées pour les deux entrées. J’applique donc une opération de tri des deux côtés

Maintenant Faites glisser le composant Jointure de fusion connecter Sur les deux sources OLE DB  Définissez la table source comme gauche et la table de destination comme entrée droite de cette transformation.

Allez dans l’éditeur du composant Jointure de fusiones , le colonne ID sera utilisé comme colonne de jointure (sélectionnée en fonction des propriétés de tri des composants précédents). Notez que si vous ne triez pas les colonnes d’entrée de la transformation de jointure de fusion, vous ne pouvez pas entrer dans l’éditeur de cette transformation et vous rencontrez l’erreur concernant le tri des entrées.

Sélectionnez toutes les colonnes des tables Source et Destination dans la transformation de jointure de fusion et renommez-les comme le montre l’image ci-dessous

À l’étape suivante, j’ai appliqué le composant Fractionnement conditionnel J’ai ajouté trois conditions différentes en fonction du résultat de la jointure externe complète. Ce sont les trois conditions:

1. UPDATE (correspondance avec ID): Si le ID existe à la fois sur la source et la destination, nous effectuons une mise à jour.
2. INSERT (id introuvable dans la destination): Si ID se trouve uniquement à gauche (source), nous devons insérer ces données dans la table de destination.
3. DELETE (ID est introuvable dans la source): Si ID n’est pas trouvé dans la table source, nous devons supprimer ces données de la table de destination                           

1 Insert !ISNULL(id) && ISNULL(id_NEW)
2 Delete ISNULL(id) && !ISNULL(id_NEW)
3 Update !ISNULL(id) && !ISNULL(id_NEW)

Nous avons donc maintenant trois flux de données pour effectuer une insertion, une mise à jour ou une suppression.

• Opération insert

Ajouter une destination OLE DB et connecter la sortie NEW RECORDS. Définissez la configuration de la table de destination et utilisez des colonnes avec le préfixe source dans le mappage de colonnes de la destination OLE DB. Ce composant de destination insérera de nouveaux enregistrements dans la table de destination

• Opération delete

Pour supprimer des données, j’ai une connexion de destination OLE DB et j’ai ajouté une commande sql comme suit à l’aide de l’éditeur avancé

delete from Produit_new
where id_NEW=?

• Opération Update

Ajouter de nouveau le composant  Fractionnement conditionnel   Nous utilisons ce composant pour rechercher uniquement les enregistrements qui ont changé dans l’une des valeurs. Nous comparons donc les colonnes source et destination équivalentes pour trouver des données non correspondantes.C’est l’expression utilisée pour trouver les données de correspondance dans la capture d’écran ci-dessous

Après avoir lancé la commande avec le paramètre «?», J’ai ensuite appliqué le mappage de colonne d’entrée avec les colonnes de destination de paramètres disponibles en fonction de la table de destination

Exécution du package

Après avoir configuré toutes les étapes ci-dessus, en changeant au hasard certaines des données, je lance alors le package et obtient les éléments suivants

Conclusion

la synchronisation a été bien passer entre la table source et la destination  en effectuant  des opérations d’insertion, de mise à jour et de suppression avec des données de la source vers la destination

Modifier le mode de déploiement SSAS du mode multidimensionnel au mode tabulaire sans réinstaller Analysis Services

Bonjour

Analysis Services peut être installé dans l’un des trois modes serveur suivants: Exploration multidimensionnelle et d’exploration de données (par défaut), Power Pivot pour SharePoint et Tabulaire. Le mode serveur d’une instance Analysis Services est déterminé lors de l’installation lorsque vous choisissez les options d’installation du serveur.
Comment vérifier le mode ?

Connectez-vous à l’instance dans SQL Server Management Studio et cliquez avec le bouton droit sur le nom de l’instance. Ensuite, sélectionnez Propriétés

Dans la capture d’écran ci-dessous, vous pouvez voir que le mode serveur est en mode multidimensionnel.

Pour modifier le mode de déploiement

• Sauvegarde des bases de données Analysis Services multidimensionnelles sur l’instance (le cas échéant)
• Détachez les bases de données Analysis Services multidimensionnelles de l’instance (le cas échéant). Ces bases de données ne seront pas utilisables en mode tabulaire
• Naviguez jusqu’au chemin « : \ Program Files \ Microsoft SQL Server \ MSAS11.MSSQLSERVER \ OLAP \ Config » et sauvegardez le fichier « msmdsrv.ini »
• Ouvrez le fichier « msmdsrv.ini » et remplacez la valeur de DeploymentMode par 2.
  0 – Multidimensional
  1 – SharePoint
  2 – Tabular

le fichier msmdsrv.ini est sur ce emplacement C:\Program Files\Microsoft SQL Server\MSAS12.SQLPROD\OLAP\Config

Ouvrez le fichier de configuration dans le Bloc-notes. Modifiez la propriété du mode de déploiement de 0 (multidimensionnelle) à 2 (tabulaire). Dans la capture d’écran ci-dessous, le mode de déploiement est défini sur 0 (mode multidimensionnel)

• Redémarrez les services SQL Server Analysis

Mode multidimensionnel SSAS – Avant modification

Mode tabulaire SSAS – Après modification

TRUNCATE TABLE WITH PARTITIONS (SQL Server 2016)

Bonjour

TRUNCATE TABLE WITH PARTITIONS est une nouvelle Amélioration de T-SQL dans SQL Server 2016
Il est bien connu que TRUNCATE TABLE est plus rapide que l’instruction DELETE. TRUNCATE TABLE utilise des ressources système et un fichier journal transactionnel inférieurs à ceux d’une instruction DELETE.

À partir de SQL Server 2016, Microsoft a introduit une nouvelle clause TRUNCATE TABLE WITH PARTITIONS qui peut être utilisée pour tronquer toutes les lignes disponibles dans une partition

Passons en revue une simple démonstration pour nous montrer comment cela fonctionne. Nous allons commencer par ajouter un  nouveaux groupes de fichiers nommé FG1 et UN  fichiers de données à notre base de données AdventureworksDW2016CTP3 existante avec le script ci-dessous:

use [AdventureworksDW2016CTP3]

GO
use [master]

GO
USE [master]
GO
ALTER DATABASE [AdventureworksDW2016CTP3] ADD FILEGROUP [FG1]
GO

Une fois que le groupe de fichiers et les fichier ndf de base de données sont prêts, nous allons créer la fonction de partitionnement ainsi que le schéma de partitionnement

use [AdventureworksDW2016CTP3]

GO
use [model]

GO
USE [master]
GO
ALTER DATABASE [AdventureworksDW2016CTP3] ADD FILE ( NAME = N'AdventureWorksDW2014_Data2', FILENAME = N'C:\Data\AdventureWorksDW2016CTP3_Log.ndf' , SIZE = 8192KB , FILEGROWTH = 65536KB ) TO FILEGROUP [FG1]
GO

USE [AdventureworksDW2016CTP3]
GO
BEGIN TRANSACTION
CREATE PARTITION FUNCTION [Parttion1](datetime) AS RANGE LEFT FOR VALUES (N'2005-01-01T00:00:00', N'2005-05-01T00:00:00', N'2005-11-01T00:00:00', N'2007-01-01T00:00:00', N'2010-05-05T00:00:00', N'2015-05-05T00:00:00')

CREATE PARTITION SCHEME [Partition1] AS PARTITION [Parttion1] TO ([FG1], [FG1], [FG1], [FG1], [FG1], [FG1], [FG1])

ALTER TABLE [dbo].[FactResellerSalesXL_PageCompressed] DROP CONSTRAINT [PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber] WITH ( ONLINE = OFF )

SET ANSI_PADDING ON

ALTER TABLE [dbo].[FactResellerSalesXL_PageCompressed] ADD  CONSTRAINT [PK_FactResellerSalesXL_PageCompressed_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY NONCLUSTERED
(
	[SalesOrderNumber] ASC,
	[SalesOrderLineNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = PAGE) ON [FG1]

CREATE CLUSTERED INDEX [ClusteredIndex_on_Partition1_636714223260918622] ON [dbo].[FactResellerSalesXL_PageCompressed]
(
	[ShipDate]
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, DATA_COMPRESSION = PAGE ON PARTITIONS (1 TO 7)) ON [Partition1]([ShipDate])

DROP INDEX [ClusteredIndex_on_Partition1_636714223260918622] ON [dbo].[FactResellerSalesXL_PageCompressed]

COMMIT TRANSACTION

Nous allons maintenant l’exécution de l’instruction truncate TABLE. L’instruction TRUNCATE ci-dessous est utilisée pour tronquer le deuxième et troisième partitions et la quatrième partition de la table FactResellerSalesXL_PageCompressed ce qui supprimera les données de ces partition

TRUNCATE TABLE FactResellerSalesXL_PageCompressed
WITH (PARTITIONS (2 TO 4));
GO

Maintenant interrogeons la table sys.partitions pour vérifier le nombre d’enregistrements dans chaque partition avant et après l’application de l’instruction TRUNCATE TABLE: