Search code examples
sql-serverssissql-agent-job

SSIS Package works, but Job fails when CREATE table

I have a problem with SQL Server Agent. My SSIS package works well in Visual Studio, and it used to work in Agent before I made some changes in the package. Instead of using one specific Flat File Source it reads all the files added to the folder Drop, insert it into the table Clients and then moves it to Archive folder. Later the structure is the same, it creates temp table and get date from previously added table Clients. The problem is that the prat with creating Temp table used to work and now its not.

Here is the error list: Error List

From things I've checked: deploying the package, checked files if they actually have some nulls and checked if during the job there are data added to the table Clients and it seems they are not. However, Package separately works fine

Package

Solution

  • I fixed it. The reason was connected with changing of the source from Flat File Source to many files, where you use variables to do it. Probably Agent haven't get the change. Restart of the database fixed the problem.