Moving Data from Oracle to SQL with Biml
Moving Data from Oracle to SQL with Biml SQL Saturday #604, 4/8/2017 Arthur Wang 1
Topics • What is Biml? • Sourcing from Oracle • Initial setup • Biml. Express vs. Mist vs. BIDS Helper • Walkthrough/demo • Recap • Links 2
What is Biml? • XML dialect • Generates SSIS XML • Much cleaner than SSIS • Lets you embed C# 3
Sourcing from Oracle • OLE DB providers exist but are deprecated • General recommendation: use Attunity drivers • Some work is required to convert data types 4
Initial setup • Install Oracle Data Access Components (ODAC) or Oracle Client • Perform setup on ETL hosts • Perform setup on dev machine • Match bitness • Install Attunity connector for Oracle • Match to your version of SQL Server and Oracle • Install Biml compiler 5
Biml. Express vs. Mist vs. BIDS Helper • Mist: official IDE for Biml created by Varigence • Reverse-engineers packages • Can generate multiple SSIS projects • Handles all elements in Biml schema • BIDS Helper has a version of the Biml engine • Add-in for VS 2015 doesn’t support Biml • Biml. Express: official free add-in for Visual Studio 6
Walkthrough/demo 7
Recap 8
Connecting to Oracle • Use Oracle. Connection element • Connection string: “Data Source=[service]; User Id=[user ID]” • Use an Easy Connect string for the service • Use expressions for project connection manager passwords 9
Generating Oracle Source components • Use Custom. Component syntax • Copy most attributes and custom properties from. dtsx • Need to declare all columns and paths • Need to map data types from Oracle to Biml 10
Handling multiple packages • Project connection managers get regenerated with new GUIDs • Workaround: manually specify GUIDs, and don’t delete/overwrite connection managers • Use the include directive to encapsulate 11
Using metadata to generate packages • Use Oracle Data Provider for. NET (ODP. NET) to get metadata from Oracle • Pass in restrictions to limit amount of metadata returned • Retrieve metadata to iterate through schemas, tables, columns, primary keys, etc. 12
Handle Oracle data types • ODP. NET returns the Oracle data type • Write a custom function to convert Oracle to Biml data types • Use the <#+ delimiter to define functions and classes • Some additional data conversion may be required (e. g. through Data Conversion transformation) 13
Links • Initial setup: https: //technet. microsoft. com/en-us/library/ee 470675(v=sql. 100). aspx • Oracle Data Access Components (ODAC): 32 -bit and 64 -bit • Installing ODAC: https: //www. dba-resources. com/oracle-data-access-components-odac/ • Oracle Client, Database, and Example files: http: //www. oracle. com/technetwork/database/enterprise-edition/downloads/database 12 cwin 64 -download-2297732. html • Attunity Connector: 2. 0, 3. 0, 4. 0 • Biml. Express: https: //www. varigence. com/Biml. Express • Biml language reference: https: //www. varigence. com/Documentation/Language/Index • Oracle schema collections: https: //msdn. microsoft. com/enus/library/cc 716723(v=vs. 110). aspx 14
Credits • Cathrine Wilhelmsen: SQL Server, SSIS and Biml Data Types • Adeeb Mass'ad: Mapping Oracle NUMBER Data Types to SSIS Data Types • Roelant Vos: making SSIS Project Connections generate correctly using BIML Express 15
About Talavant There is a better way to make data work for companies. Better resources, strategy, sustainability, inclusion of the organization as a whole, understanding of client needs, tools, outcomes, better ROI. Better Business Intelligence. Period. STRATEGY ARCHITECTURE HOW WE DO IT VALUE WE PROVIDE • • IMPLEMENTATION Sustainability Dependable, worry-free integration Accelerated planning, implementation and results Increased company-wide buy-in & usage Copyright Talavant, Inc. 2017 By providing a holistic approach inclusive of a client’s people, processes and technologies built on investment in our own employees and company growth. 16
- Slides: 16