%3CLINGO-SUB%20id%3D%22lingo-sub-1385558%22%20slang%3D%22en-US%22%3EGeneral%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1385558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Fintegration-services%2Fdata-flow%2Foracle-connector%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%3CSTRONG%3EMicrosoft%20Connector%20for%20Oracle%3C%2FSTRONG%3E%3C%2FA%3E%20is%20now%20generally%20available.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EMicrosoft%20Connector%20for%20Oracle%20provides%20compatibility%20with%20Attunity%20Oracle%20Connector%20in%20functionality%20and%20performance.%20Packages%20designed%20with%20Attunity%20Oracle%20Connector%20can%20run%20with%20Microsoft%20Connector%20for%20Oracle%20without%20any%20changes.%26nbsp%3B%3C%2FSPAN%3EThe%20following%20Microsoft%20SQL%20Server%20products%20are%20supported%20by%20Microsoft%20Connector%20for%20Oracle%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESince%20SQL%20Server%202019%20CU1%3C%2FLI%3E%0A%3CLI%3ESQL%20Server%20Data%20Tools%20(SSDT)%26nbsp%3B%2015.9.3%20or%20later%20for%20Visual%20Studio%202017%3C%2FLI%3E%0A%3CLI%3ESQL%20Server%20Data%20Tools%20(SSDT)%20for%20Visual%20Studio%202019%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ETo%20install%20the%20connector%20for%20Oracle%20database%2C%20download%20and%20run%20the%20installer%20from%26nbsp%3B%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fdownload%2Fdetails.aspx%3Fid%3D58228%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%20data-linktype%3D%22external%22%3Ethe%20latest%20version%20of%20Microsoft%20connector%20for%20Oracle%3C%2FA%3E%3CSPAN%3E.%20Then%20follow%20the%20directions%20in%20the%20installation%20wizard.%26nbsp%3B%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--815989563%22%20id%3D%22toc-hId--1351665991%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%20id%3D%22toc-hId--1329503476%22%3E%3CFONT%20size%3D%224%22%3EDesign%20new%20SSIS%20packages%3C%2FFONT%3E%3C%2FH2%3E%0A%3CP%3EOpen%20SSDT%2015.9.3%20or%20later%2C%20Oracle%20components%20can%20be%20found%20in%20SSIS%20Toolbox%2C%20under%20Common%20Category.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20201px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191244i8CDFDB0290F08C1C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20in%20Connection%20Managers%2C%20Oracle%20connection%20manager%20is%20displayed%20with%20type%20%E2%80%9C%3CSTRONG%3EORACLE%3C%2FSTRONG%3E%E2%80%9D.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20522px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F191251iFA04681FBFA9A7FB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EMicrosoft%20Connector%20for%20Oracle%20provides%20similar%20user%20experience%20with%20Attunity%20Oracle%20Connector.%20User%20can%20design%20new%20packages%20based%20on%20previous%20experience.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId-1663770976%22%20id%3D%22toc-hId--661104517%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%20id%3D%22toc-hId--638942002%22%3EExecute%20packages%20targeting%20SQL%20Server%202019%3C%2FH3%3E%0A%3CP%3EExisting%20SSIS%20packages%20which%20have%20been%20designed%20with%20Attunity%20Oracle%20Connector%20will%20be%20automatically%20upgraded%20to%20use%20Microsoft%20Connector%20for%20Oracle.%20The%20icons%20will%20be%20changed%20as%20well.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH3%20id%3D%22toc-hId--888385985%22%20id%3D%22toc-hId-1826408316%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%20id%3D%22toc-hId-1848570831%22%3EExecute%20package%20targeting%20Pre-SQL%20Server%202019%3C%2FH3%3E%0A%3CP%3EIf%20the%20project%20is%20targeting%20Pre-SQL%20Server%202019%2C%20corresponding%20Attunity%20Oracle%20Connector%20and%20Oracle%20client%20are%20required%20to%20be%20installed%20on%20the%20SSDT%20machine%20for%20package%20execution.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAttunity%20Oracle%20Connector%20download%20links%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D55179%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Connector%20Version%205.0%20for%20Oracle%20by%20Attunity%20targeting%20SQL%20Server%202017%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D52950%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Connector%20Version%204.0%20for%20Oracle%20by%20Attunity%20targeting%20SQL%20Server%202016%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D44582%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Connector%20Version%203.0%20for%20Oracle%20by%20Attunity%20targeting%20SQL%20Server%202014%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D29283%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMicrosoft%20Connector%20Version%202.0%20for%20Oracle%20by%20Attunity%20targeting%20SQL%20Server%202012%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EP%3C%2FSPAN%3E%3CFONT%20size%3D%223%22%3Elease%20refer%20to%20this%3CSPAN%3E%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fintegration-services%2Fdata-flow%2Foracle-connector%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Epage%3C%2FA%3E%26nbsp%3B%3C%2FSPAN%3Efor%20more%20detail%20usage.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1385558%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EAnnouncing%20general%20availability%20of%26nbsp%3B%3C%2FSPAN%3EMicrosoft%20Connector%20for%20Oracle.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1415361%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1415361%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20about%20Azure%20SQL%20DB%3F%20Or%20Azure%20Synapse%20(formerly%20known%20as...)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416618%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416618%22%20slang%3D%22en-US%22%3E%3CP%3EFor%20Synapse%20you'd%20be%20able%20to%20use%20Polybase%20for%20Oracle%20connection.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416989%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416989%22%20slang%3D%22en-US%22%3E%3CP%3EDoes%20this%20setup%20only%20work%20with%20SSIS%20on%20SQL%20Server%202019%2C%20or%20can%20we%20use%20the%20updated%20SSDT%20with%20the%20Attunity%20connectors%20if%20SSIS%20is%20running%20on%20a%20previous%20version%20(in%20my%20case%20SSIS%20on%20SQL%20Server%202012).%20I%20haven't%20been%20able%20to%20update%20my%20SSDT%20since%20v15.8.0%20because%20of%20the%20Attunity%20connectors.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416997%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416997%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F679405%22%20target%3D%22_blank%22%3E%40EMorgoch%3C%2FA%3E%26nbsp%3B%3CSPAN%3EIf%20the%20project%20is%20targeting%20Pre-SQL%20Server%202019%2C%20works%20for%20Attunity%20connectors%20as%20before%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1416999%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1416999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F676361%22%20target%3D%22_blank%22%3E%40EdPearson%3C%2FA%3E%26nbsp%3Bnothing%20changes%20on%20connectivity%20to%20Azure%20SQL%20DB%20-%20OLEDB%20connection%20manager%20or%20ADO.NET%20connection%20manager%2C%20Synapse%20(previous%20Azure%20SQL%20DW)%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fintegration-services%2Fcontrol-flow%2Fazure-sql-dw-upload-task%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20SQL%20DW%20Upload%20Task%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1423546%22%20slang%3D%22en-US%22%3ERe%3A%20General%20availability%20of%20Microsoft%20Connector%20for%20Oracle%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1423546%22%20slang%3D%22en-US%22%3E%3CP%3EHei%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20upload%20some%20data%20from%20oracle%20to%20SQL%20Server%20using%20this%20connector%3C%2FP%3E%3CP%3Ethe%20varchar2%20sometimes%20is%20being%20mapped%20with%20DT_WSTR%20and%20sometimes%20with%20DT_STR%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ein%20oracle%20side%20we%20are%20trying%20to%20get%20data%20from%20a%20a%20view%20and%20from%20a%20table%3C%2FP%3E%3CP%3Eon%20the%20view%20datatype%20of%20one%20of%20the%20columns%20is%20VARCHAR2(6%20BYTE)%20and%20it%20is%20mapped%20with%20DT_WSTR%20which%20requires%20nvarchar%20on%20sql%20server%3CBR%20%2F%3Eon%20the%20table%20the%20data%20type%20of%20a%20coulmin%20is%20VARCHAR2(6%20BYTE)%20and%20it%20is%20mapped%20with%20DT_STR%20which%20requires%20varchar%20on%20sql%20server%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20same%20connection%20from%20the%20same%20PC%20is%20used%20in%20both%20cases%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eis%20there%20any%20documentation%20on%20how%20this%20mappings%20are%20done%3F%3CBR%20%2F%3Eor%20any%20indication%20on%20what%20should%20we%20look%20at%20to%20decide%20whether%20to%20use%20varchar%20or%20nvarchar%20on%20sql%20server%3C%2FP%3E%3CP%3EI'm%20trying%20to%20automate%20the%20process%20and%20I%20can't%20find%20any%20reason%20why%20one%20or%20the%20other%20datatype%20mapping%20is%20picked%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Microsoft

