{"id":5621,"date":"2013-04-01T11:30:36","date_gmt":"2013-04-01T00:30:36","guid":{"rendered":"http:\/\/mingersoft.com\/blog\/?p=5621"},"modified":"2013-03-29T21:47:39","modified_gmt":"2013-03-29T10:47:39","slug":"importing-data-from-oracle-to-ms-sql-server-databases-via-ssis","status":"publish","type":"post","link":"https:\/\/mingersoft.com\/blog\/2013\/04\/importing-data-from-oracle-to-ms-sql-server-databases-via-ssis\/","title":{"rendered":"Importing Data From Oracle to MS SQL Server Databases Via SSIS"},"content":{"rendered":"

If you’ve been following the blog then you might remember that I have\u00a0had a couple of failed attempts (first article<\/a>\u00a0and second article<\/a>) at getting connectivity going between an Oracle database as a source and a Microsoft SQL Server database as a destination.<\/p>\n

Not to be\u00a0dissuaded\u00a0from finding a solution I have found something that I have verified as working at least from a 32-bit perspective. I will try and test this on 64-bit versions of\u00a0Windows Server 2008 R2 and MS SQL Server 2008 R2 and report back on how things go.<\/p>\n

In a nutshell, you need to install the Oracle Data Access Components (or ODAC for short) – you’re most interested in the Oracle Provider for OLE DB. I used an older server running Windows Server 2003 R2 with MS SQL Server 2005 and installed\u00a0ODAC 11.2 Release 5 (11.2.0.3.20) with Oracle Developer Tools for Visual Studio<\/a>. There are a stack of other versions there but I just chose that version as it was the most recent one available.<\/p>\n

Installation should be fairly straight forward as follows:<\/p>\n

    \n
  1. Install the ODAC suite,<\/span><\/li>\n
  2. Update the tnsnames.ora file in the location containing the Oracle Instant Client with the connection details for your desired Oracle databases,\n
      \n
    1. Typically <target>\\[username]\\product\\[version number]\\client\\network\\admin<\/li>\n<\/ol>\n<\/li>\n
    2. In your desired SSIS package, create a new database connection and use Oracle Provider for OLE DB<\/strong>,<\/li>\n
    3. Specify the server using its SID and enter the corresponding username and password,<\/li>\n
    4. Tick\u00a0Allow saving password<\/strong>,<\/li>\n
    5. Click Test Connection<\/strong>\u00a0to verify everything is correctly,<\/li>\n
    6. Click\u00a0OK<\/strong> if everything checks out.<\/li>\n<\/ol>\n

      Now, you may run into a couple of issues along the way. The first one will be an error message complaining about a component not being registered correctly. One such form of the error message can look like the following:<\/p>\n

      Cannot create and instance of OLE DB provider “OraOLEDB.Oracle”<\/p><\/blockquote>\n

      This is usually caused by one of the DLL files failing to register during initial installation. The way to fix this via an administrative command line is as follows:<\/p>\n

      regsvr32.exe\u00a0<target>\\[username]\\product\\[version number]\\client\\bin\\OraOLEDB11.dll<\/p><\/blockquote>\n

      You may need to change the DLL filename if you install an older or newer version of ODAC (so OraOLEDB10.dll for Oracle 10g for example).<\/p>\n

      The other issue you might encounter is having the password for the Oracle database successfully retained in your SSIS package. This can be rather frustrating as there doesn’t seem to be an apparent reason why this occurs.<\/p>\n

      The default protection level used by Visual Studio for SSIS packages is\u00a0EncryptSensitiveWithUserKey<\/strong> however this doesn’t seem to do the trick. Instead, you need to use\u00a0EncryptSensitiveWithPassword<\/strong> and provide a password for encryption both for saving on the local filesystem and again when exporting to an SSIS repository (if you use that). You’ll also need to provide it again when adding the SSIS package to a schedule to view its options prior to locking it in.<\/p>\n","protected":false},"excerpt":{"rendered":"

      If you’ve been following the blog then you might remember that I have\u00a0had a couple of failed attempts (first article\u00a0and second article) at getting connectivity going between an Oracle database as a source and a Microsoft SQL Server database as a destination. Not to be\u00a0dissuaded\u00a0from finding a solution I have found something that I have … <\/p>\n

      Continue reading<\/a><\/p>\n","protected":false},"author":1,"featured_media":4214,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[152,151,18],"tags":[401,400,783,2246,2037,1353,1919,1967,2280],"class_list":["post-5621","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-microsoft-operating-systems-technology","category-operating-systems","category-technology","tag-32-bit","tag-64-bit","tag-database","tag-microsoft","tag-odbc","tag-oracle","tag-sql-server","tag-ssis","tag-windows-server-2008-r2","item-wrap"],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/mingersoft.com\/blog\/wp-content\/uploads\/2012\/05\/SQL-Server-Logo.png?fit=247%2C267&ssl=1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts\/5621","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/comments?post=5621"}],"version-history":[{"count":0,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/posts\/5621\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/media\/4214"}],"wp:attachment":[{"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/media?parent=5621"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/categories?post=5621"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mingersoft.com\/blog\/wp-json\/wp\/v2\/tags?post=5621"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}