none
Data Transfer - OLE DB Errors

    Question

  • Hi all,

    I am using Visual Studio 2008 to migrate a remote MySQL database to a remote SQL Server database. I am using a .Net MySQL Data Provider source connection manager and an OLE DB SQL Server Native Client destination connection manager. I am using fast loading for the transfer. While the data flow works perfectly on the Dev destination server, it doesnt work on the UAT destination server. One of the differences between the two is the permissions. I have DBO permissions on Dev and only select, insert, update, delete on UAT. I do not know what permissions exactly is required for the data transfer. The following are the errors I get:

    [mssql - positions [799]] Error: 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 10.0"  Hresult: 0x80004005  Description: "Cannot find the object "EHF.positions" because it does not exist or you do not have permissions.".
    [mssql - positions [799]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "input "OLE DB Destination Input" (812)" failed because error code 0xC020907B occurred, and the error row disposition on "input "OLE DB Destination Input" (812)" specifies failure on error. An error occurred on the specified object of the specified component.  There may be error messages posted before this with more information about the failure.
    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "mssql - positions" (799) failed with error code 0xC0209029 while processing input "OLE DB Destination Input" (812). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.
    [mysql - positions [826]] Error: The component "mysql - positions" (826) was unable to process the data. Exception from HRESULT: 0xC0047020
    [SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.  The PrimeOutput method on component "mysql - positions" (826) returned error code 0xC02090F5.  The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.  There may be error messages posted before this with more information about the failure.

    This problem is occurring for tables with over 10,000 rows.

    Help please.

    • Moved by lucy-liu Wednesday, March 21, 2012 7:43 AM it is a sql server migration issue (From:Visual Studio Extensibility)
    Monday, March 19, 2012 10:13 AM

Answers

  • I have figured out the issue. I was trying to migrate identity columns from MySQL to SQL Server. Although I had the permissions to select, update, insert, delete I did not have the permissions to set identity insert on. The error message below has no mention of identity insert in there.

    Cannot find the object "EHF.positions" because it does not exist or you do not have permissions.

    Note to MS: Please improve quality of error reporting.

    • Marked as answer by rahmanu Friday, March 23, 2012 11:36 AM
    Friday, March 23, 2012 11:35 AM

All replies

  • Still waiting for response.

    Tuesday, March 20, 2012 11:57 AM
  • Hi rahmanu,

    Thank you for your post.

    I will move it to "SQL Sever Migration" forum for a better support.

    Thank you for your understanding!


    Lucy Liu [MSFT]
    MSDN Community Support | Feedback to us

    Wednesday, March 21, 2012 7:42 AM
  • Hi rahmanu,

    The error log you posted, some of the errors are related to MySQL, which is difficult to troubleshoot in this forum.

    For migration from MySQL to SQL Server, I would like to suggest you to use SQL Server Migration Assistant for MySQL v5.2, you can download it from this website:

    http://www.microsoft.com/download/en/details.aspx?id=28764

    And here are some references about SSMA:

    http://msdn.microsoft.com/en-us/library/hh313129.aspx

    http://blogs.technet.com/b/dataplatforminsider/archive/2010/08/12/microsoft-announces-sql-server-migration-assistant-for-mysql.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, March 21, 2012 9:15 AM
    Moderator
  • Hi Iric,

    The errors are all SQL Server / Visual Studio errors and they are ambiguous and do not really point me in any direction. The package works with one SQL Server target and not with another. I think it might be because I do not have the appropriate permissions on the UAT server but I am too much of a newbie to know which permissions are required for a fast-load table to table copy. If you can tell me then I can check whether the DBA would grant me those rights.

    Unfortunately, my company are not willing to pay for the SSMA license. So all I have on the app stack is Visual Studio and SQL Server. If there is another way to run the migration using the tools I have available, please let me know.

    Regards

    Wednesday, March 21, 2012 9:58 AM
  • Thanks Lucy Liu
    Wednesday, March 21, 2012 9:59 AM
  • Hi rahmanu,

    SQL Server Migration Assistant is free. You can download it from the link I provided above.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 22, 2012 1:18 AM
    Moderator
  • Does SSMA require access to either the client machine or the server machine?

    I do not have access to either. Both are accessed remotely.

    Thursday, March 22, 2012 4:19 PM
  • rahmanu,

    Yes, it needs the access to both of the two machine.

    Could you please describe how your two machines connected? And please provide more information about your Dev destination server and UAT destination server.

    Have you tried that grant the same permission to UAT as Dev has?


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, March 23, 2012 1:50 AM
    Moderator
  • I have figured out the issue. I was trying to migrate identity columns from MySQL to SQL Server. Although I had the permissions to select, update, insert, delete I did not have the permissions to set identity insert on. The error message below has no mention of identity insert in there.

    Cannot find the object "EHF.positions" because it does not exist or you do not have permissions.

    Note to MS: Please improve quality of error reporting.

    • Marked as answer by rahmanu Friday, March 23, 2012 11:36 AM
    Friday, March 23, 2012 11:35 AM