Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS 2012 Problem with Oracle when run at Server via SSISDB Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 8:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:24 AM
Points: 24, Visits: 127
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.
Post #1584137
Posted Thursday, June 19, 2014 8:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:24 AM
Points: 24, Visits: 127
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.
Post #1584139
Posted Saturday, June 21, 2014 6:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:08 PM
Points: 1,386, Visits: 6,243
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?
Post #1584578
Posted Sunday, June 22, 2014 7:44 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 23, 2014 2:24 AM
Points: 24, Visits: 127
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.

Post #1584890
Posted Monday, June 23, 2014 1:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:20 PM
Points: 13,252, Visits: 10,133
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1584923
Posted Monday, June 23, 2014 12:07 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 11:12 AM
Points: 607, Visits: 2,110
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.
Post #1585215
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse