3

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.

10
  • It's not 3rd party, but it is used heavily when there are multiple steps when using data integration, transformations, cleansing, etc. It's just a component, and a very powerful one, of SQL Server. See here for more
    – S3S
    Apr 24 '17 at 14:53
  • i understand it is not 3rd party in terms of owner. i meant software that acts on SQL Server rather than being a part of it. Anyway. That doesn't answer the question. It's easy to get info on what it does.
    – Zach Smith
    Apr 24 '17 at 14:55
  • "The SSIS Import/Export Wizard lets the user..." where on earth is this. i have SSMS and VS open.
    – Zach Smith
    Apr 24 '17 at 14:56
  • Well 3rd party and wrapper are both incorrect terms. Jobs usually execute a SSIS package, but jobs can do much more than fire off an SSIS package. I'm not being rude, but there are hundreds of posts and videos on the net about this.
    – S3S
    Apr 24 '17 at 14:58
  • @ZachSmith Right-click on database name -> Tasks -> Import Data/Export Data Apr 24 '17 at 15:03
5

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).

7
  • SSDT is SQL Server Data Tools, which is what the designer of SSIS packages used to be called. Now it's still called that, but tightly integrated into Visual Studio. The installation media should have an option to install the design environment as well. Although it is not typically installed on the developers work station. Apr 27 '17 at 1:13
  • Ah, so it's the new name for BIDS (Business Intelligence Designer Tools), thanks. Yep, just a specialized version of MS Visual Studio that's build especially for creating and working with SSIS packages. Edited my post to include that.
    – BradC
    Apr 27 '17 at 13:22
  • Thanks. I didn't realize the distinction between the SSIS server and an SSIS package.
    – Zach Smith
    Apr 27 '17 at 14:48
  • Thanks again for this answer. I'm now looking at installing the server component on my local machine and can't seem to find it. I have SQL Server 2016 developer edition. In the SSMS connection window, there is a local database engine server, a local analysis services server, a reporting services server, but no integration services server. Do you know why @BradC?
    – Zach Smith
    May 10 '17 at 14:36
  • @ZachSmith The "server component" of SSIS is really just a tool for shops to manage tons of SSIS packages on a standalone server that isn't running a SQL instance, I've never actually needed to use it. On your dev workstation, build SSIS packages using SSDT, run them with SSDT or using a client utility called "SSIS Package Execution Utility". Or run them on any SQL server, directly from a SQL agent job.
    – BradC
    May 10 '17 at 14:43
2

Both SSIS and SSDT are Microsoft product (component). I added 3 links because those explains these components better than I could.

SQL Server Integration Services(SSIS)

Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

SQL Server Data Tools (SSDT)

SQL Server Data Tools (SSDT) transforms database development by introducing a ubiquitous, declarative model that spans all the phases of database development inside Visual Studio. You can use SSDT Transact-SQL design capabilities to build, debug, maintain, and refactor databases. You can work with a database project, or directly with a connected database instance on or off-premise. Developers can use familiar Visual Studio tools for database development. Tools such as: code navigation, IntelliSense, language support that parallels what is available for C# and Visual Basic, platform-specific validation, debugging, and declarative editing in the Transact-SQL editor. SSDT also provides a visual Table Designer for creating and editing tables in either database projects or connected database instances. While you are working on your database projects in a team-based environment, you can use version control for all the files. When it’s time to publish your project, you can publish to all supported SQL platforms; including SQL Database and SQL Server. SSDT platform validation capability ensures that your scripts work on the target you specify.

Another good blog about what is SSDT.

2
  • I think there is a disconnect from people who have used SQL Server for a while and have seen new features introduced, vs new users such as myself. Yes. I get all the above. Is SSDT a means of working with SSIS packages? that is never explicitly mentioned
    – Zach Smith
    Apr 24 '17 at 15:12
  • I totally agree with you and that is the reason community is here to help. You can use SSDT to work with SSIS packages and much more. See this and this link. Apr 24 '17 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.