I have a package in SSIS that loads 3 excel files into a table: once the loading is finished, I use a table to track the load time + the amount of loaded data, After that I transfer the files via FileTaskSystem and ForEach Container. This is my package:
it works perfectly when I execute it in SSIS and the this is the output in the transfertable:
and moreover, the files are transferred to the destination.
When I execute that package in the SSMS via SQL Agent it works but it doesn't load the data inside the mrrMashbir3 and mrrMega tables. The only thing that works is the RetailerContainer which is not inside ForeachLoop
As you can see, all the actions that were in the Foreach didn't work (neither the file transferring).
1条答案
按热度按时间ego6inou1#
The Foreach File enumerator will raise an Informational event when it cannot list the contents of a folder. Not an error, just a simple "I didn't find any files here"
As the process works when "you" run it but does not when the "SQL Agent" account runs it, the most probable issue is that account does not have permissions to the path. Less likely is that the path does not exist - i.e. development uses a mapped drive D: which is really \server\share\data which your account auto maps but the agent account does not.
Resolution is going to be dependent on the root cause but probably takes the form of one of the following
Check your SSIS log for Information events, you'll likely see one from the
Mashbir Foreachand if the above options don't resolve it, edit your question with the specific details (account that is running the package, where the foreach enumerators are pointed to, etc) and then comment on this answer and I'll check back.