Search code examples
sql-serverssisenvironment-variablessql-agentforeach-loop-container

SSIS Project - Catalog Deployment - Environment Variable (for location) accessing file server mapped to a local Z drive - SQL Server Agent issue

I have an SSIS package that reads a number of files using a For Each Loop Container. There are a number of parameters in this package, and in the Integration Services Catalog in the SSMS, I have created an environment with many variables for this project/package.

There are a number of environment variables for this package. There is a particular environment variable for Source Location.

While in my DEV setting, I was able to pass the Source Location environment variable as :

C:\Data Repository\Files    (in a local machine).

Everything fine. Package runs perfectly, and For Each Loop Container works reads the files.

However, in the PROD setting, I have to use a file server, mapped to a Z drive.

For example: 

This PC > Data Repository (\\tordfs) (Z:) > Data Repository > X

becomes

Z:\Data Repository\X

when I copy the path.

Inside the SSIS package, I am able to set the parameter value for Source Location as Z:\Data Repository\X and the For Each Loop Container works fine from the SSDT/Visual Studio.

Now after the SSIS package/project is deployed to the SSMS Catalog, when I feed Z:\Data Repository\X as a value for the Source Location environment variable, and I Execute the package manually from the Catalog, it works fine.

However, when I use the SQL Server Agent for the above process, I get the following error:

For Each Loop Container:Warning: The For Each File enumerator is empty. The For Each File enumerator did not find any files that matched the file pattern, or the specified directory was empty.

Is there anything I need to do in the For Each Loop Container or the SSIS Catalog to eliminate the above error during execution from the Catalog using SQL Server Agent?

Let me know.

Solution

  • In Windows mapped drives are user-specific. So you would have to map the drive for the account running the package. Instead use a UNC Path in both cases, and not a drive letter.

    So something like:

    \\tordfs\Data Repository\Files

    The account running the package will still need permissions to the share, and permissions to the folder, but won't need a drive letter mount.