SSIS (SQL Server Information Services) is a first-party component/platform for Microsoft SQL Server. It isn't an add-on product, you will be given the option to include components when installing SQL server.
Don't think of it as a wrapper for T-SQL, think of it as a place to run T-SQL. To use SSIS you create a "package", which is a standalone file with a tax extension. You create the package in SSDT (SQL Server Data Tools), a customized version of MS Visual Studio (this used to be called BIDS). Your SSIS package can be executed on any SQL server, by calling it directly from a SQL Agent job. You can also run it on a separate server that has SSIS server components installed, or from a development workstation using either SSDT or the "SSIS Package Execution Utility".
Here's an example of typical use:
The SQL Agent allows you to create scheduled jobs, in which you might run a bit of T-SQL code to perform some database maintenance or something. Paste the T-SQL code into the step detail window, set the job schedule, and you are good to go. But what if you need to do something more complex than you'd normally attempt in T-SQL? Or something that T-SQL can't do at all?
SSIS allows you to run T-SQL, but you can also do a ton of other stuff:
- Easily manage connections to multiple SQL servers, for imports/exports or other operations
- Create high-performing data flows and transformations
- Supports a branching flow, which can launch truly parallel tasks, something that normally isn't possible using T-SQL
- Supports other scripts/code languages for more sophisticated logic
- Easy mechanisms for looping through objects
- Detailed error logging and/or package progress logging
- Lots of other cool stuff.
So you create your more sophisticated "nightly data processing" SSIS package that imports and validates data from 2 other SQL servers, 1 Oracle instance, and 5 text files, then processes the data per your business rules, all using parallel processing to take advantage of your multiple CPU cores. Then call that from your job step instead of your T-SQL code.
Note: installing the Server Component of SSIS isn't strictly necessary, that is a tool to store and manage SSIS packages, or run SSIS packages on a dedicated host.
EDIT: SSDT is the tool used to create and edit SSIS packages. It is basically a customized version of MS Visual Studio. It used to be called BIDS.
