SSIS 2012 Problem with Oracle when run at Server via SSISDB

Question

SSIS 2012 Problem with Oracle when run at Server via SSISDB

waralees

SSC Eights!

Points: 862
More actions
June 19, 2014 at 8:29 pm

#309316

Dear all,
I do SSIS to get data from oracle to MSSQL by using Oracle provider for OLE DB follow step from "http://sqlblog.com/blogs/jorg_klein/archive/2011/06/09/ssis-connect-to-oracle-on-a-64-bit-machine.aspx". My Test scenario are
1. my machine it work fine.
2. On server by using bat file to run ssis > it work
3. On server by using SSMS (at section Integration Service Catalogs) right click package to validate in 32 bit >> Error AcquireConnection
4. On server by using SSMS (at section Integration Service Catalogs) right click package to validate in 64 bit >> Error Convert unicode to string
Anyone can help me to solve this problem??
Thanks in advance.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

waralees SSC Eights! Points: 862 More actions · Answer 1

I really confuse that Why I user bat file to run SSIS package (force to run in 32 bit) it's work. But when I use SSMS to run it doesn't work. Please help me to solve that.

Jo Pattyn SSC-Dedicated Points: 32348 More actions · Answer 2

What is exactly the problem? The acquire connection or the unicode conversion?

Have you tested the oracle connection from the server using "sqlplus" for both the clients?

waralees SSC Eights! Points: 862 More actions · Answer 3

Dear Jo Pattyn.

At the same package, same server, the Problem is depend on run-time mode. For 64 bit run-time mode, then the problem is unicode conversion. For 32 bit run-time mode,then the problem is acquire connection.

I have test connection via sqlplus already, It's work.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

waralees (6/22/2014)
Dear Jo Pattyn.
At the same package, same server, the Problem is depend on run-time mode. For 64 bit run-time mode, then the problem is unicode conversion. For 32 bit run-time mode,then the problem is acquire connection.
I have test connection via sqlplus already, It's work.

The AcquireConnection problem is probably - and there's no way in knowing for sure since you didn't post any actual error message (unless they are in the screenshots which are blocked here by the firewall - caused by a 64-bit OLE DB provider, which cannot run in 32-bit.

The Unicode problem is pretty straight forward: Oracle stores the data in Unicode, and you probably write it to a non-Unicode column in SQL Server.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Nevyn SSChampion Points: 14210 More actions · Answer 5

Is the batch file executing it from the catalog, or from the file system?

If the problem is only with the catalog, did you configure the package/project correctly? Protection levels are stripped and sensitive data removed when you deploy to the catalog. The first time you deploy a project, you need to make sure that the connections are configured correctly in the catalog.