SSIS Package errors opening file on server using newly setup PC, works fine on old PC (same config)

Messiaux Yannick 1 Reputation point
2023-09-01T13:05:39.9966667+00:00

I just recently got my work laptop changed and made sure I have the same software installed. I use Visual Studio 2019 with Integation Services and SQL Server Management Studio.

The package is very simple: the source file is a CSV file and destination is SQL Server. When I run the package in Visual Studio, it executes well. When I run it from SSMS it fails, saying "Error: cannot open the datafile". If I run the same package on my old laptop in SSMS it runs well.

The package runs with my own credentials and I have access to the file.

Note that my old laptop has SSMS 18 and the new has SSMS 19 (not sure if it makes a big difference).

If I ccreate a job to run it under SQL Server Agent and proxy it works well.

Any idea why I can't access the file?

Thank you in advance for your help

Visual Studio
Visual Studio
A family of Microsoft suites of integrated development tools for building applications for Windows, the web and mobile devices.
4,827 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,216 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,503 questions
Windows 11
Windows 11
A Microsoft operating system designed for productivity, creativity, and ease of use.
8,813 questions
0 comments

6 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Messiaux Yannick 1 Reputation point
    2024-04-05T06:37:29.8733333+00:00

    Thank you all for your answers. Finally, the problem is linked to security settings.

    My previous laptop was using a simple userid/password credentials to login to my work account and same for the login on SSIS and SSMS. The new laptop requires a Kerberos double-hop authentication when SSIS and SSMS still require a single userid/password login. Therefore, when I try to execute a package in SSMS, the package is executed on my behalf by the server and the resources on the network cannot be reached.

    The only solution is to create jobs with SQL Server Agent and a proxy.

    Hope this will help others as well.