Microsoft Connector for Oracle is now generally available. 

 

Microsoft Connector for Oracle provides compatibility with Attunity Oracle Connector in functionality and performance. Packages designed with Attunity Oracle Connector can run with Microsoft Connector for Oracle without any changes. The following Microsoft SQL Server products are supported by Microsoft Connector for Oracle:

  • Since SQL Server 2019 CU1
  • SQL Server Data Tools (SSDT)  15.9.3 or later for Visual Studio 2017
  • SQL Server Data Tools (SSDT) for Visual Studio 2019

 

To install the connector for Oracle database, download and run the installer from the latest version of Microsoft connector for Oracle. Then follow the directions in the installation wizard. 

 

Design new SSIS packages

Open SSDT 15.9.3 or later, Oracle components can be found in SSIS Toolbox, under Common Category.

1.png

 

And in Connection Managers, Oracle connection manager is displayed with type “ORACLE”.

2.png

Microsoft Connector for Oracle provides similar user experience with Attunity Oracle Connector. User can design new packages based on previous experience.

 

Execute packages targeting SQL Server 2019

Existing SSIS packages which have been designed with Attunity Oracle Connector will be automatically upgraded to use Microsoft Connector for Oracle. The icons will be changed as well.

 

Execute package targeting Pre-SQL Server 2019

