SSIS Wizard new mapping file not found

Question

Hi

I am attempting to migrate a Sybase ASA database to SQL Server using the 'SQL Import and Export Wizard' within Visual Studio 2019. I am able to run the wizard and create a package but the datatype mappings are not correct, e.g. datetime is mapped to smalldatetime. I have created two new mapping files (NetODBCToSSIS10.xml and NetODBCToMSSQL.xml) but I have been unable to get the wizard to recognize the new files.

I am using the following settings in the wizard:

1. Data source: DSN = Sybase DSN (created using the 'SQL Anywhere 17' ODBC driver)
2. Destination: MS OLE DB Provider for SQL Server
3. Copy data from one or more tables or views
4. All tables are displayed but the datatype mappings are not correct so I am unable to import the data.

The Wizard displays a dialog showing the following:

[Source Information]
Cannot locate the mapping file to map the provider types to SSIS types

[Destination Information]
Destination Location : localhost
Destination Provider : SQLOLEDB
Mapping file (to SSIS type): C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\MappingFiles\MSSQLToSSIS10.XML

[Conversion Table]
SSIS conversion file: C:\Program Files (x86)\Microsoft Visual Studio\2019\Professional\Common7\IDE\CommonExtensions\Microsoft\SSIS\150\binn\DtwTypeConversion.xml

I have tried modifying the 'SourceType' at the start of the mapping files to include 'Sap.Data.SQLAnywhere.SAConnection' and 'System.Data.Odbc.OdbcConnection' but it is not found.

The following asks a similar question but there is no satisfactory response:

https://social.msdn.microsoft.com/Forums/en-US/dfce5ec6-c862-416f-b06d-eb6078e9958b/how-to-add-a-new-mapping-file?forum=sqlintegrationservices

Please let me know what I need to do to get the new mapping files recognized.

Regards,
Richard

Answer 1

Hi @Marshall, Richard J ,

If you edit an existing mapping file, or add a new mapping file to the folder, you have to close and reopen the SQL Server Import and Export Wizard or SQL Server Data Tools (SSDT) to load the new or changed mapping file.

See data-type-mapping-in-the-sql-server-import-and-export-wizard.

Also try to change the Destination Provider to SQL Server Native Client 11.0 to see if it could be helpful.

Here is a same thread you may take a reference.

trying-to-add-odbc-to-ssis11-xml-mapping-file-in-ssms

Regards，

Zoe

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

For those who are still looking for a solution

In the source, you need to specify the file name of the ODBC driver in capital letters. For example, in my case for PostgreSQL was PSQLODBC35W.DLL

<?xml version="1.0" encoding="utf-8" ?> 
<dtm:DataTypeMappings
	xmlns:dtm="http://www.microsoft.com/SqlServer/Dts/DataTypeMapping.xsd" 
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	SourceType="PSQLODBC35W.DLL" 
	MinSourceVersion="*" 
	MaxSourceVersion="*" 
	DestinationType="SSIS Data Flow" 
	MinDestinationVersion="*" 
	MaxDestinationVersion="*">

	<!-- ... -->

</dtm:DataTypeMappings>