Difference between scheduling Jobs in SSMS in Managed Instance and Data Factory

0
votes

If I have an Azure Managed Instance, what is the difference between scheduling jobs that contain T-SQL and/or packages within the MI on SSMS using SQL Agent and Azure Data Factory? Does the scheduled job run on the VM/Managed Instance instead of the IR in Data Factory? If so how does this handle SSIS Packages? I assume it will just fail?

I thought you could only schedule the jobs with SSIS packages inside Data Factory but I guess I was wrong as it has let me create a schedule (albeit it has not run yet).

Some of these jobs have been created from an offline sql server DB and recreated using a T-SQL script.

EDIT: I am aware of the differences between Data Factory and SQL Agent. I suppose the questions I am asking are:

  1. Can the SQL Agent Job Scheduler run Jobs that have both a T-SQL step and a SSIS package step just like a normal sql server or do the packages strictly have to be scheduled to run in Data Factory with SSIS IR? The official Microsoft Documentation is confusing

  2. If so, what is the best way to run a job that uses both T-SQL scripts and SSIS Packages in a Managed Instance?

1
sqlazuressmsazure-data-factorysql-agent-job
SQL Agent is a built-in feature in Locl-SQL Server or Azure MI, and Data Factory is most like a ETL tool. They are different things. Data Factory provide the feature to run the SSIS package with SSIS IR. Please edit your question and learn here: stackoverflow.com/help/how-to-ask - Leon Yue
Yes I am aware of the differences between Data Factory and SQL Agent. I suppose the questions I am asking are: 1. Can the SQL Agent Job Scheduler run Jobs that have an SSIS package in a step or do the packages strictly have to be scheduled to run strictly in Data Factory with SSIS IR. 2. If so, what is the best way to run a job that uses both T-SQL scripts and SSIS Packages in a Managed Instance? - Chaddeus
Hi @Chaddeus, thanks for clarify the question! - Leon Yue
Hi, If the answer is helpful for you, hope you can accept it as answer( click on the check mark beside the answer to toggle it from greyed out to filled in.). This can be beneficial to other community members. This is also a way to encourage more community members to help you, thank you! - Leon Yue

1 Answers

0
votes

For your first question "Can the SQL Agent Job Scheduler run Jobs that have an SSIS package in a step or do the packages strictly have to be scheduled to run strictly in Data Factory with SSIS IR?

Yes, there many tutorials talked about this, you could ref this official document:

  1. Microsoft: SQL Server Agent Jobs for Packages: You can automate and schedule the execution of SQL Server Integration Services packages by using SQL Server Agent. You can schedule packages that are deployed to the Integration Services server, and are stored in SQL Server, the SSIS Package Store, and the file system.

Azure SQL managed instance support SQL Agent job and can schedule run the SSIS packages, Data Factory also could achieve that with SSIS-IR. enter image description here

Second question, what is the best way to run a job that uses both T-SQL scripts and SSIS Packages in a Managed Instance?

Azure SQL managed instance has almost the same feature with on-premise SQL Server. So you could create the SQL Agent job to execute the T-SQL scripts or SSIS Packages like on-premise.

HTH.