Transcript Document

Jose Chinchilla, MCTS, MCITP
 Nuevo Ambiente de Desarrollo SQL Server 2012
 Habilidades T-SQL a Super Poderes SSIS
 Demo
 BIDS
 Fuentes de Datos (Data Sources) y Vistas sobre fuentes de
datos (Data Source Views)
 Paquetes (Packages)
 Control Flow vs. Data Flow
 Fuentes (Sources) & Destinos (Destinations)
 Containers, Tasks, Transformations
 Variables
 Nuevo Modelo de Deployment
 Servicios de Integracion de SQL Server
SQL Server Integration Services
 Plataforma para Integracion de datos y flujo de datos
SSIS is a platform for data integration and workflow applications
 Herramienta para la extraccion, transformacion y carga de datos
(ETL) o extraccion, carga y transformacion de datos (ELT)
Tool used for data extraction, transformation, and loading (ETL)
and/or data extraction, loading then transforming (ELT)
 Herramienta para realizar operaciones DML, DDL, DCL, y soporte de
TCL
Tool to make DML operation (Selects, Inserts, Updates, Deletes), DDL operations
(Create/Drop Table), DCL operations (grant, revoke), TCL operatios (transactions, commit,
rollback)
 Herramienta para automatizar el mantenimiento y administracion
de bases de datos
Tool to automate maintenance and tasks for SQL Server database administration
Transform
Derive
Calculations
DB Tasks
Convert
Export
OLTP DB Maintenance
Import
VB 2008 Script
C# 2008 Script
Insert
Load
WMI Read/Write
Delete
Data Profiling
Update
File System Tasks
OLAP DB Maintenance
Aggregations
Extract
Cast
OLTP &
OLAP
ETL/ELT
Web
Services
T-SQL
Hadoop
& Linux
XML
C#
VB
XML
File &
Network
WMI
Control Flow
1.
2.
3.
4.
5.
6.
7.
8.
9.
Data Tasks
Database Object Transfer Tasks
Analysis Services Tasks
File and Network Protocol Tasks
Script and Program Tasks
Package Execution Tasks
WMI Tasks
Database Maintenance Plan Tasks
Other Maintenance Tasks
Learning SSIS under 1 hour
Data Flow
1. Sources
2. Destinations
3. Transformations
Control Flow
1.
2.
3.
4.
5.
6.
7.
8.
9.
Data Tasks
Database Object Transfer Tasks
Analysis Services Tasks
File and Network Protocol Tasks
Script and Program Tasks
Package Execution Tasks
WMI Tasks
Database Maintenance Plan Tasks
Other Maintenance Tasks
Data Flow
1. Sources
2. Destinations
3. Transformations
 BIDS SQL Server 2012
o Shell de Visual Studio 2010
• SSIS – Control Flow
o T-SQL
•
•
•
•
•
Update (set based), Execute Stored Procs
Recursiones - While (loops)
File Operations (Copy,Delete,Move)
sp_send_dbmail
bcp
•
•
•
•
•
Execute SQL Task
For Loop & For Each Container
File System Task
Send Mail Task
Bulk Insert Task
o T-SQL
•
•
•
•
•
•
•
•
•
•
•
•
Select
Insert
Update (row by row)
Case
Convert, Cast
IF
Mathematic, Date, String Function & Cast Operations
Merge
Joins (left, right, full outer)
Order by
Union
Group by (count, sum, avg)
• SSIS – Data Flow
•
•
•
•
•
•
•
•
•
•
•
•
Data Flow Source
Data Flow Destination
OLE DB Command
Conditional Split
Data Conversion
Derived Column
Derived Column
Merge
Merge Join
Sort
Union All
Aggregate
New Deployment Model:
• Package & Project Parameters
• Environments & Environment variables
Integration Services Catalog (SSISDB) – no more MSDB
New Resolve References Window
New Data Flow Transform: Data Correction Component
New Integration Services Import Project Wizard
Source and Destination Assistants
Collapsible Grouping in Data Flow
New Zoom-in & Zoom-out scale
Copy & Paste in place
Undo & Redo