As a data professional, staying abreast of the latest technologies and tools is essential for effective job performance. SQL Server Integration Services (SSIS) stands out as Microsoft’s flagship enterprise solution for data integration, transformation, and migration. The release of SQL Server Integration Services 2016 (SSIS 2016) introduced numerous features enhancing scalability, performance, and usability. Building upon these advancements, the subsequent SSIS 2019 version incorporates additional enhancements to further elevate its capabilities.

This article delves into some of the key features introduced in SSIS 2019 and highlights their potential benefits for data integration projects. Whether you are a novice exploring SSIS or a seasoned user, comprehending the tool’s latest capabilities is crucial for maximizing its potential. SSIS 2019 showcases improvements in package management, toolbox expansion, workload scaling, and adept handling of Hadoop data. Continue reading to gain a comprehensive understanding of the latest update to this indispensable data integration tool.

What Is SSIS 816?

SSIS 2016, denoting SQL Server Integration Services 2016, represents the latest iteration of Microsoft’s enterprise data integration platform. Unveiled in late 2016, SSIS 2016 enhances upon its predecessors, focusing on improving performance, scalability, and user-friendliness.

The noteworthy features of SSIS 2016 encompass:

1. Project Deployment to Azure: Direct deployment of SSIS projects to Azure SQL Database, Azure SQL Data Warehouse, and Azure SQL Managed Instance facilitates the seamless migration of on-premises SSIS workloads to the cloud.

2. Incremental Package Deployment: Streamlining the deployment process, only modified packages are redeployed, accelerating the overall deployment speed.

3. Data Tap: Introducing a new data tap task enables effortless data extraction from diverse sources. With over 70 built-in connectors, including those for social media platforms, marketing, and productivity tools, data tapping becomes versatile and efficient.

4. Row Sampling Transformation: The addition of a row sampling transformation allows for the easy extraction of random data samples during the data flow process, proving valuable for testing and profiling purposes.

5. Azure Feature Pack: This includes additional components facilitating integration with Azure services such as Data Lake Store, Blob Storage, and HDInsight.

In summary, SSIS 2016 broadens integration capabilities by harnessing the capabilities of the cloud, streamlining developer workflows, and enhancing both scalability and performance. For organizations seeking robust data integration solutions, SSIS 2016 stands as a compelling choice.

Key Features and Capabilities of SSIS 816

Scalability and Performance

SSIS 816 introduces advanced scalability and performance features, empowering users to scale up to 64 cores for executing a single package. This enhancement significantly boosts the performance of data integration processes.

Furthermore, users now have the flexibility to run packages in-memory without the necessity of writing logs to disk, a choice that can contribute to performance optimization. The Scale Out feature is another notable addition, allowing the distribution of package execution across multiple machines, thereby enhancing overall scalability.

Advanced Data Flow Capabilities

SSIS 816 showcases numerous enhancements to data flow components and transformations, incorporating novel elements such as the OData Source and Destination, along with the ODBC Flow Destination. Notably, the package introduces performance enhancements for key operations like lookups, aggregations, and pivots. 

The update brings about refined error handling capabilities, featuring redirection and row-level error isolation for an improved error management process. Additionally, SSIS 816 expands its support for data types by including DATE, TIME, DATETIME2, and DATETIMEOFFSET.

Deployment and Management Improvements

SSIS 816 introduces several features that enhance deployment and management capabilities. With Project deployment, the ability to deploy complete projects to the SSIS catalog is now available, extending beyond individual packages. The inclusion of parameters facilitates the dynamic passing of values during runtime for configuration updates.

The catalog itself incorporates views for monitoring execution and validations, complemented by new T-SQL stored procedures that assist in catalog management.

In its entirety, SSIS 816 offers a plethora of capabilities and improvements, aiming to expedite ETL and data integration processes, enhance scalability, and simplify management. Leveraging these features empowers users to construct robust and high-performing data integration solutions.

Benefits of Upgrading to SSIS 816

Benefits of Upgrading to SSIS 816

The decision to upgrade your SQL Server Integration Services (SSIS) environment to version 816 brings forth numerous advantages.

Firstly, SSIS 816 delivers heightened security features, exemplified by Always Encrypted. This safeguarding mechanism encrypts data both in transit and at rest, ensuring that decryption occurs solely within the application layer. Additionally, role-based security options provide granular control over access to packages and their components.

Secondly, SSIS 816 introduces a modernized development experience with integration into Visual Studio Code. The SSIS extension pack within Visual Studio Code offers features like IntelliSense, debugging, and deployment capabilities for SSIS without the dependency on SQL Server Data Tools (SSDT). This integration allows direct SSIS package development within Visual Studio Code, streamlining the development workflow.

Furthermore, SSIS 816 presents new connectors that significantly broaden data integration capabilities. Connectors for SAP BW, Hadoop, and Spark facilitate seamless data transfer to and from these platforms. The OData connector is adept at consuming data from any OData v3/v4 service, while the Web connector efficiently extracts data from web pages using XPath queries.

Moreover, performance improvements are realized through scale-out capabilities, enabling the distribution of package execution across multiple nodes. The option to run packages in-memory by loading them into the Scale Out Master mitigates performance overhead associated with loading/unloading packages from the file system or SQL Server database.

