6 Presented by Jose Chinchilla MCP MCTS MCITP

6 Presented by: Jose Chinchilla, MCP, MCTS, MCITP Consultant, Convergence Consulting Group

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 jchinchilla@sqljoe. com jchinchilla@convergenceconsulting. net 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 • Data Governance • Information Portals • Business Intelligence • Location Intelligence (GIS) • Enterprise Performance Management

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 -R 2, Share. Point, 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: careers@convergenceconsulting. net or call 813. 968. 3238 Learning SSIS under 1 hour

Agenda § § Terms & Acronyms Development environment for SSIS 2008 -R 2 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

Terms & Acronyms 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…

What is SSIS ? § 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

SSIS – Jack of all trades 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 Cast Learning SSIS under 1 hour Extract

SSIS capabilities at a glance OLTP & OLAP ETL/ELT T-SQL Web Services XML C# VB WMI File & Network Learning SSIS under 1 hour

SSIS task groupings 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

All of that in less than 1 hour?

SSIS tasks – TODAY! 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

The Development Environment

Development Environment § BIDS 2005/2008 -R 2 § Windows § XP/Vista/7 § Server 2003/2008 -R 2 http: //technet. microsoft. com/en-us/library/ms 143506. aspx § SQL Server 2005/2008 -R 2 Integration Services § Standard, Enterprise, Developer Learning SSIS under 1 hour

Get your own license for $50 ü Full-featured for development and testing. (NOT FOR PRODUCTION) ü Same as Enterprise Edition features! Learning SSIS under 1 hour

SQL Server 2008/2008 -R 2 Edition Comparison Learning SSIS under 1 hour

From T-SQL to SSIS

T-SQL to SSIS 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 to SSIS 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 Merge Join Sort Union All Aggregate

Demo

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/ms 141026. aspx • Books on Line (BOL) http: //technet. microsoft. com/en-us/library/ms 141026. 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 Mc. Dermid 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 jchinchilla@sqljoe. com jchinchilla@convergenceconsulting. net Rate my presentation http: //www. speakerrate. com/speakers/8064 -jchinchilla Learning SSIS under 1 hour