I am new to SSIS. Is it possible to do without a Data Flow Task (and thus the Data Flow Tab) and do everything with the "Execute SQL Task" for ETL processes when all databases are on SQL Server?
That is, can one do everything (ETL) with the "Execute SQL Task" from the "Control Flow Tab"?
I don't understand why I should use the "Data Flow Task" and thus the "Data Flow Tab" when I can do everything with the "Execute SQL Task"..
As with most questions, the answer here is a clear-cut, "It depends."
If all of your data is in SQL Server, and all of the databases are on the same server (and are likely to stay that way into the foreseeable future), then no, you don't need to use data flow tasks at all. But, as noted in the comments, if that's the case, you really don't need SSIS at all, either. On one server, you can accomplish everything thing with stored procedures, orchestrated through SQL Agent jobs.
On the other hand, if you're accessing data from multiple servers, say a source system and a warehouse system, or a production server and a reporting environment, or importing data from disparate flat files, then SSIS offers a fairly easy route for moving data from server A to server B. No linked servers to worry about, and typically better performance than linked servers anyway. With even a moderately advanced SSIS package set up, you can add parameters that will allow the flexibility to move a database from one server to another without necessitating a code change.
A mature SSIS framework (or any ETL tool set, really) can make your ETL code less tightly bound to your server and database architecture, and changes to either are simpler to execute without negatively impacting the other.
Although I agree with what was said, I feel like this discussion left out some significant considerations. (FYI...This comes a decade of developing and supporting enterprise DW using SSIS and another decade using several other ETL tools).
First, lets consider what they do...Dataflows give full potential of any SSIS dataflow toolset with a ton of tools included as well as add-ons (e.g. datatype transformation, BDD for parallelism, etc). SqlTask just runs TSQL for you - meaning whatever feel like coding into inline SQL or running stored proc. For that task you're just using SSIS as a calling program and nothing more.
Dataflows give visibility into where your ETL processing is at. Hmm, so isn't that "why" you are using an ETL tool in the first place? Think about it. It's that graphical insight and standardization. Otherwise just code ton of stored procs and geek out. Lay on that technical debt and job security. But dataflows not only make it graphical, create some standard interface for the next person to support, but show you data as it flows instead of just sitting there spinning until its done. And if you're running dynamic code and store it in a variable for the dataflow you can see the SQL that was run. That's massive. Then there's the metadata checking SSIS does with a dataflow to tell you when things change. SqlTask will likely just blow up if metadata changes.
This is especially true if you're using BiXpress (which I would NEVER ever ever use SSIS without and if you're not using BiXpress ask yourself, "why the heck not?!"...there's multitude of benefits of Bixpress - email me if questions...and no I'm NOT a consultant).
So like in all things IT and like what was said, it depends. I still use SqlTasks to move from point A to B IF I'm on the same database AND it proves significantly faster. I prefer dataflows to hard coding insert/selects but sometimes performance just demands a simple insert/select.
Note I said database and not server. Folks welcome to the cloud....if you use Azure SQL or Synapse DW or SQL managed instance you're basically contained inside of a single database. If you're not there yet you will be so go ahead and act like you're there so you don't cause issues when migrating.
