SSIS Package Foreach Loop Container not finding files when deployed (fine in DEV)
I have a SSIS package created in Visual Studio 2017 SSDT with Project Deployment model and parameters. I have a project parameter called RootPath which is the path to a folder where a bunch of csv or excel files are. I want to iterate this folder and load the files into SQL Server.
It is working fine when running the package in Visual studio SSDT. however when I deploy it to SQL server and run it, the output of the Foreach Loop container is Empty and therefore cannot find any files. Input files (Excel or CSV) are kept in a shared folder accessed using UNC path. I see this message in the log:
Loop_GetFiles: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.
SQL Server version is: Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) and Visual studio 2017 SSDT version is SQL Server Data Tools 15.1.61903.01040.
I have set the Target SQL server version to SQL Server 2016 as the project is developed in Visual studio 2017 and project is deployed to SQL Server 2016 as there as some compatibility issue in script task creating GUID.
Has anyone else experienced this and discovered a workaround? Please help
Tried providing read/write access for the import file share to the account running SQL Server Agent service.
When you deploy:
1. Make sure that you build your project in Visual Studio before you deploy.
2. After you have deployed the project, if you have added a job and running this job, there are parameters to be added.
Go to this new Job -> Right click and select properties -> Double click 'Steps' -> Open the corresponding step and go to configuration. You can add your parameter here.
Hope this helps.