Connectivity Options for Data Integration with Microsoft SQL Server Integration Services (SSIS)

2. Connectivity Options for Data Integration with Microsoft SQL Server Integration Services (SSIS) Cedric Labuschagne Solutions Architect BI-Lateral BIN301

3. Agenda • What is new in SSIS 2008? • Connectivity components • Connectivity Categories • New SSIS connectors

4. Majority of the content for decision making are in multiple heterogeneous sources (e.g. ERP systems, databases, flat files) Need to integrate & consolidate data from all these sources, and make quality data available to users Thinking Bigger About Business Intelligence ERP Systems FlatFile Excel XML Databases Connectivity is the key to address these challenges • ERP

5. What is new in SSIS 2008? • Improved and new task components • New Lookup Capabilities (caching enhanced) • Data Profiling Task (data quality checking) • C# Scripting. VSA replaced by VSTA • Scalable Data Flow (thread pooling) • Enhanced and new connectivity support • ADO.NET Source and Destination • New connectors for Oracle, Teradata and SAP BI

6. SSIS Connectivity Components SSIS Package FTP ExecuteSQL Task Data Flow Task Lookup FlatFile ADO.NET FTP OLEDB FLATFILE ADO.NET Task DataTransformation Data Source/Destination ConnectionManager Note: Execute SQL Task can also make use of ADO.Net, ODBC Connection Managers

7. Component Interaction SSIS Connection Manager AcquireConnection() TaskorComponent Connection Object Write() Read() Data Storage

8. Data Flow Connectivity Options Data Flow Task OLEDBConnector ADO.NETConnector CustomConnector OLEDBProvider NativeADO.NETAdapter ADO.NETODBC Data SourceSpecificInterface/Protocol ODBC Data Source Specific Interface/ Protocol Data Storage

9. Data Access APIs and SSIS "When developing in BIDS, 32-bit data providers are always used at design time, if 64-bit providers will be used in production packages, the equivalent 32-bit provider must be available at design time."

10. SSIS Enterprise Data Connectivity

11. Connectivity Categories • Database Systems (RDBMS) • Microsoft SQL Server • Oracle • Sybase • IBM DB2 • Teradata • MS Access • Queue Systems & Protocols • MSMQ (Message Queue Task) • (s)FTP (FTP Task) ** • HTTP/HTTPs (Web Service Task) • IBM MQ Series* • TibcoRendezveus* • Unstructured ,Semi-structured Data • Microsoft Excel, CSV • Text • XML • EDI (Electronic Data Interchange) • Application Systems • Siebel • Microsoft Excel • Hyperion (Essbase, Planning, Financial Management) • SAP R/3 & SAPBI * not supported out-of-the-box, however, custom components can do this. ** sFTP is not supported out-of-the-box

12. Microsoft Connector for SAP BI

13. Microsoft Connector for SAP BI

14. Microsoft Connector for SAP BI Data Flow Task SAP BIConnection Mgr SAP BIData Source SAP BIData Destination Remote Function Calls (RFC’s) * OpenHub BAPI SAP BI *standard programming interface used by SAP

15. Application Scenario 1Extracting data from SAP BI into SQL Server SAP BI SSIS OHSDestination SSIS Package SQL Server SAP BISource OLE dBDestination ProcessChainTriggered Open Hub SAP BIConnectionManager Info Cube

16. demo SAPBI Connector

17. Microsoft Connector for SAP BI Extracting data from SAP BI to SQL Server • SAP BI Configuration • Set up the RFC Destination • Configure and create the Open Hub Destination • Create the Data Transfer Process (DTP) and transformation • Configure the process chain • SSIS Package • Setup the SAP BI Source • Setup the connection manager for SAP BI • Setup the Destination (OLE DB, SQL Destination) • Construct Workflow • Run the SSIS package

18. Application Scenario 2Loading non-SAP data into SAP BI SSIS SAP BI SSIS Package OLE DBSource SAP BIDestination InfoSource/InfoPackage SQLServer XML Staging BAPI SAP BIConnectionManager FlatFile

19. Microsoft Connector for Oracle (by Attunity)

20. Microsoft Connector for Oracle * 32 bit @design time, 64 bit @ runtime)

21. Microsoft Connector for Oracle Data Flow Task OracleConnection Mgr OracleData Source OracleData Destination OCI OCIArray Binding OCIDirect Path OCI Oracle dB

22. Performance of Oracle Connectors

23. Performance of Microsoft Connector for Oracle

24. Microsoft Connector for OracleCustom Properties

25. demo Oracle Connector

26. Microsoft Connector for Teradata (by Attunity)

27. Microsoft Connector for Teradata

28. Microsoft Connector for Teradata Data Flow Task TeradataConnection Mgr TeradataData Source TeradataData Destination TPT12 TPTFastExport TPTTPUMP TPTFastLoad Teradata DW

29. Microsoft Connector for TeradataCustom Properties

30. demo TeraData Connector

31. Recap • SSIS connectivity • Provides a robust connectivity architecture • Provides an extensible connectivity architecture • Provides rich connectivity options to enterprise data via : • Built-in connectors • Additional connectors (available from Microsoft downloads) • *Third party connectors • Rich ecosystem for SSIS connectors *[codeplex or custom dev]

32. question & answer

33. Required Slide Speakers, TechEd 2009 is not producing a DVD. Please announce that attendees can access session recordings from Tech-Ed website. These will only be available after the event. Resources Tech·Ed Africa 2009 sessions will be made available for download the week after the event from: www.tech-ed.co.za • www.microsoft.com/teched International Content & Community • www.microsoft.com/learning • Microsoft Certification & Training Resources • http://microsoft.com/technet • Resources for IT Professionals • http://microsoft.com/msdn Resources for Developers

34. SSIS Community wiki http://wiki.sqlis.com/default.aspx SSIS Codeplex http://sqlsrvintegrationsrv.codeplex.com/ SSIS Junkies : http://blogs.conchango.com/jamiethomson/ Attunity Support Forums : http://www.attunity.com/forums/microsoft-ssis-oracle-connector http://www.attunity.com/forums/micorosft-ssis-teradata-connector Additional Community Resources TechNet Community for IT Professionals http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx Developer Center http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx SQL Server 2008 Learning Portalhttp://www.microsoft.com/learning/sql/2008/default.mspx SSIS Community Resources

35. External Resources SSIS ETL Record – Loaded 1TB in 30 Minutes!http://msdn.microsoft.com/en-us/library/dd537533.aspx Connectivity Options for Microsoft SQL Server 2008 Integration Serviceshttp://msdn.microsoft.com/en-us/library/dd299429.aspx Microsoft Connectors for Oracle and Teradata by Attunityhttp://www.microsoft.com/downloads/details.aspx?FamilyId=D9CB21FE-32E9-4D34-A381-6F9231D84F1E Using SQL Server 2008 Integration Services with SAP BI 7.0 http://msdn.microsoft.com/en-us/library/dd299430.aspx SQL Server 2008 Business Value Calculator: www.moresqlserver.com Additional Resources • Team Forum: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/threads/

36. Required Slide 10 pairs of MP3 sunglasses to be won Complete a session evaluation and enter to win!

37. Required Slide © 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.