SSIS – Integration Services

Blog » SQL Server » SSIS - Integration Services

integration services ssis packageSQL Server Integration services (SSIS) is a set of tools for the development and automation of data pumps and data transformations, i.e. how to transfer data from point A to point B. These activities are called ETL (extract, transfer, load) processes. SSIS includes a number of tools and features for efficient development, administration and maintenance

Intro to SQL Server Integration Services – SSIS for Data pipelines

Below, you will find some basic information and navigation to resources (this blog or external ones if the information is not available here):

  • Installation: The software components required to run and operate SSIS are part of the SQL Server installation.
  • SSIS packages (data pipelines) are created: In Visual Studio 2019 or older (or SSDT), with the need to activate the SQL Server Integration Services add-in.
  • After creating an SSIS package: You can deploy it to the server in the SSIS catalog, and from there, you can execute this package using scheduled tasks via SQL Server Agent. This is the point of automating data warehouses built on SQL Server + SSIS data integrations. You can execute packages from localhost without limitations. To execute packages from the server using SQL Agent, you can do so only with these editions:
      • SQL Server Developer – free for testing
      • SQL Server Standard
      • SQL Server Enterprise

SSIS Tutorials for Beginners on This Website

Introduction to Integration services, how to prepare a data pump step by step:

If the default selection of connectors (for sources) and components for your data flows is not sufficient, you can use third-party paid tools that will expand your connectivity options to many more sources:

Modern Alternatives to Integration Services (SSIS)

Currently, SSIS is primarily used for on-premises older systems that are already locked into this solution (it is too expensive to redevelop this solution). New solutions tend to use more modern ETL flow solutions such as:

  • Apache Airflow – open source, typically for large enterprises (relatively complex)
  • AWS Glue – part of the paid AWS cloud ecosystem by Amazon. Also more suitable for larger clients.
  • Mage.ai (covered on this blog) – new open-source alternatives like Mage are great in terms of developer experience and can be easily installed with a few clicks.
  • And many others (Pentaho, Azure Data Factory – cloud alternative to SSIS, IBM, Oracle, Informatica, etc.)