0

I am trying to make SSIS packages using Visual Studio 19 that import data from tables in a remote Azure Managed Instance SQL database to a Microsoft Azure SQL DB database, aiming to schedule these to run weekly on Azure Data Factory. I am fairly new to both SSIS and to Microsoft Azure, but there is no-one else in the company with more expertise with either that I could consult for advice.

I have made a package consisting of an SQL script to truncate some tables, ADO NET sources for the source database (since it uses Active Directory authentication), lookups to prevent duplication of destination data (where I'm not truncating first) and OLE DB destinations for the destination tables.

The package runs fine (if somewhat slowly) in Visual Studio on my PC. However when I deploy it via SSMS I get the following validation warning messages:

Package: "Data_Import": Connection "ConnectionManager1" contains host name.

Package: "Data_Import": Connection "ConnectionManager2" contains host name.

Package: "Data_Import": Connection "ConnectionManager3" contains host name.

And when I attempt to execute the package it fails with the messages:

Import New Data:Error: Campaign Source failed validation and returned error code 0xC0208449.

and:

Import New Data:Error: ADO NET Source has failed to acquire the connection {2FA367CE-3495-40B5-926D-F8A08AC5A3DA} with the following error message: "A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)".

("Import New Data" is one of the data flow tasks and "Campaign Source" is an ADO NET source for the "Campaign" table).

I suspect this is because the connection managers contain the host name as advised by the warning before deploying, as this page suggests that packages featuring the host name instead of the IP address may fail in Azure (but it does not explain how to change the package so it uses IP addresses instead of names): https://docs.microsoft.com/en-us/sql/integration-services/lift-shift/ssis-azure-validate-packages?view=sql-server-2017

What does this mean and how can I change the connection manager properties so they don't contain the host name, so that the packages will run in Azure?

Alternatively, if this is not likely to be the source of the failure, what should I do instead?

Update: We were informed by Microsoft that we needed to peer our VNET with the source database VNET and then link this to the SSIS IR. We have done this, but now we are getting a different error:

{ "SsisDBOperationGuid": "b40b6e4d-f557-4397-9db0-947d641edbe4", "SsisDBOperationId": 19, "OperationErrorMessages": "5/29/2019 8:56:44 AM +00:00 : Import New Data:Error: ADO NET Source has failed to acquire the connection {2FA367CE-3495-40B5-926D-F8A08AC5A3DA} with the following error message: \"One or more errors occurred.\".\r\n\n5/29/2019 8:56:44 AM +00:00 : Import New Data:Error: Campaign Source failed validation and returned error code 0xC0208449.\r\n\n5/29/2019 8:56:44 AM +00:00 : Import New Data:Error: One or more component failed validation.\r\n\n", "effectiveIntegrationRuntime": "SSIS-IR-PROD" (North Europe)", "executionDuration": 14452 }

Because a small test package to import only one table also failed, I assume there is still a connectivity issue between our database and the source database. I have tried running the package in 32-bit runtime but it still gives the same errors.

Any ideas? I am feeling quite at a loss regarding what to try next!

  • What is the remote source, another Azure SQL DB, an on-premises DB server, an IaaS SQL Server? The problem is that Azure cannot find the remote source by host name, so how you correct that depends on what the source is. – HandyD May 21 at 1:02
  • I am not sure as it's ran by a third-party company and we have select-only access, I will contact them to ask. The server name ends in ".database.windows.net", does this mean it is an Azure SQL DB? – Mel May 21 at 8:11
  • I have heard from the third-party company and apparently the database is an Azure Managed Instance. – Mel May 21 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.