170 likes | 277 Views
SSIS in Denali. Peter ter Braake TrainSQL@live.nl. Agenda. New for developers SSISDB Catalog Dependency Analyser. New for developers. Source Assistant en Destination Assistant Vervangen Connection project Undo / Redo button Transformation configuration zonder inputs Vernieuwde toolbox
E N D
SSIS in Denali Peter ter Braake TrainSQL@live.nl
Agenda • New for developers • SSISDB Catalog • Dependency Analyser
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
Agenda • New for developers • SSISDB Catalog • Dependency Analyser
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
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
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
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
Agenda • New for developers • SSISDB Catalog • Dependency Analyser
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
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
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
Agenda • New for developers • SSISDB Catalog • Dependency Analyser • Vragen ???
The END Peter ter Braake TrainSQL@live.nl 06-57582223