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.
