Azure Data Factory - Self Hosted IR as a proxy - Timeout when reading from staging.

Sneha Nair 6 Reputation points
2020-07-01T22:53:41.453+00:00

Hello,

We have an ssis package which needs to pull data from an on prem sql server to an Azure SQL DB. We have used the Azure SSIS IR to host the packages on SSIDB which is present on the AZure SQL Server.
Now, we in order to connect to the on prem server, we have used a self hosted ir whoch is installed on a SQL vm and used it as a proxy. We have followed all the steps given in the below link.
https://github.com/MicrosoftDocs/azure-docs/blob/master/articles/data-factory/self-hosted-integration-runtime-proxy-ssis.md

We need to use windows authentication to connect to on prem and have followed the steps given in the link below:https://learn.microsoft.com/sql/integration-services/lift-shift/ssis-azure-connect-with-windows-auth?view=sql-server-ver15

The issue is when we try to run the package from ssms, we get the following error:

Data Flow Task:Error: ConnectByProxy: Process data from proxy to
pipeline buffer fail since error: Timeout when reading from staging.
TaskStatus: Failed, ErrorCode: 2906, ErrorMessage: Package execution failed.

Can someone please help me with this error?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,156 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,442 Reputation points Microsoft Employee
    2020-07-02T21:21:38.703+00:00

    Hi @SnehaNair-4932,

    Welcome to Microsoft Q&A platform and thanks for your query.

    Could you please make sure that TLS 1.2 is enabled on SHIR machine? In few cases, the default protocols on SHIR machine may be TLS1.1 which is not compatible with server end (i.e., Azure Storage) and hence error is thrown. If that is the case please try updating TLS 1.1 to TLS 1.2 on SHIR machine and see if that helps to successfully run the SSIS workflow on SSIS IR.

    Also I would recommend you to please go through below known limitations:

    • Only data flow tasks with Open Database Connectivity (ODBC)/OLEDB/Flat File sources or OLEDB destination are currently supported.
    • Only Azure Blob storage-linked services that are configured with Account key, Shared Access Signature (SAS) URI, or Service Principal authentication are currently supported.
    • ParameterMapping in OLEDB Source is not supported yet. As a workaround, please use SQL Command From Variable as the AccessMode and use Expression to insert your variables/parameters in a SQL command. As an illustration, see the ParameterMappingSample.dtsx package that can be found in the SelfHostedIRProxy/Limitations folder of our public preview container. Using Azure Storage Explorer, you can connect to our public preview container by entering the above SAS URI.

    Hope this helps. Please let us know how it goes.


    Thanks
    Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

    1 person found this answer helpful.

  2. Sneha Nair 6 Reputation points
    2020-07-08T12:01:44.527+00:00

    Hi Kranthi,

    We were able to resolve this issue. We were referencing the Self Hosted IR in the connect via integration runtime dropdown. We went through the documentation again and changed it to Auto Resolve. This has resolved the error.

    One question that I had was we are using Microsoft Oracle Attunity connector in our package. Now, I know that there are limitations on the data sources. Is this supported in Self Hosted IR?

    Regards,
    Sneha

    1 person found this answer helpful.