locked
ADF Copy Activity Fails from SQL on premise to Azure Database for PostgreSQL RRS feed

  • Question

  • Hi, 

    I'm trying to bulk copy a table from SQL on premise database to Azure Database for PostgreSQL using ADF Copy activity. 

    All the Connections are done and I can make the mapping b/w source and sink, but I have failure on Debug/trigger: 

    Please help: 

    { "errorCode": "2200", "message": "'Type=System.TypeInitializationException,Message=The type initializer for 'Npgsql.PoolManager' threw an exception.,Source=Npgsql,''Type=System.IO.FileNotFoundException,Message=Could not load file or assembly 'System.ValueTuple, Version=4.0.2.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified.,Source=Npgsql,'", "failureType": "UserError", "target": "ProviderType", "details": [] }

    Wednesday, January 15, 2020 1:25 PM

Answers

  • Hi All,

    After further investigation based on a support request related to this issue, below are the findings.

    Cause:
    When establishing a linked service connection to a Postgre for Azure source, ADF goes through a list of folder paths to find the System.ValueTuple.dll file.  First it looks in the  "C:\Windows\Microsoft.NET\assembly\" filepath then proceeds to 
    "C:\Program Files\Microsoft Integration Runtime".  Currently the System.ValueTuple.dll file is located in the C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\DataScan folder so ADF was unable to locate it.

    Due to the difference in Windows.Net version folder path, ADF was looking for the System.ValueTuple.dll file in the incorrect folder.  While the folder path build was C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.ValueTuple\v4.0_4.0.0.0__cc7b13ffcd2ddd51, ADF was looking in the C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.ValueTuple\v4.0_4.0.2.0__cc7b13ffcd2ddd51 path.


    Resolution
    By adding the "System.ValueTuple.dll" file from the path "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\DataScan" to "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway" which resolved our issue. 

    In addition, our engineering team will be looking into making product changes to the self-hosted integration runtime to include the System.ValueTuple.dll file in the "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\" folder to prevent future occurrences.

    Apologies for the inconvenience.  


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, March 26, 2020 9:16 PM

