Sql-server – SSIS in relation to SQL Server

sql serverssdtssist-sql

I'm finding acronomes such as 'SSIS', 'SSDT', etc etc a little confusing as to how they related to SQL Server. It seems to me that SQL Server implements the T-SQL language, and that a package such as 'SSIS' is a 3rd party 'wrapper' over the T-SQL language, and effectively a plug-in to SQL Server. Is this correct?

This blog post (here) mentions the benefits of SSIS vs T-SQL (which is why I assume that SSIS is a wrapper of T-SQL). Also, I can see that jobs created as SSIS packages can be found under SQL Server Agent in SSMS.

Best Answer

SSIS (SQL Server Information Services) is a first-party (Microsoft) 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 (and do all kinds of other very useful things).

(SSIS is the successor/replacement for DTS (Data Transformation Services) in SQL 2000 and prior, if you're familiar with that.)

To use SSIS you create a "package", which is a standalone file with a dtsx 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:

  1. Easily manage connections to multiple SQL servers (or other ODBC sources), for imports/exports or other operations
  2. Create high-performing data flows and transformations (fastest possible bulk imports, column lookups, robust error handling, including piping failed rows into a different dataset or destination table)
  3. Supports a branching flow, which can launch truly parallel tasks, something that normally isn't possible using T-SQL
  4. Supports other script/code languages for more sophisticated logic
  5. Easy mechanisms for looping through objects
  6. Detailed error logging and/or package progress logging
  7. 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 (if desired) run SSIS packages on a dedicated host.

EDIT: SSDT is the tool used to create and edit SSIS packages (thanks, @JonathanFite!). It is basically a customized version of MS Visual Studio. It used to be called BIDS (Business Intelligence Design Studio).

Related Question