1 / 23

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

6. < 1 hour. 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):

corine
Download Presentation

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

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 6 < 1 hour Presented by: Jose Chinchilla, MCP, MCTS, MCITPConsultant, Convergence Consulting Group

  2. 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: http://www.sqljoe.com Twitter: http://www.twitter.com/sqljoe Linked-in: http://www.linkedin.com/in/josechinchilla Email: jchinchilla@sqljoe.com jchinchilla@convergenceconsulting.net Rate my presentationhttp://www.speakerrate.com/speakers/8064-jchinchilla Learning SSIS under 1 hour

  3. 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

  4. We are hiring ! We are hiring ! We are hiring ! We are hiring !

  5. Immediate opening • Position Title:BI Consultant: Front-end BI Developer (Microsoft) • Location: Tampa, FL • Position Type: Fulltime • Length: 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: careers@convergenceconsulting.netor call 813.968.3238 Learning SSIS under 1 hour

  6. 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 Agenda Learning SSIS under 1 hour

  7. Terms & Acronyms BI: Business Intelligence DW: Data Warehouse SSIS: SQL Sever Integration Services BIDS: Business Intelligence Development Studio ETL: Extract, Transform & Load Package: object (XML file) that contains the business logic to manage workflows and process data Container: object to group tasks Task: component that performs an operation Transformation: component to modify and manipulate data Fact: A business measurement Measure: A quantifiable business process Dimension: Breakdown measures according to an area of interest Attribute: Characteristics that makeup a dimension member SCD: Slowly Changing Dimensions Natural Key: Unique key from data source Surrogate Key: Alternate unique key in the data warehouse Cube: Data structure that groups measures, dimensions, KPIs… Learning SSIS under 1 hour

  8. What is SSIS ? • SQL Server Integration Services • SSIS is a platform for data integration and workflowapplications • 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

  9. SSIS – Jack of all trades Transform Calculations Derive 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

  10. SSIS capabilities at a glance Learning SSIS under 1 hour

  11. SSIS task groupings Control Flow Data Flow 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 Sources Destinations Transformations Learning SSIS under 1 hour

  12. All of that in less than 1 hour?

  13. SSIS tasks – TODAY! Control Flow Data Flow 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 Sources Destinations Transformations Learning SSIS under 1 hour

  14. The Development Environment

  15. Development Environment • BIDS 2005/2008/2008-R2 • Windows • XP/Vista/7 • Server 2003/2008/2008-R2http://technet.microsoft.com/en-us/library/ms143506.aspx • SQL Server 2005/2008/2008-R2 Integration Services • Standard, Enterprise, Developer Learning SSIS under 1 hour

  16. 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

  17. SQL Server 2008/2008-R2 Edition Comparison Learning SSIS under 1 hour

  18. From T-SQL to SSIS

  19. T-SQL to SSIS T-SQL SSIS – Control Flow Execute SQL Task For Loop & For Each Container File System Task Send Mail Task Bulk Insert Task • Update (set based), Execute Stored Procs • While (loops) • File Operations (Copy,Delete,Move) • sp_send_dbmail • bcp Learning SSIS under 1 hour

  20. T-SQL to SSIS T-SQL 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 • 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

  21. Demo

  22. Additional Resources • Project Real: SQL Server 2005 BI in Practicehttp://www.microsoft.com/sqlserver/2005/en/us/project-real.aspx • Codeplexhttp://msftisprodsamples.codeplex.com • Business Intelligence Developer Networkhttp://www.bidn.com • MSDNhttp://msdn.microsoft.com/en-us/library/ms141026.aspx • Books on Line (BOL)http://technet.microsoft.com/en-us/library/ms141026.aspx • Rafael Salashttp://www.rafael-salas.com/ • Andy Leonardhttp://sqlblog.com/blogs/andy_leonard/default.aspx • Jamie Thomson (SSIS Junkie)http://sqlblog.com/blogs/jamie_thomson/default.aspx • Todd McDermidhttp://toddmcdermid.blogspot.com • Tim Mitchellhttp://timmitchell.net • SQLShare.com • Pragmatic Works • http://www.pragmaticworks.com Learning SSIS under 1 hour

  23. Thank you for attending! Blog: http://www.sqljoe.com Twitter: http://www.twitter.com/sqljoe Linked-in: http://www.linkedin.com/in/josechinchilla Email: jchinchilla@sqljoe.com jchinchilla@convergenceconsulting.net Rate my presentationhttp://www.speakerrate.com/speakers/8064-jchinchilla Learning SSIS under 1 hour

More Related