All replies

  • Hi Hris_2013,

    Thanks for your query. To better assist on your query, could you please share below details?

    1. Activity run ID?
    2. Pipeline run ID?



    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, January 15, 2020 10:47 PM
  • Hi, here it is: 

    Pipeline Run ID - 09842dec-b4be-4918-b406-570d6a08ec1d

    Activity Run - f486f516-db70-40ca-8a76-3132665cfff2

    Thank you! 

    Thursday, January 16, 2020 8:06 AM
  • Hi Hris_2013,

    Thanks for the details, we will look into it. Could you also please let us know what is Integration runtime used?

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, January 16, 2020 8:12 AM
  • For the sink /postgre/ - AutoResolveIntegrationRuntime 

    For Source /SQL on prem/ integrationRuntime1

    Thursday, January 16, 2020 8:26 AM
  • Thanks for the details Hris_2013. Will keep you posted once I have an update on your issue.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, January 16, 2020 8:31 AM
  • Hi Hris_2013

    Will it be possible for you to use same IR on both source and sink. If so, could you please try using Self Hosted Integration Runtime on source and sink? The reason is, there was an upgrade happened on Azure IR recently and assuming this error has triggered because of that.

    Please let us know if you still experience the issue after changing the IR to SHIR. 

     

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, January 16, 2020 9:35 PM
  • Thank you KranthiPakala-MSFT,

    I'll request the client to create it  and will let you know.

    For now, my workaround is to use 'staging' in the copy activity, but this is not the best solution for our client - the Storage account was not in the plan. 

    Regards, 

    Hristina 

    Friday, January 17, 2020 8:07 AM
  • Hi again KranthiPakala-MSFT, 

    one more question - how to install Self Hosted Integration Runtime on Azure Database for PostgreSQL? It is not on a VM it is an Azure Service and  I'm not aware is this  possible and how to do it. 

    Thank you!



    Friday, January 17, 2020 8:33 AM
  • Hi Hris_2013,

    Sorry for the late response. You could try using the same SHIR that you have used on the source side.

    Let us know how it goes.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Tuesday, January 21, 2020 9:53 PM
  • Hi Hi Hris_2013,

    Following up to see if you have got a chance to try the suggestion mentioned in my previous comment.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, January 22, 2020 10:57 PM
  • Hi Hris_2013,

    Just checking to see if your issue got resolved? If you have found a different solution, would you please share it here with the community, as it would be helpful for others. In case if you still need assistance do let us know.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, January 23, 2020 8:31 PM
  • Hi, 

    my solution is to use 'staging' in the copy activity using the the Storage account. 

    I haven't chance to try your solution yet, because I'm off from the project/i have competed my part/ and have no access to the DB. Once  I'm back in it - i'll try and will let you know. 

    Thank you!

    Hristina 

    Friday, January 24, 2020 7:57 AM
  • Thanks for your response Hristina. Please keep us posted once you are done with your findings. 

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, January 24, 2020 7:39 PM
  • I am facing the same issue when creating the linked service connection in ADF. am using self hoster IR to connect

    Error:

    Connection failed
    The type initializer for 'Npgsql.PoolManager' threw an exception. Could not load file or assembly 'System.ValueTuple, Version=4.0.2.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified. Activity ID: e618f17e-bef5-4440-a14a-d6b5ef9650de.

    Tuesday, March 3, 2020 7:14 PM
  • Hi CHETHAN LINGAIAH,

    Thanks for reaching out. For deeper investigation, could you please email us at AzCommunity@microsoft.com with below details.

    Subject of the email: <ATTN-Kranthi : MSDN Thread title>
    Thread URL: <MSDN Thread>
    Subscription ID:  <your subscription id>
    What is your Data Factory Region?
    What is your source &  sink?
    What is the integration runtime used in both Source and Sink? If it is SHIR - what is the version?

    Let us know here once the email is sent.


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered"Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.




    Wednesday, March 4, 2020 12:52 AM
  • I have replied.

    Was able to connect to other VM's. Issue is only with one VM where the IR version is same of others

    Wednesday, March 4, 2020 5:08 PM
  • Thanks much for sharing the details. We will look into it and get back to you once we have our findings ready. 



    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Wednesday, March 4, 2020 8:53 PM
  • Hi CHETHAN LINGAIAH,

    A support engineer is working with internal team to figure out the root cause. I will keep you posted once I have a further update on this issue.

    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Saturday, March 7, 2020 1:41 AM
  • I have the same issue:

    I am trying to copy from a selfhosted Integration Runtime from an on-premises postgres server to an Azure Database for PostgreSQL server instance. I am using a copy data activity.

    I am getting the error:

    {
        "errorCode": "2200",
        "message": "'Type=System.TypeInitializationException,Message=The type initializer for 'Npgsql.PoolManager' threw an exception.,Source=Npgsql,''Type=System.IO.FileNotFoundException,Message=Could not load file or assembly 'System.ValueTuple, Version=4.0.2.0, Culture=neutral, PublicKeyToken=cc7b13ffcd2ddd51' or one of its dependencies. The system cannot find the file specified.,Source=Npgsql,'",
        "failureType": "UserError",
        "target": "Copy table",
        "details": []
    }

    Pipeline Run ID - ef243d50-5cf7-4d3c-b51d-ff83b1318928

    Activity Run - 210abbb8-7e39-4620-984e-b41f107d4434

    I have successfully used the "Enable staging" option to get this to work anyway, but should it be necessary?

    Edit:

    I am using the On-prem integration runtime for the source linked service, and the "AutoResolveIntegrationRuntime" for the sink linked service. Is this correct?

    • Edited by mabah_mst Tuesday, March 17, 2020 11:52 AM Added details about runtime selection
    Tuesday, March 17, 2020 11:47 AM
  • Hi mabah_mst,

    Thanks for sharing the details. For deeper investigation on this issue, could you please email below details to AzCommunity@microsoft.com. 


    Subject of the email: <ATTN-Kranthi : MSDN Thread title>
    Thread URL: <MSDN Thread>
    Subscription ID:  <your subscription id>

    Please let us know here once you have done the same. 


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Friday, March 20, 2020 5:01 AM
  • Hi All,

    After further investigation based on a support request related to this issue, below are the findings.

    Cause:
    When establishing a linked service connection to a Postgre for Azure source, ADF goes through a list of folder paths to find the System.ValueTuple.dll file.  First it looks in the  "C:\Windows\Microsoft.NET\assembly\" filepath then proceeds to 
    "C:\Program Files\Microsoft Integration Runtime".  Currently the System.ValueTuple.dll file is located in the C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\DataScan folder so ADF was unable to locate it.

    Due to the difference in Windows.Net version folder path, ADF was looking for the System.ValueTuple.dll file in the incorrect folder.  While the folder path build was C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.ValueTuple\v4.0_4.0.0.0__cc7b13ffcd2ddd51, ADF was looking in the C:\Windows\Microsoft.NET\assembly\GAC_MSIL\System.ValueTuple\v4.0_4.0.2.0__cc7b13ffcd2ddd51 path.


    Resolution
    By adding the "System.ValueTuple.dll" file from the path "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\DataScan" to "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway" which resolved our issue. 

    In addition, our engineering team will be looking into making product changes to the self-hosted integration runtime to include the System.ValueTuple.dll file in the "C:\Program Files\Microsoft Integration Runtime\4.0\Gateway\" folder to prevent future occurrences.

    Apologies for the inconvenience.  


    Thank you

    If a post helps to resolve your issue, please click the "Mark as Answer" of that post and/or click Answered "Vote as helpful" button of that post. By marking a post as Answered and/or Helpful, you help others find the answer faster.

    Thursday, March 26, 2020 9:16 PM