SSIS - SQL Joe's Blog
Download
Report
Transcript SSIS - SQL Joe's Blog
Jose Chinchilla
MCITP: Database Administrator, SQL Server 2008
MCTS: SQL Server 2005 & 2008
MCTS: Business Intelligence SQL Server 2008
Position(s):
Business Intelligence Consultant, Convergence Consulting Group
President , Tampa Bay Business Intelligence User Group & PASS Official Chapter
“DBA by accident, BI Developer by chance, Geek by Choice”
Blog:
Twitter:
Linked-in:
Email:
http://www.sqljoe.com
http://www.twitter.com/sqljoe
http://www.linkedin.com/in/josechinchilla
[email protected]
[email protected]
Rate my presentation
http://www.speakerrate.com/speakers/8064-jchinchilla
Learning SSIS under 1 hour
Convergence: “to move toward or achieve union or a common conclusion or result. “
•
Florida based consulting firm founded by partners from a “big-five” consulting background.
•
Services and Practices
•
Enterprise Data Warehouse
•
Data Governance
•
Enterprise Information Management
•
Business Intelligence
•
Information Portals
•
Enterprise Performance Management
•
Location Intelligence (GIS)
We are hiring !
We are hiring !
We are hiring !
We are hiring !
Immediate opening
Position Title:
BI Consultant:
Front-end BI Developer (Microsoft)
Location:
Position Type:
Length:
Tampa, FL
Fulltime
Temp/ Contract-Hire/ Perm
Skills required: SSRS 2008 / 2008-R2, SharePoint,
Performance Point Services, T-SQL, MDX
Desirable skills: SSIS & SSAS, Dashboards & Score
Cards development
Experience: 2+ years
For immediate consideration, please send your
resume as a Word attachment along with your
rate to: [email protected] or
call 813.968.3238
Learning SSIS under 1 hour
Terms & Acronyms
Development environment for SSIS 2008-R2
T-SQL to SSIS
Demo
BIDS
Data Sources, Data Source Views
Packages
Control Flow vs. Data Flow
Sources & Destinations
Containers, Tasks, Transformations
Variables
Learning SSIS under 1 hour
BI:
DW:
SSIS:
BIDS:
ETL:
Package:
Container:
Task:
Transformation:
Fact:
Measure:
Dimension:
Attribute:
SCD:
Natural Key:
Surrogate Key:
Cube:
Learning SSIS under 1 hour
Business Intelligence
Data Warehouse
SQL Sever Integration Services
Business Intelligence Development Studio
Extract, Transform & Load
object (XML file) that contains the business logic to manage workflows
and process data
object to group tasks
component that performs an operation
component to modify and manipulate data
A business measurement
A quantifiable business process
Breakdown measures according to an area of interest
Characteristics that makeup a dimension member
Slowly Changing Dimensions
Unique key from data source
Alternate unique key in the data warehouse
Data structure that groups measures, dimensions, KPIs…
SQL Server Integration Services
SSIS is a platform for data integration and workflow applications
Tool used for data extraction, transformation, and loading (ETL)
and/or data extraction, loading then transforming (ELT)
Tool to make DML operation (Inserts, Updates, Deletes). Can also
execute DDL operations (Create/Drop Table).
Tool to automate maintenance and tasks for SQL Server
database administration
Tool to update OLAP cube partitions, slowly changing
dimensions
Learning SSIS under 1 hour
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
Learning SSIS under 1 hour
OLTP &
OLAP
ETL/ELT
T-SQL
Web
Services
XML
C#
VB
WMI
File &
Network
Learning SSIS under 1 hour
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
Learning SSIS under 1 hour
Data Flow
1. Sources
2. Destinations
3. Transformations
BIDS 2005/2008/2008-R2
Windows
XP/Vista/7
Server 2003/2008/2008-R2
http://technet.microsoft.com/en-us/library/ms143506.aspx
SQL Server 2005/2008/2008-R2 Integration Services
Standard, Enterprise, Developer
Learning SSIS under 1 hour
Full-featured for development and testing.
(NOT FOR PRODUCTION)
Same as Enterprise Edition features!
Learning SSIS under 1 hour
Learning SSIS under 1 hour
T-SQL
•
•
•
•
•
Update (set based), Execute Stored Procs
While (loops)
File Operations (Copy,Delete,Move)
sp_send_dbmail
bcp
Learning SSIS under 1 hour
SSIS – Control Flow
•
•
•
•
•
Execute SQL Task
For Loop & For Each Container
File System Task
Send Mail Task
Bulk Insert Task
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)
Learning SSIS under 1 hour
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
Additional Resources
•
Project Real: SQL Server 2005 BI in Practice
http://www.microsoft.com/sqlserver/2005/en/us/project-real.aspx
•
Codeplex
http://msftisprodsamples.codeplex.com
•
Business Intelligence Developer Network
http://www.bidn.com
•
MSDN
http://msdn.microsoft.com/en-us/library/ms141026.aspx
•
Books on Line (BOL)
http://technet.microsoft.com/en-us/library/ms141026.aspx
•
Rafael Salas
http://www.rafael-salas.com/
•
Andy Leonard
http://sqlblog.com/blogs/andy_leonard/default.aspx
•
Jamie Thomson (SSIS Junkie)
http://sqlblog.com/blogs/jamie_thomson/default.aspx
•
Todd McDermid
http://toddmcdermid.blogspot.com
•
Tim Mitchell
http://timmitchell.net
• SQLShare.com
• Pragmatic Works
1. http://www.pragmaticworks.com
Learning SSIS under 1 hour
Thank you for attending!
Blog:
Twitter:
Linked-in:
Email:
http://www.sqljoe.com
http://www.twitter.com/sqljoe
http://www.linkedin.com/in/josechinchilla
[email protected]
[email protected]
Rate my presentation
http://www.speakerrate.com/speakers/8064-jchinchilla
Learning SSIS under 1 hour