Distribuire ed eseguire pacchetti SSIS utilizzando le stored procedure

  • Articolo

Quando un progetto di Integration Services viene configurato in modo da utilizzare il relativo modello di distribuzione, è possibile utilizzare le stored procedure nel catalogo di SSIS per distribuire il progetto ed eseguire i pacchetti. Per informazioni sul modello di distribuzione del progetto, vedere Distribuzione di progetti e pacchetti.

Per distribuire il progetto ed eseguire i pacchetti, è inoltre possibile utilizzare SQL Server Management Studio o SQL Server Data Tools (SSDT). Per ulteriori informazioni, vedere gli argomenti nella sezione Vedere anche.

SuggerimentoSuggerimento

Ad eccezione di catalog.deploy_project, è possibile generare facilmente le istruzioni Transact-SQL per le stored procedure elencate nella procedura descritta di seguito effettuando le operazioni seguenti:

  1. In SQL Server Management Studio espandere il nodo Cataloghi di Integration Services in Esplora oggetti e selezionare il pacchetto che si desidera eseguire.

  2. Fare clic con il pulsante destro del mouse sul pacchetto, quindi scegliere Esegui.

  3. In base alle esigenze, impostare i valori dei parametri, le proprietà di gestione connessione e le opzioni nella scheda Avanzate, ad esempio il livello di registrazione.

    Per ulteriori informazioni sui livelli di registrazione, vedere Abilitare la registrazione per l'esecuzione di pacchetti nel server SSIS.

  4. Prima di fare clic su OK per eseguire il pacchetto, scegliere Script. Transact-SQL viene visualizzato in una finestra dell'editor di query in SQL Server Management Studio.

Per distribuire ed eseguire un pacchetto utilizzando le stored procedure

  1. Chiamare catalog.deploy_project (database SSISDB) per distribuire il progetto di Integration Services in cui è contenuto il pacchetto per il server Integration Services.

    Per recuperare il contenuto binario del file di distribuzione del progetto di Integration Services, per il parametro @project\_stream utilizzare un'istruzione SELECT con la funzione OPENROWSET e il provider BULK per set di righe. Questo provider consente di leggere i dati da un file. Tramite l'argomento SINGLE_BLOB per il provider BULK per set di righe, il contenuto del file di dati viene restituito come un set di righe a riga e colonna singole di tipo varbinary(max). Per ulteriori informazioni, vedere OPENROWSET (Transact-SQL).

    Nell'esempio seguente, il progetto SSISPackages_ProjectDeployment viene distribuito nella cartella di pacchetti SSIS nel server Integration Services. I dati binari vengono letti dal file di progetto (SSISPackage_ProjectDeployment.ispac) e archiviati nel parametro @ProjectBinary di tipo varbinary(max). Il valore del parametro @ProjectBinary viene assegnato al parametro @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. Chiamare catalog.create_execution (database SSISDB) per creare un'istanza dell'esecuzione del pacchetto e, facoltativamente, chiamare catalog.set_execution_parameter_value (database SSISDB) per impostare i valori dei parametri di runtime.

    Nell'esempio seguente, tramite catalog.create_execution viene creata un'istanza di esecuzione per package.dtsx che è contenuto nel progetto SSISPackage_ProjectDeployment. Il progetto si trova nella cartella di pacchetti SSIS. Il valore di execution_id restituito dalla stored procedure viene utilizzato per la chiamata a catalog.set_execution_parameter_value. Tramite questa seconda stored procedure il parametro LOGGING_LEVEL viene impostato su 3 (registrazione dettagliata) e un parametro del pacchetto denominato Parameter1 viene impostato su un valore 1.

    Per i parametri come LOGGING_LEVEL, il valore di object_type è 50. Per i parametri del pacchetto, il valore di object_type è 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. Chiamare catalog.start_execution (database SSISDB) per eseguire il pacchetto.

    Nell'esempio seguente, una chiamata a catalog.start_execution viene aggiunta a Transact-SQL per avviare l'esecuzione del pacchetto. Viene utilizzato il valore di execution_id restituito dalla stored procedure catalog.create_execution.

    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

Per distribuire un progetto da un server in un altro mediante stored procedure

Un progetto può essere distribuito da un server in un altro utilizzando le stored procedure catalog.get_project (database SSISDB) e catalog.deploy_project (database SSISDB).

Prima di eseguire le stored procedure, è necessario effettuare le operazioni riportate di seguito.

  • Creare un oggetto server collegato. Per ulteriori informazioni, vedere Creazione di server collegati (Motore di database di SQL Server).

    Nella pagina Opzioni server della finestra di dialogo Proprietà server collegato impostare RPC e RPC Out su True. Inoltre, impostare Abilita innalzamento di livello delle transazioni distribuite per RPC su False.

  • Abilitare i parametri dinamici per il provider selezionato per il server collegato espandendo il nodo Provider in Server collegati in Esplora oggetti, facendo clic con il pulsante destro del mouse sul provider e selezionando Proprietà. Selezionare Abilita accanto a Parametro dinamico.

  • Verificare che Distributed Transaction Coordinator (DTC) venga avviato in entrambi i server.

Chiamare catalog.get_project per restituire i dati binari per il progetto, quindi chiamare catalog.deploy_project. Il valore restituito da catalog.get_project viene inserito in una variabile di tabella di tipo varbinary(max). Tramite il server collegato non possono essere restituiti risultati di tipo varbinary(max).

Nell'esempio seguente, tramite catalog.get_project viene restituito un file binario per il progetto SSISPackages nel server collegato. Tramite catalog.deploy_project il progetto viene distribuito nella cartella denominata DestFolder nel server locale.

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

Vedere anche

Attivitá

Distribuire progetti nel server Integration Services

Eseguire un pacchetto in SQL Server Data Tools

Eseguire un pacchetto sul server SSIS mediante SQL Server Management Studio