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

Question

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

Answer 1

Hi @Messiaux Yannick,

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.

Please edit your question, and clarify how exactly you are running the package.

Also, are you are deploying SSIS project from your developer workstation with Visual Studio to a remote SSIS run-time server?

To execute SSIS package on the remote SSIS run-time server there are few ways to do that interactively:

1. Open Remote Desktop session to the remote SSIS run-time server, login to the SQL Server instance by using Windows Authentication, and execute SSIS package directly in SSMS SSIS Catalog.
2. Create SQL Server Job to execute an SSIS package.

Answer 2

Hi @Messiaux Yannick,

Seems like a permission issue, could you please check the account used to login to sql server could reach the shared folder and read the data file.

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

When you run the package in Visual Studio, it typically runs with your user credentials and has access to your local resources, including the CSV file.

However, when you run it from SSMS, it might run under a different context, possibly with different permissions or access to resources. This could explain why it fails to access the file.

Here are a few suggestions to troubleshoot and resolve the issue:

1. Check Credentials: Ensure that the SQL Server Agent and SSMS are running under the register same user credentials as your Visual Studio environment. If they are running under different users, it can lead to permission issues.

Permissions: Double-check the permissions on the CSV file. Make sure that the SQL Server service account or the user running SSMS has read access to the directory where the CSV file is located.

Answer 4

Thank you all for your feedbacks, much appreciated.

It indeed looks like a permission issue but I can't understand why. The only thing that changed between when it used to work well and when it does not work well anymore is the change of my work PC. I moved from Windows 10 to Windows 11, SSMS is the same, Visual Studio is the same and all my other SSIS packages that do not need to access files on a directory work well. The only issue is when the package needs to open a physical file on a shared drive (csv, txt, xlsx, ...) and only on my new PC. When I execute the same package from my previous PC, everything works well.

Answer 5

Check if there are any differences in permissions or file paths between the old and new laptops. Also, verify if SSMS 19 has any compatibility issues with Discord Fonts SSIS package. Creating a job with SQL Server Agent as a workaround is a good temporary solution.