question

Sudhapanuganti-9149 avatar image
0 Votes"
Sudhapanuganti-9149 asked HimanshuSinha-MSFT commented

SSIS Package execution from ADF with data in SQL Server on premise

I have a SSIS package deployed to Azure SSIDB, and my SQL Server is on premise.
I have set up Self-hosted SSIS IR and also virtual machine ,
I am able to see and access my server on premise from the linked servers in ADF,
my self-hosted SSIS IR is running fine.
I have created a virtual network with point-to-site connection as well.
and I have used the option of windows authentication in my package and I have specified it in the pipieline as well
.
.owever my pipe line execution gives an error :

{ "SsisDBOperationGuid": "3e3bded4-a5a4-48c3-9996-012f87ec860d", "SsisDBOperationId": 82, "OperationErrorMessages": "8/11/2021 9:13:09 PM +00:00 : Package:Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 11.0\" Hresult: 0x80004005 Description: \"Login timeout expired\".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 11.0\" Hresult: 0x80004005 Description: \"A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.\".\r\nAn OLE DB record is available. Source: \"Microsoft SQL Server Native Client 11.0\" Hresult: 0x80004005 Description: \"Named Pipes Provider: Could not open a connection to SQL Server [53]. \".\r\n\n8/11/2021 9:13:09 PM +00:00 : Data Flow Task:Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager \"LAPTOP-5SE9RHOF.CLAIMS_OCM_DW\" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.\r\n\n8/11/2021 9:13:09 PM +00:00 : Data Flow Task:Error: OLE DB Destination failed validation and returned error code 0xC020801C.\r\n\n", "effectiveIntegrationRuntime": "azuresqlserver-ocm-IR-2021-07-29T03-26-13Z (West US 2)", "executionDuration": 52, "durationInQueue": { "integrationRuntimeQueue": 1 } }


Any help /ssuggestions appreciated

azure-data-factoryazure-virtual-machinessql-server-integration-servicesazure-virtual-machines-networking
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @Sudhapanuganti-9149,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

Hello @Sudhapanuganti-9149,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu

0 Votes 0 ·

1 Answer

HimanshuSinha-MSFT avatar image
0 Votes"
HimanshuSinha-MSFT answered

Hello @Sudhapanuganti-9149 ,
Thanks for the ask and using the Microsoft Q&A platform .
Looking into the roor , i have the below suggestions .

  1. You mentioned that you are trying Windows authetntication , can you please try SQL authentication and give it a try .

  2. The default pot for SQL 1433 , is this white listed on the VNET ?

Please do let me know how it goes .
Thanks
Himanshu
Please do consider clicking on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.