How to execute SSIS ETL package using Python

Question

I have a SSIS package which i am currently running using Visual Studio. I want to know is there any mechanism in python by which i can execute those SSIS packages.

Answer 1

You can use DTExec to run the SSIS package. You basically launch an external process to run the package. You can execute packages that are stored on the SQL Server as well as packages that are on he filesystem. It's a pretty flexible tool with command line arguments to support many scenarios.

Answer 2

You can execute a Transact-SQL script that call for the execution of the dtsx package.

This has an advantage over other answers, because you do not need to have acces to the DTExec (that requires access to the machine where the SSIS Service is running) and works for other programming languages (most if not all of them allow executing TSQL).

The only requirements would be:

• Be able to connect to the SQL Server Database service within python (pyodbc or similar)
• Configure the Integration Services Catalog in the SQL Server Database service

Microsoft quickstart: SSIS with TSQL

Stored procedure documentation: catalog.create_execution

Code example, extracted from the quickstart:

Declare @execution_id bigint
EXEC [SSISDB].[catalog].[create_execution] @package_name=N'Package.dtsx',
    @execution_id=@execution_id OUTPUT,
    @folder_name=N'Deployed Projects',
      @project_name=N'Integration Services Project1',
    @use32bitruntime=False,
      @reference_id=Null
Select @execution_id
DECLARE @var0 smallint = 1
EXEC [SSISDB].[catalog].[set_execution_parameter_value] @execution_id,
    @object_type=50,
      @parameter_name=N'LOGGING_LEVEL',
      @parameter_value=@var0
EXEC [SSISDB].[catalog].[start_execution] @execution_id
GO