1 / 17

New Features in SSIS for Developers: SSISDB Catalog & Dependency Analyzer

Discover the latest enhancements in SQL Server Integration Services (SSIS) tailored for developers. This session covers the new SSISDB Catalog, Dependency Analyzer, and features such as Source and Destination Assistants, improved project deployment modes, and a revamped toolbox. Learn how to efficiently manage configurations, environments, and parameters, along with tips for upgrading older projects. Dive into execution monitoring and impact analysis, ensuring a thorough understanding of data lineage and dependencies within your projects.

helene
Télécharger la présentation

New Features in SSIS for Developers: SSISDB Catalog & Dependency Analyzer

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SSIS in Denali Peter ter Braake TrainSQL@live.nl

  2. Agenda • New for developers • SSISDB Catalog • Dependency Analyser

  3. New for developers • Source Assistant en Destination Assistant • Vervangen Connection project • Undo / Redo button • Transformation configuration zonder inputs • Vernieuwde toolbox • Tasks/transforms verdelen in groepen • Grouping in Data Flow • Hetzelfde als in Control Flow • Parameters • Met package of project scope • Alleen in nieuwe project deployment mode • Upgrade oude projecten • ‘gewone’ upgrade • Switchen naar nieuwe project deployment mode

  4. Agenda • New for developers • SSISDB Catalog • Dependency Analyser

  5. SSISDB Catalog • Database waarin packages worden opgeslagen • Vergelijkbaar met ReportServer database • Naast packages ook overige SSIS items • Alleen gebruikt met nieuwe project deployment • Bevat sprocs en views voor • Execution • Validation • Monitoring • Te maken door rechtsklik op Integration Services folder in SSMS • Create Catalog • Vereist CLR enabled on Instance

  6. Environments • Environment is verzameling van (environment) variables • Vervangen configurations • Environments worden gekoppeld aan projecten • Directe of relatieve verwijzing • Environment variables worden gekoppeld aan Integration Services parameters • Voor elke package execution kunnen we kiezen welke gekoppelde environment te gebruiken

  7. Run a package • T-SQL: • catalog.create_execution to create an execution object and specify the project, package, environment reference, and if the 32-bit runtime should be used while running on a 64-bit computer. The ID of the execution is returned with the @execution_id output argument. • catalog.set_execution_parameter_value (optional) to override a server default parameter value. The specified value will be used only with this instance of package execution.  • catalog.start_execution to start the execution. • Grafischvanuit SSMS

  8. Monitoring • Grafisch in SSMS • Query onderstaande views met T-SQL: • catalog.operations: To display all types of operations. • catalog.operation_messages: To display messages related to all types of operations. • catalog.executions: To display only package execution operations. • catalog.validations: To display only project and package validation operations. • catalog.extended_operation_info: To display additional validation details

  9. catalog.operations

  10. Agenda • New for developers • SSISDB Catalog • Dependency Analyser

  11. Dependency Services • Verzameltimpact and data lineage informatie • Werkt op (zievolgende slide) • SQL Server • Integration Services • Impact (Rode pijlenin graph): • welkeobjectenhangen van elkaaraf • Watvalteromalsikdezetabelverander? • Data lineage (Groenepijlenin graph): • Waarkomengegevensvandaan? • Kolomprijskomt via Derived Column uitflatfile

  12. supported objects: • Connection Manager • Task Component • Source Component • Destination Component • Transformation Component • External Input and Output Columns • Variable • XML Document • Database server • Database • Table • View • Column • TSQL Stored Procedure • File • Package

  13. Installeren • Run IALSetup.cmd from command prompt • In binn folder • E.g. ialsetup.cmd -f "C:\...\MSSQL11.MSSQLSERVER\MSSQL\DATA" -s localhost • IALSetupmaaktDependencyCatalog database • Zelfnogencryptie key aanmaken: • USE DependencyCatalogGOCREATE MASTER KEYENCRYPTION BY PASSWORD = ‘P@ssw0rd‘GO

  14. Impact Analysis

  15. Data Lineage

  16. Agenda • New for developers • SSISDB Catalog • Dependency Analyser • Vragen ???

  17. The END Peter ter Braake TrainSQL@live.nl 06-57582223

More Related