I've created a SSIS project in Visual Studio and it runs well to process the text file, insert records in SQL tables and move the processed file to archive folder when I manually execute the package in Visual Studio.
But, after I deployed the project to SSISDB in the Integration Services Catalogs and created a job in SQL Server Agent, I can see that the job succeeded in the history, but the the text file was not processed, the records were not inserted to the SQL tables and the text file was not moved to archive folder.
From the Execution Information from of the package in SSISDB, the message shows: Foreach File1 Testing: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.
In my Collection page of Foreach Loop Editor, I did use full folder path like \\MyTestingFileServer1\FilesInPlace\ not the mapped network drive name. The files was set as: "*.txt" and Retrive file name I select: "Fully qualified".
Please tell me what else should I check for the configuration to make the job run in the SQL Server Agent.
You need to verify that the account running the SSIS package has permissions to the network location. If the agent job is owned by a sysadmin account - and the job step is not using a proxy account - the agent job will be running in the context of the account running SQL Server.
If the agent job is running as a non-sysadmin account - and the job step is not set to run as a proxy account - it will be running in the context of the job owner.
Ideally - you should create a proxy account for Integration Services and set the job step to run as that proxy account. You can then make sure the proxy account has the necessary rights to the UNC location.
Hi jeffw8713,
Do you think this is the permissions issue in SQL Server Agent and I should execute the job with an account with system admin privileges?
I was using my login with sa privileges and SQL admin to create the job and run the job, both accounts have the same issue. Should I create a proxy to set the job and run?
For the proxy account, what is the credential name should be use? as now, I got an error when I create a now proxy account. The Credential name can't verify any login name when I put in any name.
No idea - but it generally is related to network permissions when an agent job doesn't work like this. If the agent job is owned by your account (not recommend - your account can be disabled causing agent jobs to fail) and your account is sysadmin - then the agent job is running in the context of the service account running SQL Server.
If that service account does not have permissions to the network location - it needs to be added on the network share.