As a data professional, staying informed about the latest technologies and tools is essential for effectively performing your job. SQL Server Integration Services, or SSIS, is Microsoft’s enterprise data integration tool for data transformation and movement. The recently released version, SQL Server Integration Services 2016 (SSIS 2016), introduced numerous features that enhance scalability, performance, and usability. The latest update, SSIS 2019, builds upon these capabilities with additional enhancements.
In this article, we will explore some of the key new features in SSIS 2019 and how they can benefit your data integration projects. Whether you are just starting with SSIS or have been using it for years, understanding the latest features of this tool is crucial to maximizing its potential. SSIS 2019 brings improvements in package management, expands the toolbox, streamlines task execution, and enhances handling of Hadoop data.
What is SSIS 2016?
SSIS 2016 refers to SQL Server Integration Services 2016, the latest version of Microsoft’s enterprise data integration platform. Released at the end of 2016, SSIS 2016 builds upon the performance, scalability, and ease of use of its predecessors, making significant improvements.
Some important new features introduced in SSIS 2016 include:
- Azure Project Deployment: Now, you can directly deploy SSIS projects to Azure SQL Database, Azure SQL Data Warehouse, and Azure SQL Managed Instance. This allows you to lift and shift your SSIS workloads to the cloud with ease.
- Incremental Package Deployment: Only modified packages need to be redeployed, speeding up the deployment process.
- Data Taps: Using the new Data Tap task, you can easily capture data from a wide range of sources, leveraging more than 70 built-in connectors, including social media platforms, marketing tools, and manufacturing tools.
- Change Data Capture for Data Flows: Easily capture unordered samples of data during data flow, which is useful for data profiling and auditing purposes.
- Azure Feature Pack: Additional components to integrate with Azure services such as Data Lake Store, Blob Storage, and HDInsight.
In summary, SSIS 2016 enhances integration capabilities, harnesses the power of the cloud, boosts developer productivity, and improves scalability and performance. For any organization looking to build robust data integration solutions, SSIS 2016 is an excellent option to consider.
Key Features and Capabilities of SSIS 816
Scalability and Performance
SSIS 816 introduces advanced capabilities for scalability and performance, enhancing the execution of data integration processes. With the ability to leverage up to 64 cores for single package execution, significant improvements in performance are achievable.
Running packages in memory without the need for writing to disk further optimizes performance. The Scale Out feature facilitates the distribution of package execution across multiple machines, contributing to enhanced scalability.
Advanced Data Flow Capabilities
SSIS 816 brings a host of additions to components and transformations in data flow. It introduces new elements such as OData source and destination, along with ODBC flow destination.
Improved functionality for search, aggregate, and pivot operations within packages is also included. The enhancement of error handling, including improved redirection and row-level error handling, ensures better data flow control. Additionally, the introduction of new data types like DATE, TIME, DATETIME2, and DATETIMEOFFSET adds versatility to data handling.
Enhanced Tuning and Management
Several features in SSIS 816 contribute to improved tuning and management of integration processes. Parameters enable the passing of values at runtime, facilitating dynamic configuration updates.
The inclusion of reviews within the catalog for import and validation ensures the integrity of the execution and authentication processes. The introduction of new T-SQL stored procedures aids in efficient organization of the catalog.
SSIS 816 offers a range of capabilities and enhancements, streamlining the tuning and management of ETL and data integration processes. Leveraging these features empowers users to create robust solutions that showcase strong performance and scalability.
Benefits of Upgrading to SSIS 816
Transitioning your SQL Server Integration Services (SSIS) environment to version 816 offers numerous benefits:
Enhanced Security Features:
First and foremost, SSIS 816 offers improved security features such as always encrypted. It encrypts data in the database, decrypting it only within the application layer, ensuring the secure movement of sensitive data.Role-based security provides the capability to manage access to packages and their elements.
Advanced Integration with Visual Studio Code:
Secondly, upgrading to SSIS 816 provides a new and improved experience with the integration of Visual Studio Code. Visual Studio Code, without the need for SQL Server Data Tools (SSDT) extension pack, offers IntelliSense, debugging, and tuning capabilities for SSIS. Streamline your workflow by conveniently creating SSIS packages directly within Visual Studio Code.
Introduction of New Connectors:
Moreover, SSIS 816 introduces new connectors that enhance your data integration capabilities. Effortlessly link with platforms such as SAP BW, Hadoop, and Spark, enabling smooth data transfer through dedicated connectors. The OData connector utilizes data from any OData v3/v4 service, and the Web connector can extract data from web pages through XPath queries.
Improved Scalability through Scale Out:
Furthermore, SSIS 816 enhances performance through scale-out capabilities, allowing you to distribute the execution of a package across multiple nodes. Loading packages into memory using the Scale Out Master improves loading/unloading performance from the file system or SQL Server databases.
Long-Term Support and Expansion:
Lastly, SSIS 816 extends support for a decade, providing long-term collaboration for your data integration solutions. Regular updates and patches contribute to maintaining optimal uptime and addressing potential issues as they arise.
In summary, upgrading to SSIS 816 brings added security, a smoother development experience, expanded connectivity options, improved performance, and long-term support. For any organization relying on data integration, these benefits make upgrading to SSIS 816 a worthwhile investment.
Changes and Additions in SSIS 816:
SSIS 2016, the latest version of SQL Server Integration Services, introduces numerous changes and additions to enhance performance, scalability, and ease of use. SSIS 2016 incorporates several notable enhancements, including:
Performance and Scalability:
SSIS 2016 brings changes to enhance performance and scalability. It introduces a new Scale Out feature, allowing you to distribute packages across multiple servers to achieve high throughput. Improvements in data flow, logging, and checkpoint performance contribute to overall better functionality.
R Integration and Advanced Analytics:
In SSIS 2016, several new data mining algorithms are introduced along with the integration of R scripts. This integration enables the infusion of novel analytics and predictive modeling into your ETL operations. You can seamlessly run R scripts directly from SSIS packages and transmit data to R for analysis.
Enhancements in Deployment:
SSIS 2016 makes project deployment easier with new features like Incremental Package Deployment, Environment Cloning, and Project Versioning. This enables the streamlined deployment of updates to ongoing projects efficiently. Now, you can selectively deploy changed packages, clone environments, and revert to previous package versions if needed.
Other Improvements:
Additional enhancements in SSIS 2016 include:
- Incorporated additional data sources like SAP HANA, Hadoop, Azure SQL Data Warehouse, and integration with OData.
- Support for the latest version of Oracle 12c as existing data sources.
- Updated OLE DB Provider for SQL Server.
- Enhanced logging choices, including the option for customized logging when necessary.
- Tight integration with Power BI, allowing the initiation of Power BI reports directly from SSIS packages.
SSIS 2016 brings numerous enhancements in performance, scalability, and usability to elevate your ETL solutions. Additional data sources, data mining capabilities, and deployment options offer more flexibility and power for creating the latest data integration projects. Overall, SSIS 2016 is a significant improvement that enables you to build high-performance and robust ETL systems.
Getting Started with SSIS 816
To initiate SQL Server Integration Services (SSIS) 2016, follow these steps:
Download and Install SSIS:
Download SQL Server 2016, which encompasses SSIS. The SQL Server Developer Edition, available for free, includes SSIS. Install SQL Server 2016 and make sure to select Integration Services as a feature during installation.
Launch SSIS and Explore the Catalog:
Upon completing the installation of SQL Server 2016, access SSIS through the Microsoft SQL Server program group. Upon the initial launch of SSIS, you’ll encounter the SSIS Catalog, a repository for storing your SSIS projects. The catalog efficiently organizes your SSIS projects, environments, parameters, and logging.
Create a New SSIS Project:
To create your first SSIS package, right-click on the SSIS Catalog and select “New Folder” and then “Project.” Choose “Integration Services Project” as the project type. Name your new project and click “OK.” Your new SSIS project will now appear in the SSIS Catalog.
Design Your SSIS Package:
Open SQL Server Data Tools (SSDT) for SQL Server 2016 development environment by double-clicking on your new SSIS project. Right-click on the “Packages” folder and select “New SSIS Package” to start designing your ETL data flow. Design the elements of your data flow, such as sources, transformations, and destinations, at the design level.
Order and Execute Your Package:
Set properties in your SSIS package by configuring components with scripts or expressions. Subsequently, click the ‘Start’ button or press F5 to execute your package. This will initiate the execution of your package, allowing data to flow through the components of the data flow.
Deploy and Schedule Your Package:
To deploy your SSIS project for execution and scheduling of packages, right-click on your project in the SSIS Catalog, select “Deploy Project.” Your project and packages will be deployed. You can then use SQL Server Agent or other scheduling tools to execute your packages on a schedule.
By following these steps, you will initiate the process of creating, running, and scheduling SSIS packages. If you have any further questions, feel free to let me know!
Conclusion
At this point, you should possess a comprehensive grasp of SSIS version 816. With practice, you’ll become proficient in implementing data integration solutions and managing complex ETL processes efficiently. Starting with the basics, learning fundamental concepts, and applying your knowledge to real-world projects will help you evolve into a data professional. SSIS provides a robust set of tools to empower you as a data professional. Stay updated with the latest releases and features to maximize the capabilities of this platform. With hard work and perseverance, you’ll automate your data workflow and enhance data processing without consuming excessive time. The possibilities in data integration are limitless. Move forward and apply your new SSIS 816 skills in your work!