Visão Geral
Curso 55321 SQL Server Integration Services SSIS. Este curso apresenta os conceitos, arquitetura, ferramentas e técnicas de desenvolvimento de soluções de integração, transformação e movimentação de dados utilizando o Microsoft SQL Server Integration Services (SSIS). O treinamento aborda a construção de processos ETL (Extract, Transform, Load), permitindo extrair dados de diferentes fontes, realizar transformações, aplicar regras de negócio e carregar informações em bancos de dados e ambientes analíticos.
Os participantes aprenderão a desenvolver, configurar, executar, monitorar e solucionar problemas em pacotes SSIS, utilizando o SQL Server Data Tools (SSDT), fluxos de controle, fluxos de dados, conexões, componentes de transformação e mecanismos de tratamento de erros. O curso também contempla parâmetros, variáveis, expressões, implantação de projetos, execução automatizada, logging e otimização de desempenho, preparando os profissionais para desenvolver pipelines de integração de dados confiáveis e escaláveis em ambientes corporativos.
Objetivo
Após realizar este curso 55321 SQL Server Integration Services SSIS, você será capaz de:
- Compreender a arquitetura e os componentes do SQL Server Integration Services
- Desenvolver pacotes ETL para extração, transformação e carregamento de dados
- Configurar conexões com diferentes fontes e destinos de dados
- Criar fluxos de controle e fluxos de dados
- Implementar transformações e regras de negócio em pipelines de integração
- Trabalhar com variáveis, parâmetros, expressões e configurações dinâmicas
- Implementar tratamento de erros, logging e mecanismos de auditoria
- Desenvolver processos de integração com múltiplas fontes de dados
- Implantar e executar projetos SSIS no Integration Services Catalog
- Automatizar processos ETL utilizando SQL Server Agent
- Monitorar, diagnosticar e otimizar o desempenho de pacotes SSIS
- Implementar boas práticas de desenvolvimento, manutenção e governança de processos ETL
Publico Alvo
- Desenvolvedores SQL Server
- Desenvolvedores e engenheiros de dados
- Administradores de banco de dados (DBAs)
- Analistas de Business Intelligence (BI)
- Profissionais de ETL e integração de dados
- Analistas de sistemas e desenvolvedores de aplicações
- Especialistas em Data Warehouse
- Profissionais responsáveis por processos de migração e integração de dados
Pre-Requisitos
- Conhecimentos básicos de bancos de dados relacionais
- Familiaridade com Microsoft SQL Server
- Conhecimentos básicos de SQL e Transact-SQL (T-SQL)
- Noções de modelagem e manipulação de dados
- Conhecimentos básicos de lógica de programação são desejáveis
Materiais
Ingles/Portugues
Conteúdo Programatico
Module 1 – Introduction to SQL Server Integration Services
- Data integration concepts and ETL architecture
- Introduction to SQL Server Integration Services
- SSIS architecture and runtime components
- Integration Services projects and packages
- SQL Server Data Tools (SSDT) environment
- Visual Studio and SSIS development extensions
- Package execution and deployment models
- SSIS development lifecycle and best practices
Module 2 – SSIS Projects, Packages and Connections
- Creating and configuring SSIS projects
- Package structure and organization
- Connection Managers and connection configuration
- Connecting to SQL Server databases
- Flat File and Excel connections
- XML and other data source connections
- OLE DB and ADO.NET providers
- Connection expressions and dynamic configurations
- Package properties and execution settings
Module 3 – Control Flow and Workflow Management
- Control Flow architecture and components
- Tasks and containers in SSIS packages
- Execute SQL Task configuration
- Data Flow Task execution
- File System Task and file management
- Script Task fundamentals
- Sequence Containers and For Loop Containers
- Foreach Loop Containers
- Precedence Constraints and workflow conditions
- Event Handlers and package execution events
Module 4 – Data Flow and ETL Development
- Data Flow architecture and execution pipeline
- Sources, transformations and destinations
- OLE DB Source and Destination
- Flat File Source and Destination
- Excel Source and Destination
- Data Conversion and Derived Column transformations
- Conditional Split and Multicast transformations
- Lookup and Fuzzy Lookup transformations
- Aggregate, Sort and Merge Join transformations
- Union All and Merge transformations
- Slowly Changing Dimension (SCD) transformations
- Handling NULL values and data type conversions
Module 5 – Advanced Data Transformation and ETL Patterns
- Implementing complex data transformation workflows
- Data cleansing and standardization
- Data validation and quality controls
- Incremental data loading strategies
- Change Data Capture (CDC) integration concepts
- Lookup optimization and reference data management
- Handling duplicate records and data inconsistencies
- Implementing staging areas and intermediate processing
- Loading relational and dimensional data structures
- ETL orchestration and dependency management
Module 6 – Variables, Parameters and Expressions
- SSIS variables and variable scopes
- System and user-defined variables
- Project and package parameters
- Expressions and dynamic property configuration
- Property expressions and connection string management
- Environment variables and SSIS environments
- Dynamic file and database connections
- Reusable and parameterized ETL packages
Module 7 – Error Handling, Logging and Auditing
- SSIS error handling architecture
- Error outputs and redirection strategies
- Event handlers and exception management
- Logging providers and execution logging
- Data lineage and audit information
- Checkpoints and package restartability
- Transaction management and consistency
- Failure notifications and operational monitoring
- Troubleshooting package execution failures
Module 8 – Deployment, Scheduling and Administration
- SSIS deployment models
- Project Deployment Model
- Integration Services Catalog (SSISDB)
- Project and package deployment
- Environment configuration and parameter mapping
- Package execution using SQL Server Agent
- Command-line execution with DTExec
- Package execution using PowerShell
- Version control and release management
- SSIS administration and operational governance
Module 9 – Performance Optimization and Troubleshooting
- SSIS pipeline performance architecture
- Buffer management and memory optimization
- Data Flow performance tuning
- Fast Load and bulk insertion strategies
- Parallel execution and resource management
- Optimizing Lookup and Join operations
- Monitoring execution performance
- Identifying bottlenecks and resource constraints
- Troubleshooting package failures and connectivity issues
- Performance optimization best practices
Module 10 – Practical Labs and ETL Project Development
- Creating an SSIS project from scratch
- Extracting data from SQL Server and CSV files
- Transforming and validating data
- Implementing Lookup and Conditional Split operations
- Developing an incremental ETL loading process
- Implementing error handling and execution logging
- Configuring dynamic parameters and environments
- Deploying packages to SSISDB
- Scheduling ETL execution with SQL Server Agent
- Developing an end-to-end data integration project
TENHO INTERESSE