Having problems with Migrating from MSSQL(SSMS) to MYSQL(workbench) for files that have nvarchar max and TEXTIMAGE_ON (unicode)

Splasty 0 Reputation points
2023-05-15T15:41:59.01+00:00

have been recently trying to migrate some of the databases with a massive amount of data from MSSQL to MYSQL. So, I have migrated the schema only using MySQL workbench. Now, to transfer the data, I am exporting the data from MSSQL to a CSV file and then directly importing them into MySQL workbench. The exported data from MSSQL into a CSV file is normally in a utf 8 format. However, some of the tables in the MSSQL database have TEXTIMAGE_ON and nvarchar max. So they cannot be exported unless I click on the Unicode box during exporting the data. This leads to the file being formatted into utf 16 BOM format.

So I have tried multiple ways to solve this. Here are the steps that I tried.
1)I tried directly migrating using the migration wizard in MYSQL workbench but since most of the tables are huge and contain 10000 rows, it is not a viable option
2) I tried using Notepad++ to convert the utf 16 into utf 8 format. Then when I tried importing it into the MYSQL workbench, the process works but in the end, it does not import any of the rows or any data whatsoever
3) I tried using BCP format and also wrote a Python script for utf 16 to utf 8 formats but I get the same problem as in the number 2 solution.
4)I tried using SSIS where I use an OLE DB Source to connect with a script component(script for converting into utf 8 directly) in a data flow section but I get this error. I am using a Windows connection and I tested the connection and everything works. The error is:

SIS package "C:\Database Migration\Data Extraction 2\Integration Services Project1\Integration Services Project1\Package.dtsx" starting.

Information: 0x4004300A at Data Flow Task, SSIS.Pipeline: Validation phase is beginning.

Warning: 0x8020F45F at Package, Connection manager "LAPTOP-KLRPQCGK.Database": SSIS Error Code DTS_W_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "OleDbConnectionManager" failed with error code 0x80004005. Wait 5 seconds to retry.

Error: 0xC0202009 at Package, Connection manager "LAPTOP-KLRPQCGK.Database": SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login timeout expired".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. The server is not found or not accessible. Check if the instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "TCP Provider: No connection could be made because the target machine actively refused it.

".

Error: 0xC020801C at Data Flow Task, OLE DB Source [2]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "LAPTOP-KLRPQCGK.Database" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.

Error: 0xC0047017 at Data Flow Task, SSIS.Pipeline: OLE DB Source failed validation and returned error code 0xC020801C.

Error: 0xC004700C at Data Flow Task, SSIS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "C:\Database Migration\Data Extraction 2\Integration Services Project1\Integration Services Project1\Package.dtsx" finished: Failure.

The program '[32992] DtsDebugHost.exe: DTS' has exited with code 0 (0x0).

Any solutions on what should I Do next?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,070 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,487 questions
{count} votes