Integration Services

Microsoft first introduced Integration Services (SSIS) alongside SQL Server 2005 as the replacement for Data Transformation Services (DTS).  Since then, Integration Services has changed and matured with each new SQL Server version, but perhaps none more than the SQL Server 2016 release of Integration Services.  Incremental package deployment to the SISSDB catalog, support for Hadoop file system (HDFS), numerous Azure features such as Azure storage connectors, and many other features that the database developer and database administrator communities have been clamoring for are included in this release. The tool is designed to:

  • consume data from heterogeneous data sources
  • transform the data with multiple built-in transformation tasks or custom developed tasks
  • load the data to multiple built-in, 3rd party, or custom-developed destinations

The utilization of integration services brings many benefits to an organization. For one, heterogeneous data is sourced within the same package, empowering a single extraction process to access data from a variety of sources. Integration services is also highly capable of handling large volumes of data, enabling organizations to use integration services for large data movement.

Integration services can load and transform data in-memory, avoiding the temporary data from being written to disk. The tool is designed to handle complex transformations, multi-step operations, and custom-designed transformations, helping an organization meet all of its business needs, even if desired functionality of the solution isn’t built in yet.

Key2 Consulting has used integration services for Extracting, Transforming, and Loading Data at many clients. Some ways we have helped organizations are:

  • loading and transforming billion-record data sets from multiple data sources
  • customizing an auditing and logging framework to help monitor and troubleshoot integration services executions, as well as trend execution performance over time
  • implementing complex data transformation using .NET script
  • migrating existing code bases from package to project deployment mode
  • integrating SharePoint as a source for data warehouses to replace cumbersome, shared Excel documents

If you are considering implementing or customizing a new or existing integration services solution to better fit your business, we can help.