Blog > Janvier 2013
Requête dynamique dans un package SSIS


Dans SSIS, il est possible de créer des requêtes dynamiques exécutées pour chaque élément d'un flux.
Par exemple, on veut initialiser la valeur d'une colonne en fonction de l'identifiant de la table :
Mettre "Id=" [Colonne_Id] dans une colonne [Colonne_Description]

Pour cela :
  • Utiliser le composant "Colonne dérivée" pour créer une nouvelle donnée avec l'expression : "Id=" + (DT_WSTR,50) [Colonne_Id]
  • Utiliser le composant "Commande OLE DB" et écrire la requête suivante :
    UPDATE MaTable SET Colonne_Description = ? WHERE Colonne_Id = ?
  • Utiliser l'onglet "Mappage de colonnes" du composant pour mapper avec les bonnes données du flux. SSIS génère automatique des paramètres param_0 et param_1 qui correspondent aux ? rencontrés, dans l'ordre d'apparition.

Créer un catalogue de projet SSIS


Une fois qu'un package SSIS a été créé dans SQL Server Data Tools, il est possible de le déployer sur un service Integration Services. Néanmoins un catalogue SSISDB doit au préalable être créé sur cette instance et l'utilitaire d'installation d'SQL Server 2012 ne le fait pas. Vous pouvez créer le catalogue SSISDB à travers SQL Server Management Studio à l'aide des instructions suivantes :
  • Ouvrez SQL Server Management Studio.
  • Connectez-vous au moteur de base de données SQL Server.
  • Dans l'Explorateur d'objets, développez le nœud du serveur, cliquez avec le bouton droit sur le nœud Catalogues Integration Services, puis sélectionnez "Créer le catalogue".
  • Cliquez sur Activer l'intégration du CLR (le catalogue utilise des procédures stockées du CLR).
  • Cliquez sur Activer l'exécution automatique des procédures stockées Integration Services au démarrage de SQL Server (pour que la procédure stockée catalog.startup s'exécute chaque fois que l'instance de serveur SSIS est redémarre). La procédure stockée effectue la maintenance de l'état des opérations pour le catalogue SSISDB. Elle résout l'état de tous les packages en cours d'exécution si et quand l'instance de serveur SSIS s'arrête.
  • Entrez un mot de passe, puis cliquez sur OK. Le mot de passe protège la clé principale de la base de données utilisée pour le chiffrement des données du catalogue. Enregistrez le mot de passe dans un emplacement sécurisé. Il est également recommandé de sauvegarder la clé principale de base de données.
Vous pouvez également créer le catalogue par programme, en utilisant Windows PowerShell et l'espace de noms Microsoft.SqlServer.Management.IntegrationServices. Dans l'exemple suivant, les propriétés MaxProjectVersions et OperationLogRentionTime sont modifiées.
# Load the IntegrationServices Assembly
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Management.IntegrationServices")

# Store the IntegrationServices Assembly namespace to avoid typing it every time
$ISNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

Write-Host "Connecting to server ..."

# Create a connection to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

# Create the Integration Services object
$integrationServices = New-Object $ISNamespace".IntegrationServices" $sqlConnection

# Get the existing SSIS Catalog
$catalog = $integrationServices.Catalogs["SSISDB"]

# Changes the catalog property - Maximum number of versions maintained for a project
$catalog.MaxProjectVersions = 5
$catalog.Alter()

# Changes the retention window
$catalog.OperationLogRetentionTime = 100
$catalog.Alter()

Créer une valeur aléatoire dans SSIS


Dans certains cas, il peut être utile de générer une donnée aléatoire dans SSIS. Dans mon cas, j'avais besoin de générer une valeur aléatoire dans une plage.
Dans un premier temps, il faut ajouter le composant Script et l'attacher à la source de données :

Ensuite ouvrir le composant et ajouter une nouvelle colonne de sortie par l'onglet "Entrées et sorties".
Pour finir, il faut modifier le script en cliquant sur le bouton suivant dans l'onglet principal :

A ce niveau, on peut générer le code C# que l'on souhaite en modifiant le fonction Input0_ProcessInputRow.
Par exemple, pour générer un nombre aléatoire dans une colonne de sortie nommé "randomNumber" :
Random rnd = new Random();

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    Row.randomNumber = rnd.Next(1, 51);
}

Bonne année 2013


1 2