The SQL Server Integration Service

SQL Server Integration Services. At the heart of SQL is, of course, the database service which manages the database for us. Around the database is a number of services and those include integration services, analysis services, reporting services, and now master data services also. Those services, I say, are "around" the database engine because the database engine must be installed in order to run any of these other services.

Integration Services itself is a platform, and we use that platform to build tools or packages that allow data integration, so that we can take data from one source and move it over into another source. Integration Services also allows us to do data transformations, and really if you look at that, that's an ETL, an Extract, Transform, and a Load. And I should have drug out that word "transform" because that's where the lion's share of the work is.

Frankly, extracting and loading are pretty easy. It's the transformation that you need to spend a lot of time on. So, if we look at SQL Server Management Studio and we fire this on up, and connect into the database engine, there's our one database down here, and I have other instances of databases running up in here. Now, if I want to start Integration Services, I'm going to need to do this in a different way as we go along.

I'm going to right-click, right-click, run as admin. And now I'm just going to connect directly into Integration Services, and here you see packages. So the platform Integration Services is dependent on the SQL database that's installed, and then that platform has packages, which we'll cover in detail, and those packages taken together with different tasks perform actions to do the ETLs and other things, such as maintenance plans, etc.

That's what Integration Services is. It allows you to come in and to use the SQL server data tools, which happens to be right here, to create these packages and then to deploy the packages either into the file system, or into the MSDB program, and then to allow the management of those packages via the Integration Services service.