The right combination: SSIS, Oracle and Microsoft OLE DB Provider for Oracle

It's been a while since I posted anything. I have been busy with so many things in my life. Work and family.

I would like to take note of this issue with SSIS. It took me a long time to figure this out. Thanks to this post. It gave me a clue on what's going on. In SSIS, it's very typical that when you are working on your development box, everything works fine, but the moment you deploy it to another machine, it doesn't run. Adding to complexities is when you try to schedule it via SQL Server Agent. I will probably discuss this complexity on another post, but for now, I would like to take note of the right combination when dealing with Oracle.

When I configure a new OLE DB Connection Manager, there are 2 different providers that I can choose from:

1. "Oracle Provider for OLE DB"
2. "Microsoft OLE DB Provider for Oracle".

At least in my case, one could have been probably installed when I installed Oracle client, the other one is a MS default. I think what happened initially is I assumed that either one of them should work ok, and in fact it worked ok in my dev. But deploying to the staging server, causes this error:

"SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred "Microsoft OLE DB Service Components".

The reason is because I chose "Oracle Provider for OLE DB". So eventhough the Oracle installed in my dev box is exactly the same as in the staging server, it's still possible that there is a difference. But anyway, what solved my problem? I created another OLE DB Connection Manager using the other one, "Microsoft OLE DB Provider for Oracle", issue fixed!

What gives? Anybody has an idea what the difference between the two?