June 22, 2024

Information is the lifeblood of any group. As knowledge volumes proceed to blow up, managing and integrating knowledge from numerous sources is turning into more and more essential. That is the place SQL Server Integration Providers (SSIS) performs a pivotal position. SSIS gives a flexible platform to ingest, remodel, combine, and migrate knowledge throughout methods.

The newest launch – SSIS 2016 – presents a significant improve over earlier variations with highly effective new capabilities. On this submit, we are going to discover the important thing enhancements in SSIS 2016 (additionally referred to as SSIS vNext or SSIS 816) and the way they assist unlock the total potential of knowledge integration.

Key Options of SSIS 816

SSIS 816 comes stacked with a number of promising options to make ETL improvement extra intuitive and environment friendly. Let’s take a look at a few of the most notable capabilities:

Fashionable Growth Setting

The up to date SSDT (SQL Server Information Instruments) present a extra productive IDE for SSIS with:

  • A tabbed interface for straightforward navigation between packages
  • Enhanced keyboard shortcuts for ceaselessly used instructions
  • Constructed-in code snippets to speed up improvement
  • Dwell knowledge viewers to examine knowledge flows throughout validation
  • Sooner validation checks to floor points early on

These instruments assist speed up improvement cycles and let builders focus extra on logic.

Versatile Deployment Choices

SSIS 2016 presents extra deployment configurations past the standard SQL Server Database Engine. Packages can now be deployed to:

  • SQL Server on Linux
  • Azure SQL Database
  • SQL Server on Azure VM
  • SQL Server on Docker

This assist for cross-platform deployment brings the specified flexibility to run SSIS workloads wherever.

Tighter Azure Integration

Native connectors are offered for Azure knowledge companies like Blob Storage, Information Lake Retailer, Cosmos DB and Azure SQL DW. SSIS may leverage Azure Information Manufacturing unit Pipelines for scheduling, execution and monitoring of jobs. Azure integration bridges on-prem and cloud knowledge for strong hybrid architectures.

Function Primarily based Entry Management

The brand new safety mannequin permits granular entry management to SSIS Catalog sources through area teams and Azure Energetic Listing. Setting-specific configurations may also be secured with precision. Multi-tenant safety is now doable for shared SSIS options.

Operational Insights

Constructed-in knowledge assortment and reporting present deeper visibility into SSIS operations. The stories cowl bundle execution statistics, efficiency metrics and errors. This permits smarter monitoring and troubleshooting.

Different Notable Enhancements

  • Sooner throughput and scalability through full 64-bit runtime
  • Wealthy parsing assist for JSON, Avro and Hadoop codecs
  • SQL 2016 At all times Encrypted safety for delicate knowledge
  • Bundle parameters for managing configurations higher

As evident, SSIS 2016 is leaps forward with options that immediately deal with ache factors in knowledge integration. Subsequent, let’s see how they translate to tangible advantages.

ETL Course of in SSIS 816

ETL (Extract, Rework and Load) stays probably the most widespread knowledge integration approach. It combines three separate features – knowledge extraction, transformation, and loading into targets.

SSIS gives built-in parts to implement widespread ETL duties like:

  • Connecting to supply databases
  • Importing and exporting information
  • Cleaning, remodeling knowledge
  • Loading knowledge warehouses
  • Information mining and evaluation

The improved capabilities in SSIS 2016 make the ETL improvement expertise a lot smoother.

The information extraction step includes connecting to numerous sources and extracting related knowledge. SSIS presents wealthy connectivity to relational (SQL Server, Oracle), information (CSV, XML) and massive knowledge (Hadoop) sources.

Key enhancements in SSIS 2016 embody:

Connectors for Azure Information Providers

Seamless connectivity to Azure Blob, ADLS, CosmosDB and SQL DW simplifies extracting knowledge from Azure platforms.

Connectivity to Hadoop

The ODBC drivers present out-of-the-box connectivity to Hadoop for extracting large knowledge into SSIS.

Staging Options

Native and distant file staging areas could be configured with compression, partitions and so forth. This assists in excessive efficiency extraction eventualities.

Change Information Seize (CDC)

The CDC parts can monitor modifications for web new extraction to scale back load volumes. That is helpful for frequent ETL runs.

Information Transformation

This includes manipulating the extracted knowledge earlier than loading into locations. Steps like sorting, becoming a member of, de-duplicating, validating knowledge occur right here.

Key knowledge transformation options in SSIS 2016 are:

Sooner Information Circulate Engine

The information circulation job is optimized for responding quicker with greater throughput capability. Parallelized transformations additional increase pace.

Richer Transformations

New derived column and combination transformations assist implement complicated logic with out customized coding. Free hand SQL statements may also be run.

Cloud Scale

Integration with Azure Information Lake Retailer (ADLS) and SQL DW allow large scale transformations on petabytes of knowledge in a distributed style.

Information High quality Providers

Tight coupling with SQL Server DQS introduces enterprise-grade knowledge profiling, matching and cleaning proper inside SSIS.

Information Loading

This last ETL stage includes loading the processed knowledge into goal databases or knowledge marts. SSIS presents broad assist for capturing reworked knowledge into platforms like Azure Synapse Analytics, SQL Server 2017, Hadoop clusters and so forth.

Notable knowledge loading options are:

Polybase for Quick Loading

Bulk load efficiency will get a lift by leveraging polybase expertise to maneuver large knowledge units quicker into SQL DW.

Change Monitoring for Incrementals

