Tuesday, 21 June 2016

Connecting different DBMS (MySQL, Postgres, Oracle) to Microsoft SSIS

Lately found that people are facing issues with connecting there databases to Microsoft SSIS who has only good support to its native SQL Server

As a matter of fact, even though your machine is 64 bit, SSIS packages will not support the same architecture because of the inbuilt drivers (talking about SQL Server Data Tools 2013).

In case you have tried to install all kinds of drivers 64 bit for your platforms or may be used another 3rd party tools like DevArt,  I will suggest you to remove all  the drivers right away and lets start from fresh. It wont take more than 10-15 minutes to set up

Connecting to MySQL in SSIS 

Download 32 bit MYSQL ODBC connector: http://dev.mysql.com/downloads/connector/odbc/5.1.html#win32 
Note: 64 bit gives architecture mismatch issues 
After installation, you can drag a ODBC Destination component and create a new connection and select “Use Connection String :” 

DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=databasename; 
Provide User and Password details and Test the connection 

Connecting to Oracle in SSIS 
You need to download a copy of "ODTwithODAC1120320_32bit.exe" which can be found at: 

After installation, you can drag a ADO NET Destination component 
You can then choose the provider ".Net Providers\OracleCLient Data Provider". 
Provide user name and password and Test the connection 

Alternative, you can also try ODBC connection using following String 
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe))) 


Connecting to PostgreSQL in SSIS 
  1. Download the latest .msi file for Windows from this location:http://www.postgresql.org/ftp/odbc/versions/msi/ 
  1. Run the msi file on your SQL Server. 
  1. Launch the ODBC Administrator Utility and choose the type of data source you need; File, System or User and click Add. 
  1. The ODBC Administrator will present a list of drivers. Scroll to the bottom and you will see two options for PostgreSQL; ANSI and Unicode. Select the ANSI version. 
  1. The Administrator will present a screen on which you must supply a database name, server name, user name and password. Test the connection and Save it with a proper name 
  1. Drag an ODBC component and select the name created in the 5th step from the drop down menu of User/System defined component 
  1. Test the connection 


No comments:

Post a Comment