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]
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
•
Enterprise Information Management
•
Information Portals
•
Location Intelligence (GIS)
•
Data Governance
•
Business Intelligence
•
Enterprise Performance Management
We are hiring !
We are hiring !
We are hiring !
We are hiring !
Immediate opening
Position Title: Location: Position Type: Length:
Experience: 2+ years BI Consultant:
Front-end BI Developer (Microsoft)
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
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
Convert OLTP DB Maintenance Import C# 2008 Script Derive Calculations Transform DB Tasks WMI Read/Write Data Profiling File System Tasks Aggregations VB 2008 Script Delete Insert Load Update Extract OLAP DB Maintenance Export Cast
Learning SSIS under 1 hour
OLTP & OLAP
Learning SSIS under 1 hour
Web Services ETL/ELT C# VB File & Network T-SQL WMI XML
Control Flow
1. Data Tasks 2. Database Object Transfer Tasks 3. Analysis Services Tasks 4. File and Network Protocol Tasks 5. Script and Program Tasks 6. Package Execution Tasks 7. WMI Tasks 8. Database Maintenance Plan Tasks 9. Other Maintenance Tasks
Data Flow
1. Sources 2. Destinations 3. Transformations
Learning SSIS under 1 hour
Control Flow 1. Data Tasks
2. Database Object Transfer Tasks 3. Analysis Services Tasks
4. File and Network Protocol Tasks 5. Script and Program Tasks
6. Package Execution Tasks 7. WMI Tasks 8. Database Maintenance Plan Tasks 9. Other Maintenance Tasks
Data Flow 1. Sources 2. Destinations 3. Transformations
Learning SSIS under 1 hour
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
Learning SSIS under 1 hour
Full-featured for development and testing.
(NOT FOR PRODUCTION)
Same as Enterprise Edition features!
Learning SSIS under 1 hour
• • • • •
T-SQL
Update (set based), Execute Stored Procs While (loops) File Operations (Copy,Delete,Move) sp_send_dbmail bcp • • • • •
SSIS – Control Flow
Execute SQL Task For Loop & For Each Container File System Task Send Mail Task Bulk Insert Task
Learning SSIS under 1 hour
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
Learning SSIS under 1 hour
Additional Resources
• • • • • • • • • • • • 1.
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 http://www.pragmaticworks.com
Learning SSIS under 1 hour
Blog: Twitter: Linked-in: Email: Thank you for attending!
http://www.sqljoe.com
http://www.twitter.com/sqljoe http://www.linkedin.com/in/josechinchilla [email protected]
Rate my presentation
http://www.speakerrate.com/speakers/8064-jchinchilla
Learning SSIS under 1 hour