question

MDD-4710 avatar image
1 Vote"
MDD-4710 asked ·

UNABLE TO RUN SSIS PACKAGE THROUGH SQL SERVER AGENT FOR AZURE MANAGED INSTANCES

Hi,

I created a SSIS package in Visual Studio 2017 to load data from one of the tables in Azure Managed Instances to another database, and it worked fine.

But, when I create a job for that SSIS package in SQL SERVER AGENT in SSMS v.2018 and run that job, it throws me the following error:

Description: ADO NET Source has failed to acquire the connection {----} with the following error message: "Unable to load adalsql.dll (Authentication=ActiveDirectoryIntegrated). Error code: 0x2. For more information, see http://go.microsoft.com/fwlink/?LinkID=513072";.

I visited that website and re-installed adalsql.ddl, but it still gives me that same error.

What could be the possible solution?

Thank you

not-supported
· 2
10 |1000 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.

What kind of SQL Server did you create the Agent job on? SSMS is just a client tool. Does your SSIS pacakge run in Visual Studio? I don't believe that Managed Instance supports windows logins. First try getting it working using SQL Authentication and go from there.

0 Votes 0 ·

I used Microsoft SQL Server to create the Agent job, and picked the following:
Type: SQL Server Integration Services Package;
Run as: SQL Server Agent Service Account;
Package Source: File System

Yes, my SSIS package runs in Visual Studio - I used Azure Active Directory Integrated to connect to the Azure db

I can also connect to the Azure db in Microsoft SQL Server through Azure Active Directory - Universal with MFA

However, there are only two options in Visual Studio:
Azure Active Directory Integrated (picked this in my SSIS package);
Azure Active Directory Password

Thank you!

0 Votes 0 ·
neray-MSFT avatar image
0 Votes"
neray-MSFT answered ·

@MDD-4710,
The issue you have described pertains more to the SQL Server and hence we would suggest you to post the query on the SQL Server Forum for better assistance.


· Share
10 |1000 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.

UriDimant avatar image
0 Votes"
UriDimant answered ·

Hi
Where you deployed the package to? You can more detailed error message under SSISDB catalog

· Share
10 |1000 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.

louisesangbedo-6502 avatar image
0 Votes"
louisesangbedo-6502 answered ·

I got a similar issue. ADO.Net connection within an active directory context .Package failing in SSIS_DB integration services catalog however when i test the package itself before deployment I realize that the connection is failing with the error message below.


TITLE: Connection Manager



Test connection failed because of an error in initializing provider. One or more errors occurred.


BUTTONS:

OK


· Share
10 |1000 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.

MDD-4710 avatar image
0 Votes"
MDD-4710 answered ·

Hi everyone,
my apologies for not being active in this QNA.

I already found the solution.

The issue was, even though I installed adasql.ddl and correct version of ADO.NET in my pc, those library and framework were still missing in our work server (sql server agent Jobs run in our office server).

After DBA installed them in the server side too, the job ran successfully.

I did not use SSISDB but deployed the SSIS package in the SQL Server Agent using the
Type - SQL Server Integration Services Package Package source - File System

Thank you!

· Share
10 |1000 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.