CODEWITHSUNDEEP
windowsssisdirectoryetlsql-server-agent
Baldie47
Baldie47

Reputation: 1254

File System Task from SSIS not working in SQL Server Agent (no errors)

I have a File system Task that does some ETL (moves some .txt files, copies to other directory, renames) between servers.

enter image description here

When I execute this package from Visual Studio, it works well, it copies the files from one server to the other, renames, and archives. but when I set it with a job to run from the SQL Server Agent, it finishes without error, but it doesn't operate with any file, (no copy, no rename, no move) not even at the same server level.

this is the history log from running the job:

Executed as user: USERNAME. Microsoft (R) SQL Server Execute Package Utility Version 12.0.2548.0 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 3:08:56 PM DTExec: The package execution returned DTSER_SUCCESS (0). Started: 3:08:56 PM Finished: 3:08:57 PM Elapsed: 0.688 seconds. The package executed successfully. The step succeeded.

The paths that I use for the package are all in the format:

\\ServerName\directory\subDir

My question would be, how can I know why the File System Task is not actually working?

I don't know how to check on logs more detailed to what I see on the SQL Server Agent to know what is actually happening, and if it's a permission issue I believe that I should see errors. I'm not sure about what is happening at this point. Please let me know if there is more info I should add to help.

EDIT: I'm adding logging files from what I execute from VisualStudio (the one that works) and when the package is called from SQL Server Agent.

I will replace server names and usernames but I'll show what permits they have on different folders.

**MyUsername**  = Username when I run from visual studio
**ORIGIN**  =  Origin server from where I copy the files
**DESTINATION** =  Destination server where I put them and manipulate
**SqlServerAgent**  = ServerAgent name

Log files from my computer when I run from visual studio (and the package operates with the files):

Log in Pastebin (VisualStudio)

and the log files for when I run from the SQL Server Agent:

log sql server agent

And information about the permits:

enter image description here

enter image description here

Upvotes: 1

Views: 1639

Answers (1)

Baldie47
Baldie47

Reputation: 1254

I found the issue, wasn't in the Parameters, or permit. The issue was that the For Each Loop was pointing to a mapped drive instead of full qualified path like the rest of the folders, hence the For Each Loop wasn't finding anything. However the permits were ok, just wasn't finding anything.

Upvotes: 1

Related Questions