SQL Server Integration Services (SSIS) plays a crucial role in the world of data professionals, providing enterprise data integration, transformation, and migration capabilities. With the release of SQL Server Integration Services 2016 (SSIS 2016), Microsoft introduced several new features to enhance scale, performance, and usability. Building upon these capabilities, SSIS 2019 further expands the tool’s potential. In this article, we will explore the most important new features of SSIS 2019 and how they can benefit your data integration projects. Whether you are a beginner or an experienced SSIS user, understanding the latest enhancements of this tool is essential for maximizing its potential.
What is SSIS 816?
SSIS 816 refers to SQL Server Integration Services 2016, the latest version of Microsoft’s enterprise data integration platform. Released in 2016, SSIS 2016 improved upon its predecessors by enhancing performance, scalability, and ease of use. This version introduced features like project deployment to Azure, incremental package deployment, data tap, row sampling transformation, and Azure Feature Pack. These additions expanded integration capabilities, leveraged the power of the cloud, increased developer productivity, and improved scale and performance.
Key Features and Capabilities of SSIS 816
Scalability and Performance
SSIS 816 provides enhanced scalability and performance capabilities, allowing you to scale up to 64 cores for a single package execution. This greatly improves performance for data integration processes. Additionally, you have the option to run packages in-memory without writing logs to disk, resulting in improved performance. The Scale Out feature enables you to distribute package execution across multiple machines, enhancing scalability.
Advanced Data Flow Capabilities
SSIS 816 introduces several enhancements to data flow components and transformations. It includes new data flow components like the OData Source and Destination, as well as the ODBC Flow Destination. Performance improvements are also present for lookups, aggregations, pivots, and error handling. New data types, such as DATE, TIME, DATETIME2, and DATETIMEOFFSET, are supported, providing increased flexibility.
Deployment and Management Improvements
SSIS 816 offers various features that improve deployment and management processes. With project deployment, you can deploy entire projects to the SSIS catalog, rather than individual packages. Parameters allow you to pass values at runtime for configuration updates. The catalog includes views to monitor execution and validations, and new T-SQL stored procedures assist in catalog management.
SSIS 816 provides a wealth of capabilities and improvements for faster, more scalable, and easier-to-manage ETL and data integration processes. Leveraging these features, you can build robust and high-performing data integration solutions.
Benefits of Upgrading to SSIS 816
Upgrading your SQL Server Integration Services environment to version 816 brings several advantages. Firstly, enhanced security features, such as Always Encrypted, protect sensitive data both in motion and at rest. Role-based security controls access to packages and their elements. Secondly, SSIS 816 offers a modernized development experience with Visual Studio Code integration. The SSIS extension pack in Visual Studio Code provides IntelliSense, debugging, and deployment capabilities, simplifying your workflow. Additionally, new connectors expand your data integration capabilities, including SAP BW, Hadoop, Spark, OData, and web connectors. Performance is improved through scale-out and in-memory package execution. Lastly, SSIS 816 provides extended maintenance for 10 years, ensuring long-term support for your data integration solutions.
Changes and Enhancements in SSIS 816
SSIS 2016 introduced significant changes and enhancements to improve performance, scale, and ease of use. Performance and scalability improvements include a new scale-out feature for distributing packages across multiple servers, as well as enhancements in data flow, logging, and checkpoints. Integration with R scripts allows for advanced analytics and predictive modeling within ETL processes. Deployment enhancements simplify project deployment by introducing incremental package deployment, environment cloning, and project versioning. Additional improvements include enhanced connectivity, updated OLE DB provider for SQL Server, enhanced logging, and tighter integration with Power BI.
How to Get Started with SSIS 816
To begin using SQL Server Integration Services 2016, follow these steps:
- Download and Install SSIS: Download SQL Server 2016, which includes SSIS. The free SQL Server Developer Edition provides access to SSIS. During installation, select Integration Services as one of the features to install.
- Launch and Explore SSIS: After installing SQL Server 2016, launch SSIS from the Microsoft SQL Server program group. The SSIS catalog serves as a repository for your SSIS projects. Take some time to explore the catalog and familiarize yourself with its organization.
- Create a New SSIS Project: Right-click the SSIS catalog and select “New Folder” and then “Project.” Choose “Integration Services Project” as the project type, provide a name for the project, and click “OK” to create it. Your new SSIS project will now appear in the SSIS catalog.
- Design an SSIS Package: Double-click your new SSIS project to open SQL Server Data Tools (SSDT), the SSIS development environment. Right-click the “Packages” folder and select “New SSIS Package” to create a new package. Use the design surface to drag and drop data flow components such as sources, transformations, and destinations to build your ETL data flow.
- Configure and Execute the Package: Set properties, add scripts or expressions, and configure the components in your SSIS package. Once configured, execute the package by clicking the “Start” button or pressing F5. Watch as data flows through the data flow components.
- Deploy and Schedule the Package: To make your SSIS project and packages available for scheduling and execution, right-click your project in the SSIS catalog and select “Deploy Project.” Once deployed, you can schedule the packages for execution using SQL Server Agent or another scheduling tool.
By following these steps, you can start building, running, and deploying SSIS packages. As you gain experience, you’ll become more proficient in implementing data integration solutions and handling complex ETL processes.
Conclusion
In conclusion, SSIS 816 offers a wide range of features and enhancements that empower data professionals to optimize their data integration projects. With improved scalability, performance, and usability, SSIS 816 enables you to build robust and high-performing ETL solutions. By upgrading to SSIS 816, you gain enhanced security, a streamlined development experience, expanded connectivity, improved performance, and long-term support. Stay up to date with the latest releases and features of SSIS to unleash the full potential of this versatile data integration tool. With perseverance and practice, you’ll be automating data workflows and optimizing data processing in no time. The possibilities for data integration are endless. Now, go forth and leverage your newfound SSIS 816 skills to transform your data integration projects.