{"id":4177,"date":"2016-12-26T17:59:55","date_gmt":"2016-12-26T12:29:55","guid":{"rendered":"https:\/\/www.inogic.com\/blog\/?p=4177"},"modified":"2022-06-13T13:25:56","modified_gmt":"2022-06-13T07:55:56","slug":"steps-to-connect-ssis-to-oracle","status":"publish","type":"post","link":"https:\/\/www.inogic.com\/blog\/2016\/12\/steps-to-connect-ssis-to-oracle\/","title":{"rendered":"Steps To Connect SSIS to Oracle"},"content":{"rendered":"<p><strong>Introduction<\/strong><\/p>\n<p style=\"text-align: justify;\">In SSIS, if you want to migrate the data from Oracle to Microsoft Dynamics CRM we need to connect to the OLA DB source to execute the Oracle queries. This blog will discuss how to connect to the Oracle server.<\/p>\n<p><strong>Prerequisite:<\/strong><\/p>\n<p style=\"text-align: justify;\">Install the software mentioned below on the client machine:<\/p>\n<p style=\"text-align: justify;\"><strong>1) Oracle 11g Database express edition (OracleXE112_Win64) &#8211; <\/strong>We need this because when we install this software, it automatically installs the \u201cOracle Data Provider For .Net\u201d. And this provider is needed for SSIS Tool.<\/p>\n<p style=\"text-align: justify;\"><strong>2) ODAC 32 and 64 bit:<\/strong><\/p>\n<p>\u00a0 \u00a0 \u00a0 \u00a0ODAC 64 bit driver is installed using .exe file.<\/p>\n<p>Install ODAC 32 bit driver using CMD as shown in the screenshot:<\/p>\n<ul>\n<li>Go to ODAC 32 bit folder where the install.bat file is located using CMD.<\/li>\n<li>Type \u201cinstall.bat all c:\/oracle\u201d command and press Enter.<\/li>\n<li>Installation file will be located at \u201cc:\/oracle\u201d folder.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-4178\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/16.jpg\" alt=\"1\" width=\"631\" height=\"324\" \/><strong>3)\u00a0<\/strong><strong>Install Oracle 11g client 32 and 64 bit:<\/strong><\/p>\n<p>After installation of the client, you will find the \u201ctnsnames.ora\u201d file at \u201cF:\\app\\product\\11.2.0\\client_2\\Network\\Admin\\Sample\u201d path<\/p>\n<p><strong>Connection Steps:<\/strong><\/p>\n<p>1. Edit the \u201ctnsnames.ora\u201d file.<\/p>\n<p><strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0Local_name<\/strong> =<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0(<strong>DESCRIPTION<\/strong> =<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0(<strong>ADDRESS<\/strong> = (PROTOCOL = TCP)(HOST = your host Address <em>(e.g. 183.516.6.51)<\/em>)(PORT = 1521))<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0(<strong>CONNECT_DATA<\/strong> =<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (<strong>SERVER<\/strong> = your server address <em>(e.g. 157.20.4.148)<\/em>)<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 (<strong>SERVICE<\/strong>_<strong>NAME<\/strong> = your service name)<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0)<\/p>\n<p style=\"padding-left: 30px;\">\u00a0 \u00a0 )<\/p>\n<p>2. After performing the above steps, you need to add the Environment variable on client machine. Please follow the below steps to add environment variable.<\/p>\n<ul>\n<li>Go to System Setting and click on \u201c<em>Advance system settings<\/em>\u201d.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4179\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/25.jpg\" alt=\"2\" width=\"665\" height=\"185\" \/><\/p>\n<ul>\n<li>Go to <em>Advance<\/em> tab and click on Environment Variables.<\/li>\n<\/ul>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-4180\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/34.jpg\" alt=\"3\" width=\"420\" height=\"470\" \/><\/p>\n<ul>\n<li>Add new system variable and give values as below<strong>:<\/strong><\/li>\n<\/ul>\n<p><strong>Variable<\/strong> <strong>Name <\/strong>&#8211; TNS_ADMIN<\/p>\n<p><strong>Variable<\/strong> <strong>Value <\/strong>&#8211; \u201cfile path where we store the tnsnames.ora\u201d for example We have installed the Oracle 11g client at: \u201cF:\\app\\user\\product\\11.2.0\\client_2\\Network\\Admin\\Sample\u201d path and \u201ctnsnames.ora\u201d file is located at this path.<\/p>\n<p>After adding the Environment variable, restart the machine.<\/p>\n<p>3. After restarting the machine, open the Visual Studio New SSIS project as shown in the below screenshot.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4181\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/42.jpg\" alt=\"4\" width=\"665\" height=\"290\" \/>After creating project, drag the Data Flow Task Control. In the Data flow task control you need to add the \u201c<strong>OLE DB Source<\/strong>\u201d control. Please see the below screenshot.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter size-full wp-image-4182\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/52.jpg\" alt=\"5\" width=\"594\" height=\"274\" \/>Edit the OLE DB source. Click on <strong>New<\/strong> &gt;&gt; Click on New to configure the ODBC connection. Please see the below screenshot.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4183\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/62.jpg\" alt=\"6\" width=\"665\" height=\"518\" \/>You will get the below screen. Select the \u201c<strong>Native OLE DB\\Microsoft OLE DB Provider for Oracle<\/strong>\u201d. Enter the server name, User Name, and Password. Please see the below screenshot.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4184\" src=\"https:\/\/www.inogic.com\/blog\/wp-content\/uploads\/2016\/12\/71.jpg\" alt=\"7\" width=\"665\" height=\"431\" \/>Then click on Test Connection. Now you will able to connect and access the Oracle server.<\/p>\n<p><strong>Conclusion<\/strong><\/p>\n<p>In this way, you can connect to Oracle server to migrate the data from Oracle to Microsoft Dynamics CRM.<\/p>\n<p>&nbsp;<\/p>\n<h2 style=\"text-align: left;\"><div class=\"su-heading su-heading-style-default su-heading-align-center\" id=\"\" style=\"font-size:15px;margin-bottom:5px\"><div class=\"su-heading-inner\">One Pic = 1000 words! Analyze data 90% faster with visualization apps!<\/div><\/div><\/h2>\n<p style=\"text-align: left;\"><em>Get optimum visualization of Dynamics 365 CRM data with &#8211;<\/em><br \/>\n<em><strong><a href=\"https:\/\/bit.ly\/3lYvozZ\" target=\"_blank\" rel=\"noopener noreferrer\">Kanban Board<\/a> <\/strong>\u2013 Visualize Dynamics 365 CRM data in Kanban view by categorizing entity records in lanes and rows as per their status, priority, etc.<\/em><br \/>\n<em><strong><a href=\"https:\/\/bit.ly\/3lCSBaA\" target=\"_blank\" rel=\"noopener noreferrer\">Map My Relationships<\/a><\/strong> \u2013 Map My Relationships \u2013 Visualize connections and relationships between Dynamics 365 CRM entities or related records in a Mind Map view.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction In SSIS, if you want to migrate the data from Oracle to Microsoft Dynamics CRM we need to connect to the OLA DB source to execute the Oracle queries. This blog will discuss how to connect to the Oracle server. Prerequisite: Install the software mentioned below on the client machine: 1) Oracle 11g Database\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.inogic.com\/blog\/2016\/12\/steps-to-connect-ssis-to-oracle\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":13,"featured_media":4185,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[19,57],"tags":[958,1674],"class_list":["post-4177","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-dynamics-crm","category-ssis","tag-integration-services-ssis-connections","tag-steps-to-connect-ssis-to-oracle"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/4177","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/comments?post=4177"}],"version-history":[{"count":0,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/posts\/4177\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media\/4185"}],"wp:attachment":[{"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/media?parent=4177"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/categories?post=4177"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.inogic.com\/blog\/wp-json\/wp\/v2\/tags?post=4177"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}