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

Question

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

Sneha Nair 6

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?

2 answers

Your answer

Answer 1

KranthiPakala-MSFT 46,637 Microsoft Employee Moderator

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

KranthiPakala-MSFT 46,637 Reputation points Microsoft Employee Moderator

2020-07-06T17:30:53.54+00:00

Hi @Sneha Nair ,

Following up to see if the above info was helpful in resolving your issue? If you have further query, do let us know.

----------

Thank you
Sneha Nair 6 Reputation points

2020-07-07T17:31:40.927+00:00

Hello,

Unfortunately, enabling TLS to 1.2 didn't do anything. I am still getting this error. We have created a simple package to test the data flow from on prem to Azure (no parameters and only ole db source).

We are getting this weird error and the logs don't say much.
KranthiPakala-MSFT 46,637 Reputation points Microsoft Employee Moderator

2020-07-07T17:48:09.24+00:00
Hi @Sneha Nair ,

Thanks for your response. Looks like this needs a deeper analysis. For deeper investigation and immediate assistance, if you have a support plan you may file a support ticket, else could you please send an email to AzCommunity@Microsoft.com with the below details, so that we can enable a one-time-free support for you to work closely on this matter.

Subject of the email: <Attn - Kranthi : Microsoft Q&A Thread title>

Thread URL: <Microsoft Q&A Thread link>

Subscription ID: <your subscription id>

Please let me know once you have done the same.

----------

Thanks
Sneha Nair 6 Reputation points

2020-07-07T17:53:19.913+00:00

Data Flow Task:Error: ConnectByProxy: Process data from proxy to pipeline buffer fail since error: Timeout when reading fromstaging. TaskStatus: Failed, ErrorCode:2906, ErrorMessage: Package execution failed., Output: {
"OperationErrorMessages": "7/7/202012:31:30PM -04:00: {FDB3BB08-97CD-4F8E-97BD-0F3144BA2622}:Error:System.AggregateException: One or more errors occurred. ---> System.FormatException: No valid combination of account informationfound.\r\n at Microsoft.WindowsAzure.Storage.CloudStorageAccount.

Answer 2

Sneha Nair 6

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

KranthiPakala-MSFT 46,637 Reputation points Microsoft Employee Moderator

2020-07-13T20:13:34.52+00:00

Hi @Sneha Nair ,

Apologizes for the delay in response and thanks much for sharing the resolution details here with community, as it would be beneficial for other users who experience similar issue.
Regarding the limitation query, I have reached out to internal team to get a confirmation. I will keep you posted as soon as I have a response from the team.

In the meantime, please feel free to click "Accept answer" on you response, that way it will help others to find the solution easily.

----------

Thank you
Singh, Pritpal (NonCityStaff) 0 Reputation points

2024-01-04T21:52:26.2933333+00:00

Hi Sneha

Where you have "changed it to Auto Resolve"

Thanks

Share via

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

2 answers

Your answer