Geek Answers Handbook

The SSIS package running on the server agent does not do its job (even with a successful message)

I have to say that I hate myself for such a general question: "What am I doing wrong?" but I just have no idea what might be the problem:

I created an SSIS package that takes data from flat files (CSV), calculates the average of one of the columns by date, and writes them to the database and deletes the original file. Everything works fine when it is running in SSIS, but when I plan it in Server Agent it just doesn’t work - it’s a successful report log, but there is no new data in the database, and the .csv file exists in the original location. I know the problem with the security level set in SSIS, so I changed it to "EncryptAllWithPassword" and I use the same password with Server Agent. Here is a link to the script server agent job (created as "script job as DROP and CREATE")

Edit: just to make things weirder using

  dtexcec /f {filepath} /de {password}

runs the program without problems. I know that I can open such a command in Windows itself, but I would like to save all scheduled tasks in one place - in Agent Server

EDIT: allowed by changing the path to UNC

+4
source share
1 answer

There are two important things to keep in mind when setting up packages running with a SQL Server Agent job.

Use UNC paths for all file locations, no matter how simple they are. There is a high probability that the server will have a different view of the file structure than your development machine, so UNC paths ensure that both machines refer to the same paths.

- , http://www.mssqltips.com/sqlservertip/2163/running-a-ssis-package-from-sql-server-agent-using-a-proxy-account/. . ( ).

+3

More articles:


All Articles