Déployer et exécuter des packages SSIS à l'aide de procédures stockées

  • Article

Lorsque vous configurez un projet Integration Services afin d'utiliser le modèle de déploiement de projet, vous pouvez utiliser les procédures stockées du catalogue SSIS pour déployer le projet et pour exécuter des packages. Pour plus d'informations sur les modèles de déploiement de projet, consultez Déploiement de projets et de packages.

Vous pouvez également utiliser SQL Server Management Studio ou Outils de données SQL Server (SSDT) pour déployer le projet et pour exécuter des packages. Pour plus d'informations, consultez les rubriques de la section Voir aussi.

ConseilConseil

Vous pouvez facilement créer des instructions Transact-SQL pour les procédures stockées répertoriées dans la procédure ci-dessous, à l'exception de catalog.deploy_project, en procédant comme suit :

  1. Dans SQL Server Management Studio, développez le nœud Catalogues Integration Services dans l'Explorateur d'objets et accédez au package à exécuter.

  2. Cliquez avec le bouton droit sur le package, puis sélectionnez Exécuter.

  3. Si nécessaire, définissez les valeurs des paramètres, les propriétés du gestionnaire de connexions, ainsi que les options dans l'onglet Avancé, par exemple, le niveau de journalisation.

    Pour plus d'informations sur les niveaux de journalisation, consultez Activer la journalisation des exécutions de package sur le serveur SSIS.

  4. Avant de cliquer sur OK pour exécuter le package, cliquez sur Script. Transact-SQL s'affiche dans une fenêtre de l'Éditeur de requête dans SQL Server Management Studio.

Pour déployer et exécuter un package à l'aide de procédures stockées

  1. Appelez catalog.deploy_project (base de données SSISDB) pour déployer le projet Integration Services qui contient le package sur le serveur Integration Services.

    Pour récupérer les contenus binaires du fichier de déploiement de projet Integration Services, pour le paramètre @project\_stream, utilisez une instruction SELECT avec la fonction OPENROWSET et le fournisseur d'ensembles de lignes BULK. Le fournisseur d'ensembles de lignes BULK vous permet de lire des données dans un fichier. L'argument SINGLE_BLOB du fournisseur d'ensembles de lignes BULK retourne le contenu du fichier de données sous la forme d'un ensemble de lignes à une seule ligne, une seule colonne de type varbinary (max). Pour plus d'informations, consultez OPENROWSET (Transact-SQL).

    Dans l'exemple suivant, le projet SSISPackages_ProjectDeployment est déployé dans le dossier SSIS Packages sur le serveur Integration Services. Les données binaires sont lues à partir du fichier projet (SSISPackage_ProjectDeployment.ispac) et sont stockées dans le paramètre @ProjectBinary de type varbinary (max). La valeur du paramètre @ProjectBinary est attribuée au paramètre @project\_stream.

    DECLARE @ProjectBinary as varbinary(max)
DECLARE @operation_id as bigint
Set @ProjectBinary = (SELECT * FROM OPENROWSET(BULK 'C:\MyProjects\ SSISPackage_ProjectDeployment.ispac', SINGLE_BLOB) as BinaryData)

Exec catalog.deploy_project @folder_name = 'SSIS Packages', @project_name = 'DeployViaStoredProc_SSIS', @Project_Stream = @ProjectBinary, @operation_id = @operation_id out

  2. Appelez catalog.create_execution (base de données SSISDB) pour créer une instance d'exécution du package, et appelez éventuellement catalog.set_execution_parameter_value (base de données SSISDB) pour définir les valeurs de paramètre d'exécution.

    Dans l'exemple suivant, catalog.create_execution crée une instance d'exécution pour le fichier package.dtsx contenu dans le projet SSISPackage_ProjectDeployment. Le projet se trouve dans le dossier SSIS Packages. L'execution_id retourné par la procédure stockée est utilisé dans l'appel à catalog.set_execution_parameter_value. Cette deuxième procédure stockée attribue au paramètre LOGGING_LEVEL la valeur 3 (journalisation verbose) et attribue à un paramètre de package nommé Parameter1 la valeur 1.

    Pour les paramètres tels que LOGGING_LEVEL la valeur d'object_type est 50. Pour les paramètres de package la valeur d'object_type est 30.

    Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

GO

  3. Appelez catalog.start_execution (base de données SSISDB) pour exécuter le package.

    Dans l'exemple suivant, un appel à catalog.start_execution est ajouté à Transact-SQL pour démarrer l'exécution du package. L'execution_id retourné par la procédure stockée catalog.create_execution est utilisé.

    Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx', @execution_id=@execution_id OUTPUT, @folder_name=N'SSIS Packages', @project_name=N'SSISPackage_ProjectDeployment', @use32bitruntime=False, @reference_id=1

Select @execution_id
DECLARE @var0 smallint = 3
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=50, @parameter_name=N'LOGGING_LEVEL', @parameter_value=@var0

DECLARE @var1 int = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,  @object_type=30, @parameter_name=N'Parameter1', @parameter_value=@var1

EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO

Pour déployer un projet de serveur à serveur à l'aide de procédures stockées

Vous pouvez déployer un projet de serveur à serveur à l'aide des procédures stockées catalog.get_project (base de données SSISDB) et catalog.deploy_project (base de données SSISDB).

Vous devez effectuer les opérations suivantes avant d'exécuter les procédures stockées.

  • Créez un objet serveur lié. Pour plus d'informations, consultez Créer des serveurs liés (moteur de base de données SQL Server).

    Dans la page Options du serveur de la boîte de dialogue Propriétés du serveur lié, attribuez à RPC et à Sortie RPC la valeur True. Par ailleurs, attribuez à Activer la promotion des transactions distribuées pour RPC la valeur False.

  • Vérifiez les paramètres dynamiques du fournisseur sélectionné pour le serveur lié : développez le nœud Fournisseurs sous Serveurs liés dans l'Explorateur d'objets, puis cliquez avec le bouton droit sur le fournisseur, et cliquez sur Propriétés. Sélectionnez Activer en regard de Paramètre dynamique.

  • Vérifiez que le Coordinateur de transactions distribuées (DTC, Distributed Transaction Coordinator) est démarré sur les deux serveurs.

Appelez catalog.get_project pour retourner les données binaires du projet, puis appelez catalog.deploy_project. La valeur retournée par catalog.get_project est insérée dans une variable de table de type varbinary (max). Le serveur lié ne peut pas retourner des résultats qui sont varbinary (max).

Dans l'exemple suivant, catalog.get_project retourne une valeur binaire pour le projet SSISPackages sur le serveur lié. catalog.deploy_project déploie le projet sur le serveur local, dans un dossier nommé DestFolder.

declare @resultsTableVar table (
project_binary varbinary(max)
)
 
INSERT @resultsTableVar (project_binary)
EXECUTE [MyLinkedServer].[SSISDB].[catalog].[get_project] 'Packages', 'SSISPackages'

declare @project_binary varbinary(max)
select @project_binary = project_binary from @resultsTableVar

exec [SSISDB].[CATALOG].[deploy_project] 'DestFolder', 'SSISPackages', @project_binary

Voir aussi

Tâches

Déployer des projets sur le serveur Integration Services

Exécuter un package dans les outils de données SQL Server

Exécuter un package sur le serveur SSIS à l'aide de SQL Server Management Studio