Connecting to ORACLE database from SSIS

Issue: I have to connect to ORACLE database from my SSIS tool.

Solution: Download and install

32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio 

Using the following link:http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html

Then install the driver and it ask for data base details before the final step.So provide the DB Name(Just a name),Host name,Port,Service name

After competing installation restart your machine.

Then open your SSIS and select OLEDB Source and create a new connection.

From the Provider select Oracle Provider for OLEDB.

Then it will ask for the server or file name, give the name of your DB that you provided during installing the client.(Entry in your tnsnames.ora) Then give username and password and test the connection.


You can see connection successful and you can connect to the database and select the tables. 

Issue while Converting String to Int data type in SSIS Source Excel.

Issue: When converting String data type to Int using data conversion in SSIS, The transformation failing with conversion error.Source used is Excel

Cause: Excel source file contain Nulls and empty values. So because of empty string we are not able convert the values.

Solution:Add a derived column transformation and add the following expression for the derived column.

Expression: ISNULL([COLUMN_NAME]) || TRIM([COLUMN_NAME]) == "" ? (DT_I4)0 : (DT_I4)[COLUMN_NAME]

Then use this derived column in the output.

So if there is empty string or null values that will be replaced with O else with the column name.

Thank you.