Lastly, SSIS 816 extends maintenance support for a decade, ensuring prolonged support for data integration solutions. Regular updates and patches guarantee maximum uptime and address any potential issues.

In summary, the upgrade to SSIS 816 encompasses enhanced security measures, a streamlined development experience, expanded connectivity, improved performance, and a decade-long support commitment. These compelling benefits make the decision to upgrade a valuable investment for any organization reliant on effective data integration.

Changes and Enhancements in SSIS 816

SSIS 2016, the most recent release of SQL Server Integration Services, brings forth a myriad of changes and enhancements aimed at bolstering performance, scalability, and user-friendliness. Key improvements in SSIS 2016 encompass:

Performance and Scalability

SSIS 2016 has implemented alterations to enhance both performance and scalability. Notably, a novel scale-out feature enables the distribution of packages across multiple servers, resulting in elevated throughput. Additionally, performance enhancements have been introduced in areas such as data flow, logging, and checkpoints.

R Integration and Advanced Analytics

SSIS 2016 introduces a range of new data mining algorithms and integrates seamlessly with R scripts. This integration enables the infusion of advanced analytics and predictive modeling directly into your ETL processes. With the capability to execute R scripts within SSIS packages, you can seamlessly send data to R for analysis.

Deployment Enhancements

SSIS 2016 streamlines project deployment through enhanced features such as incremental package deployment, environment cloning, and project versioning. This simplifies the process of deploying updates to existing projects. With the ability to deploy only the packages that have undergone changes, clone environments, and revert to prior package versions when necessary, the deployment workflow becomes more efficient.

Other Improvements

SSIS 2016 incorporates various additional enhancements, including expanded connectivity to data sources such as SAP HANA, Hadoop, Azure SQL Data Warehouse, and OData. It also extends support to newer versions of existing data sources like Oracle 12c and introduces an updated OLE DB provider for SQL Server. The logging capabilities are enhanced with the inclusion of custom log providers, and there is a tighter integration with Power BI, enabling the initiation of a Power BI report directly from an SSIS package.

These improvements in SSIS 2016 contribute to enhanced performance, scalability, and usability in your ETL solutions. The augmented repertoire of data sources, data mining features, and deployment options provides greater flexibility and empowerment for constructing sophisticated data integration projects. In summary, SSIS 2016 stands as a significant advancement, enabling the creation of higher-performing and more robust ETL systems.

How to Get Started With SSIS 816

To get started with SQL Server Integration Services (SSIS) 2016, follow these steps:

Download and Install SSIS

To access SSIS, download SQL Server 2016, which incorporates this integration service. The free SQL Server Developer Edition, encompassing SSIS, is available for download. During the installation process, ensure that you select Integration Services as one of the features to be installed.

Launch and Explore SSIS

After installing SQL Server 2016, you can launch SSIS from the Microsoft SQL Server program group. The first time you launch SSIS, you will see the SSIS catalog, a repository for your SSIS projects. The SSIS catalog organizes your SSIS projects, environments, parameters, and logging.

Create a New SSIS Project

To initiate your initial SSIS package, start by right-clicking on the SSIS catalog and then choosing “New Folder”. After that, select “Project” and proceed by opting for “Integration Services Project” as the project type. Assign a name to your new project and click on “OK” to finalize its creation. Upon completion, your newly created SSIS project will be visible within the SSIS catalog.

Design an SSIS Package

To begin working on your new SSIS project, double-click it to open SQL Server Data Tools (SSDT), which serves as the SSIS development environment. Then, right-click on the “Packages” folder and choose “New SSIS Package” to initiate the creation of a new package. Utilize the drag-and-drop functionality to add data flow components such as sources, transformations, and destinations onto the design surface, allowing you to construct your ETL data flow.

Configure and Execute the Package

Customize the components within your SSIS package by adjusting their properties and incorporating scripts or expressions as needed. After configuring your package, initiate its execution by clicking the “Start” button or pressing F5. This will trigger the execution process, allowing data to seamlessly flow through the designated data flow components.

Deploy and Schedule the Package

To make your SSIS project and its packages available for scheduling and execution, begin by right-clicking on your project within the SSIS catalog and choose “Deploy Project.” This action will deploy both the project and its associated packages. After deployment, you can schedule the packages for execution using SQL Server Agent or any other preferred scheduling tool.

By following these steps, you’ll be on your way to building, running, and deploying SSIS packages. Feel free to reach out if you have any further questions!

Conclusion

By now, you should have developed a solid understanding of SSIS 816. With consistent practice, you’ll progressively enhance your ability to implement data integration solutions and manage intricate ETL processes. It’s essential to start with simple tasks, grasp the fundamentals, and gradually expand your expertise through practical projects. SSIS offers a comprehensive toolkit that empowers data professionals like yourself. Keep yourself informed about the latest releases and features to fully leverage the capabilities of this platform. 

With dedication and perseverance, you’ll soon be automating data workflows and streamlining data processing tasks. The potential for data integration is vast and limitless. Now, take your newfound SSIS 816 skills and apply them confidently!