Loading solely web new rows via change monitoring improves effectivity over full hundreds. This assists frequent ETL runs.

Partition Desk Loading

Desk partitions routinely swap for rolling window hundreds into partitioned tables, eliminating lock conflicts.

Automated Indexing

Vacation spot desk indexes get auto created after load based mostly on standards. This accelerates downstream reporting on loaded knowledge.

As we reviewed, SSIS 2016 accelerates and optimizes all three phases – extraction, transformation and loading. Subsequent let’s see the way it helps with knowledge migration eventualities.

Information Migration and Transformation in SSIS 816

Information migration between methods is a standard want at present. Mainframe modernization, database upgrades, mergers/acquisitions, cloud migrations all contain shifting knowledge. SSIS presents a flexible workflow to plan and execute each one-time and periodic knowledge migrations.

Listed here are some key options that help knowledge migration initiatives in SSIS 2016:

Finish-to-Finish Pipeline Orchestration

SSIS can orchestrate all the migration workflow – knowledge profiling, mapping, ETL conversions, testing, cutover, post-migration testing as a complete knowledge migration manufacturing facility.

Any-to-Any Information Motion

Native connectivity permits shifting knowledge between virtually any supply and vacation spot – SQL Server, Oracle, DB2, Sybase together with information, large knowledge and cloud platforms.

Graphical Mappings

Intuitive drag-drop fashion mappings between sources and locations assist speed up ETL improvement for conversions. The metadata-driven method avoids guide coding.

Information Validation & Testing

Constructed-in knowledge viewers, profiling duties, conditional splits and auditing help with high quality testing to make sure migration integrity checks are met.

Simplified Re-Runs

Parameterization and configurations allow repeatable migration bundle execution. This permits simplified re-runs for dealing with failed rows.

DevOps Capabilities

Supply management integration, steady integration pipelines, automated testing and cloud deployment assist implement DevOps practices for knowledge migration initiatives too.

Cloud Information Integration

Managed Azure SSIS Integration Runtimes present a sturdy platform emigrate knowledge between on-prem and cloud knowledge platforms.

As we see, SSIS 2016 is properly geared to assist streamlined planning and execution of knowledge migration initiatives – one of many hardest IT challenges.

Information Safety and Safety in SSIS 816

Information safety is paramount when shifting knowledge between methods. SSIS 2016 incorporates a number of enhancements to safeguard knowledge:

Delicate Information Encryption

Finish-to-end encryption for delicate columns ensures safe knowledge switch over the wire and at relaxation. Bundle-level parameters present encryption keys.

Safe Credentials Storage

Encrypted credential managers retailer connection passwords and keys in protected shops like Azure Key Vault. This eliminates exposing secrets and techniques.

Entry Restrictions

RBAC (Function Primarily based Entry Management) at bundle, surroundings, and undertaking ranges permits fine-grained permissions to SSIS options.

Compliance Controls

Admin rights can implement deployment insurance policies, auditing specs, execution logging, and so forth. to fulfill safety and compliance governance.

Manufacturing Readiness

A examined, validated manufacturing launch ensures all safety measures are reviewed earlier than going stay to attenuate dangers.

Third-Occasion Audits

Unbiased audits by danger and safety consultants present an out of doors perspective to determine and deal with gaps proactively.

Ongoing Assessments

Common risk modeling, penetration testing, static code evaluation, and so forth. are wanted given at present’s dynamic risk panorama.

Therefore SSIS 2016 helps embed important knowledge safety capabilities into ETL options proper from the beginning via proactive design practices.

Migrating Present SSIS Packages to SSIS 816

For organizations with important investments in legacy SSIS packages, migrating current packages to SSIS 2016 is essential for standardization. The excellent news is the migration course of is kind of easy for well-structured packages.

Listed here are the principle steps for migration:


Catalog all current packages, configuration information, dependencies and so forth. Evaluate them to flag any improve points to deal with upfront.

Convert to Venture Deployment Mannequin

Bundle configurations have to be moved to the undertaking stage away from file system based mostly configurations. This permits model management.

Recompile Packages

All packages have to be recompiled in Visible Studio 2019 with the most recent undertaking parameters. This validates every thing works.

Repair Deprecations

Deprecated options might get eliminated in SSIS 2016. Establish any use of deprecated parts like customized log suppliers and repair them.

Retest Migrated Packages

Rigorously retest executed packages for accuracy, efficiency and performance in opposition to QA take a look at instances after migration.

Schedule Regression Testing

Schedule periodic regression testing on migrated packages to catch any new points which will emerge after migration to manufacturing.

Regulate Execution Plans

Primarily based on the deployment mannequin – file system vs database vs Azure companies, replace bundle workflows, scheduling and parameters appropriately.

With some diligence, most legacy packages could be migrated to realize standardization. For extremely complicated packages, a phased migration could also be preferable.


SSIS 2016 marks a significant milestone in empowering smoother knowledge integration throughout numerous methods. The host of promising options make ETL design, knowledge migration and course of orchestration markedly environment friendly.

Tight integration with Azure companies creates flexibility to function SSIS workloads on-premise or cloud. Scalability is enhanced to deal with large knowledge pipelines. Manageability will get higher with deeper operational visibility. Safety is hardened via fine-grained encryption, auditing and entry controls.

For organizations trying to embark on new knowledge analytics initiatives or consolidate current ETL sprawl, SSIS 2016 checks all of the bins. Its enterprise-grade toolset presents a sturdy basis to construct strategic knowledge integration options that evolve with enterprise wants. The time is correct to unleash its full potential.