You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@airflow.apache.org by Neeku Endhuku Nenu cheppanu <id...@gmail.com> on 2021/06/24 10:43:18 UTC
Re: SSIS
Hi team,
Is it possible to schedule a SSIS package's in Airflow??
Please let me know, if it possible...
Many thanks,
Krishna v.
Re: SSIS
Posted by Neeku Endhuku Nenu cheppanu <id...@gmail.com>.
Thank you very much for your help Daniel...
On Tue, 29 Jun, 2021, 9:18 pm Daniel Standish, <dp...@gmail.com> wrote:
> Here:
>
> from contextlib import closing
>
> from airflow.models.baseoperator import BaseOperator
> from airflow.providers.odbc.hooks.odbc import OdbcHook
>
>
> class SqlAgentOperator(BaseOperator):
> def __init__(self, job_name: str, **kwargs):
> super().__init__(**kwargs)
> self.job_name = job_name
>
> 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)
>
>
> There is a guide called Creating a Custom Operator
> <https://airflow.apache.org/docs/apache-airflow/stable/howto/custom-operator.html>
> which explains how operators work.
>
> Note, I don't think this waits synchronously for the sqlagent job to
> complete. For that you'd have to add some polling logic.
>
>
Re: SSIS
Posted by Daniel Standish <dp...@gmail.com>.
Here:
from contextlib import closing
from airflow.models.baseoperator import BaseOperator
from airflow.providers.odbc.hooks.odbc import OdbcHook
class SqlAgentOperator(BaseOperator):
def __init__(self, job_name: str, **kwargs):
super().__init__(**kwargs)
self.job_name = job_name
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)
There is a guide called Creating a Custom Operator
<https://airflow.apache.org/docs/apache-airflow/stable/howto/custom-operator.html>
which explains how operators work.
Note, I don't think this waits synchronously for the sqlagent job to
complete. For that you'd have to add some polling logic.
Re: SSIS
Posted by Neeku Endhuku Nenu cheppanu <id...@gmail.com>.
Hi daniel,
Thank you for your support....
I'm new to python, I'm a .net developer basically... So, if it is possible
can you please send me the full template for my reference.. if possible
only...
Many thanks in advance...
Thanks & Regards,
Krishna V.
On Tue, 29 Jun, 2021, 8:32 pm Daniel Standish, <dp...@gmail.com> wrote:
> 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)
>
>
>
>
>
Re: SSIS
Posted by Daniel Standish <dp...@gmail.com>.
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)
Re: SSIS
Posted by Constance Martineau <co...@astronomer.io.INVALID>.
Hi Neeku,
I used to work in an environment that heavily relied on MSSQL Server and
SSIS. Among other things, we used Airflow to orchestrate the SSIS jobs when
moving to Airflow. While there is no specific "SSIS" package, assuming you
are using odbc drivers, there is an odbc provider (
https://airflow.apache.org/docs/apache-airflow-providers-odbc/stable/_api/airflow/providers/odbc/hooks/odbc/index.html).
You can use this or the PythonOperator in conjunction with pyodbc to run
the same stored procs or sql statements that your SSIS jobs are running.
Some refactoring will be required, but it is worth it. Your mileage may
vary, but after a data scientist moved all his workloads to Airflow, he
said that the time he spent debugging his pipelines went down 50%.
Hope this helps,
Constance
On Tue, Jun 29, 2021 at 5:04 AM Neeku Endhuku Nenu cheppanu <
idk.050694@gmail.com> wrote:
> Thank you for your response...
>
> On Tue, 29 Jun, 2021, 2:17 pm Jarek Potiuk, <ja...@potiuk.com> wrote:
>
>> I think there are no ready operators for SSIS. You can build your own
>> (and contribute them back maybe) - it's actually not that difficult if you
>> have a Python API, or some command line interface (which you can invoke via
>> Bash Operator for example).
>>
>> I might be biased, and I do not know much about SSIS but from a quick
>> search I think people treat SSIS as an expensive competitor to Airflow and
>> mostly abandon it in favour of Airflow and Python:
>>
>> -
>> https://medium.com/kabbage-engineering/running-airflow-at-kabbage-d39ebc101778
>> - https://www.stitchdata.com/vs/ssis/airflow/
>> -
>> https://towardsdatascience.com/3-reasons-why-im-ditching-ssis-for-python-ee129fa127b5
>>
>> So maybe instead of integrating it with Airflow, consider replacing the
>> use of SSIS with Airflow.
>>
>> Of course it's a narrow, biased, 2 minutes search in Google so don't take
>> it for granted, do your own research and decide.
>>
>> J.
>>
>>
>>
>>
>> On Tue, Jun 29, 2021 at 10:36 AM Neeku Endhuku Nenu cheppanu <
>> idk.050694@gmail.com> wrote:
>>
>>> Thank you for your response.
>>>
>>> SSIS - SQL server integration services..
>>>
>>> We are trying to schedule these SSIS jobs in Airflow...
>>>
>>> We don't find any article about this...
>>>
>>> Is it possible to schedule these SSIS jobs in Airflow??
>>>
>>>
>>> Please help me out..
>>>
>>>
>>> Many Thanks,
>>> Krishna v.
>>>
>>> On Tue, 29 Jun, 2021, 1:46 pm Ash Berlin-Taylor, <as...@apache.org> wrote:
>>>
>>>> What is SSIS?
>>>>
>>>> What have you tried already?
>>>>
>>>> What error are your getting?
>>>>
>>>> -ash
>>>>
>>>> On 29 June 2021 09:02:43 BST, Neeku Endhuku Nenu cheppanu <
>>>> idk.050694@gmail.com> wrote:
>>>>>
>>>>> Please help me out on this..
>>>>>
>>>>> On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
>>>>> idk.050694@gmail.com> wrote:
>>>>>
>>>>>> Hi team,
>>>>>>
>>>>>> Is it possible to schedule a SSIS package's in Airflow??
>>>>>>
>>>>>> Please let me know, if it possible...
>>>>>>
>>>>>>
>>>>>> Many thanks,
>>>>>> Krishna v.
>>>>>>
>>>>>
>>
>> --
>> +48 660 796 129
>>
>
Re: SSIS
Posted by Neeku Endhuku Nenu cheppanu <id...@gmail.com>.
Thank you for your response...
On Tue, 29 Jun, 2021, 2:17 pm Jarek Potiuk, <ja...@potiuk.com> wrote:
> I think there are no ready operators for SSIS. You can build your own (and
> contribute them back maybe) - it's actually not that difficult if you have
> a Python API, or some command line interface (which you can invoke via Bash
> Operator for example).
>
> I might be biased, and I do not know much about SSIS but from a quick
> search I think people treat SSIS as an expensive competitor to Airflow and
> mostly abandon it in favour of Airflow and Python:
>
> -
> https://medium.com/kabbage-engineering/running-airflow-at-kabbage-d39ebc101778
> - https://www.stitchdata.com/vs/ssis/airflow/
> -
> https://towardsdatascience.com/3-reasons-why-im-ditching-ssis-for-python-ee129fa127b5
>
> So maybe instead of integrating it with Airflow, consider replacing the
> use of SSIS with Airflow.
>
> Of course it's a narrow, biased, 2 minutes search in Google so don't take
> it for granted, do your own research and decide.
>
> J.
>
>
>
>
> On Tue, Jun 29, 2021 at 10:36 AM Neeku Endhuku Nenu cheppanu <
> idk.050694@gmail.com> wrote:
>
>> Thank you for your response.
>>
>> SSIS - SQL server integration services..
>>
>> We are trying to schedule these SSIS jobs in Airflow...
>>
>> We don't find any article about this...
>>
>> Is it possible to schedule these SSIS jobs in Airflow??
>>
>>
>> Please help me out..
>>
>>
>> Many Thanks,
>> Krishna v.
>>
>> On Tue, 29 Jun, 2021, 1:46 pm Ash Berlin-Taylor, <as...@apache.org> wrote:
>>
>>> What is SSIS?
>>>
>>> What have you tried already?
>>>
>>> What error are your getting?
>>>
>>> -ash
>>>
>>> On 29 June 2021 09:02:43 BST, Neeku Endhuku Nenu cheppanu <
>>> idk.050694@gmail.com> wrote:
>>>>
>>>> Please help me out on this..
>>>>
>>>> On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
>>>> idk.050694@gmail.com> wrote:
>>>>
>>>>> Hi team,
>>>>>
>>>>> Is it possible to schedule a SSIS package's in Airflow??
>>>>>
>>>>> Please let me know, if it possible...
>>>>>
>>>>>
>>>>> Many thanks,
>>>>> Krishna v.
>>>>>
>>>>
>
> --
> +48 660 796 129
>
Re: SSIS
Posted by Jarek Potiuk <ja...@potiuk.com>.
I think there are no ready operators for SSIS. You can build your own (and
contribute them back maybe) - it's actually not that difficult if you have
a Python API, or some command line interface (which you can invoke via Bash
Operator for example).
I might be biased, and I do not know much about SSIS but from a quick
search I think people treat SSIS as an expensive competitor to Airflow and
mostly abandon it in favour of Airflow and Python:
-
https://medium.com/kabbage-engineering/running-airflow-at-kabbage-d39ebc101778
- https://www.stitchdata.com/vs/ssis/airflow/
-
https://towardsdatascience.com/3-reasons-why-im-ditching-ssis-for-python-ee129fa127b5
So maybe instead of integrating it with Airflow, consider replacing the use
of SSIS with Airflow.
Of course it's a narrow, biased, 2 minutes search in Google so don't take
it for granted, do your own research and decide.
J.
On Tue, Jun 29, 2021 at 10:36 AM Neeku Endhuku Nenu cheppanu <
idk.050694@gmail.com> wrote:
> Thank you for your response.
>
> SSIS - SQL server integration services..
>
> We are trying to schedule these SSIS jobs in Airflow...
>
> We don't find any article about this...
>
> Is it possible to schedule these SSIS jobs in Airflow??
>
>
> Please help me out..
>
>
> Many Thanks,
> Krishna v.
>
> On Tue, 29 Jun, 2021, 1:46 pm Ash Berlin-Taylor, <as...@apache.org> wrote:
>
>> What is SSIS?
>>
>> What have you tried already?
>>
>> What error are your getting?
>>
>> -ash
>>
>> On 29 June 2021 09:02:43 BST, Neeku Endhuku Nenu cheppanu <
>> idk.050694@gmail.com> wrote:
>>>
>>> Please help me out on this..
>>>
>>> On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
>>> idk.050694@gmail.com> wrote:
>>>
>>>> Hi team,
>>>>
>>>> Is it possible to schedule a SSIS package's in Airflow??
>>>>
>>>> Please let me know, if it possible...
>>>>
>>>>
>>>> Many thanks,
>>>> Krishna v.
>>>>
>>>
--
+48 660 796 129
Re: SSIS
Posted by Neeku Endhuku Nenu cheppanu <id...@gmail.com>.
Thank you for your response.
SSIS - SQL server integration services..
We are trying to schedule these SSIS jobs in Airflow...
We don't find any article about this...
Is it possible to schedule these SSIS jobs in Airflow??
Please help me out..
Many Thanks,
Krishna v.
On Tue, 29 Jun, 2021, 1:46 pm Ash Berlin-Taylor, <as...@apache.org> wrote:
> What is SSIS?
>
> What have you tried already?
>
> What error are your getting?
>
> -ash
>
> On 29 June 2021 09:02:43 BST, Neeku Endhuku Nenu cheppanu <
> idk.050694@gmail.com> wrote:
>>
>> Please help me out on this..
>>
>> On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
>> idk.050694@gmail.com> wrote:
>>
>>> Hi team,
>>>
>>> Is it possible to schedule a SSIS package's in Airflow??
>>>
>>> Please let me know, if it possible...
>>>
>>>
>>> Many thanks,
>>> Krishna v.
>>>
>>
Re: SSIS
Posted by Ash Berlin-Taylor <as...@apache.org>.
What is SSIS?
What have you tried already?
What error are your getting?
-ash
On 29 June 2021 09:02:43 BST, Neeku Endhuku Nenu cheppanu <id...@gmail.com> wrote:
>Please help me out on this..
>
>On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
>idk.050694@gmail.com> wrote:
>
>> Hi team,
>>
>> Is it possible to schedule a SSIS package's in Airflow??
>>
>> Please let me know, if it possible...
>>
>>
>> Many thanks,
>> Krishna v.
>>
Re: SSIS
Posted by Neeku Endhuku Nenu cheppanu <id...@gmail.com>.
Please help me out on this..
On Thu, 24 Jun, 2021, 4:13 pm Neeku Endhuku Nenu cheppanu, <
idk.050694@gmail.com> wrote:
> Hi team,
>
> Is it possible to schedule a SSIS package's in Airflow??
>
> Please let me know, if it possible...
>
>
> Many thanks,
> Krishna v.
>