none
SSIS Execute SQL Task using ADO.NET connection type to a MySQL Database

    Dotaz

  • In SSIS, I am using an Execute SQL Task using an ADO.NET connection to a MySQL Database.

    My SQLStatement is:

    Select Table_Name from INFORMATION_SCHEMA.TABLES where Table_Name like 'report_interval_2018_01_03_%';

    But I am getting this error. Is there a problem with my syntax?

    Error: 0xC002F210 at Query Tables in Source that Look Like "report_interval_", Execute SQL Task: Executing the query "Select Table_Name from INFORMATION_SCHEMA.TABLES w..." failed with the following error: "Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Object reference not set to an instance of an object.'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

    pondělí 26. března 2018 21:46

Odpovědi

  • Hi blueboyz79,

    I used .NET Provider for SqlClient Data Provider. 

    As I mentioned, this issue is related to MySQL .NET Driver.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď blueboyz79 středa 28. března 2018 21:18
    středa 28. března 2018 5:46
    Moderátor

Všechny reakce

  • Hi there,

    Looks like a driver issue.

    Check your setup against https://blogs.msdn.microsoft.com/mattm/2008/03/04/connecting-to-mysql-from-ssis/


    Arthur

    MyBlog


    Twitter

    pondělí 26. března 2018 23:31
    Moderátor
  • Hi blueboyz79,

    I tested your query on my environment and it worked fine.

    So, please make sure you have installed the ADO.NET provider properly, as well as MySQL Client on your machine. In my scenario, I download it from:

    Download Connector/Net
    Restart SSDT and choose .NET Providers\MySQL Data Provider.

    Regards,

    Pirlo Zhang


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    úterý 27. března 2018 2:28
    Moderátor
  • So I reinstalled MySQL ADO.Net Driver, Yes, it works if the ResultSet is set to None.

    But, I need the query to load into a ResultSet, the ResultSet loads to a variable name User::var_obj

    the variable name User::var_obj is then mapped to an Object  Data Type

    So in the General Tab -> Result Set - Full Result Set

    Parameter Mapping Tab -> Variable Name - User::var_obj, Direction - Input, Data Type - Object, Parameter Name - 0, Parameter Size - 255

    Result Set Tab -> Result Name - 0, Variable Name - User::var_obj

    Now this setup works perfectly on an OLEDB connection to MS SQL Server, but not on a ADO.NET connection to MySQL.

    středa 28. března 2018 2:14
  • Hi blueboyz,

    Yes, I can reproduce this issue.

    -->>Failed to create an IDataAdapter object. This provider may not be fully supported with the Execute SQL Task. Error message 'Object reference not set to an instance of an object.'.". 

    I tested other ADO.NET Driver and it worked fine. So this issue is related to the MySQL Driver itself.

    Perhaps the drive is not supported to use Fully Result Set or Single Row. I would suggest you use OLE DB/ODBC as a workaround and connect to MySQL supporters for more professional assistance.

    Thanks for your understanding and support.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    středa 28. března 2018 2:58
    Moderátor
  • Hi Pirlo,

    So you mentioned "I tested other ADO.NET Driver and it worked fine."

    What was the other ADO.NET Driver?

    středa 28. března 2018 5:40
  • Hi blueboyz79,

    I used .NET Provider for SqlClient Data Provider. 

    As I mentioned, this issue is related to MySQL .NET Driver.

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Označen jako odpověď blueboyz79 středa 28. března 2018 21:18
    středa 28. března 2018 5:46
    Moderátor
  • Hi,

    These errors are version specific. My experience is that version 6.9.X of MySQL .NET Driver is working in my environment (SQL2016 & MariaDB 10.1

    I tried 6.10.X & 8.0.X and both return the "Failed to create an IDataAdapter object"

    Regards

    David Sonnet

    • Navržen jako odpověď Sonnet David sobota 14. července 2018 5:06
    sobota 14. července 2018 5:05