If the project is targeting Pre-SQL Server 2019, corresponding Attunity Oracle Connector and Oracle client are required to be installed on the SSDT machine for package execution.

 

Attunity Oracle Connector download links:

 

Please refer to this page for more detail usage.

6 Comments
Senior Member

What about Azure SQL DB? Or Azure Synapse (formerly known as...)?

Occasional Visitor

For Synapse you'd be able to use Polybase for Oracle connection. 

Occasional Visitor

Does this setup only work with SSIS on SQL Server 2019, or can we use the updated SSDT with the Attunity connectors if SSIS is running on a previous version (in my case SSIS on SQL Server 2012). I haven't been able to update my SSDT since v15.8.0 because of the Attunity connectors.

Microsoft

@EMorgoch If the project is targeting Pre-SQL Server 2019, works for Attunity connectors as before

Microsoft

@EdPearson nothing changes on connectivity to Azure SQL DB - OLEDB connection manager or ADO.NET connection manager, Synapse (previous Azure SQL DW) - Azure SQL DW Upload Task

Senior Member

Hei

 

I'm trying to upload some data from oracle to SQL Server using this connector

the varchar2 sometimes is being mapped with DT_WSTR and sometimes with DT_STR

 

in oracle side we are trying to get data from a a view and from a table

on the view datatype of one of the columns is VARCHAR2(6 BYTE) and it is mapped with DT_WSTR which requires nvarchar on sql server
on the table the data type of a coulmin is VARCHAR2(6 BYTE) and it is mapped with DT_STR which requires varchar on sql server

 

the same connection from the same PC is used in both cases

 

is there any documentation on how this mappings are done?
or any indication on what should we look at to decide whether to use varchar or nvarchar on sql server

I'm trying to automate the process and I can't find any reason why one or the other datatype mapping is picked