As others have suggested, using airflow to orchestrate stored procs
directly (or to build sql statements and execute them) is a nice pattern
that you could use to ultimately get rid of SSIS.
However if you have legacy jobs that need to stay running as is, and you
just want to orchestrate them from airflow, one approach you can use is to
trigger sqlagent jobs from airflow. In case it is helpful you can kick off
the sqlagent job using odbc like so:
def execute(self, context):
cmd = f"execute msdb.dbo.sp_start_job '{self.job_name}'"
hook = OdbcHook()
with closing(hook.get_conn()) as conn:
cur = conn.cursor()
cur.execute(cmd)
