have a sql2005 with SSIS packet that im trying to run with jobs but get eventid 18456 (Login failed for user 'sa'. [CLIENT: <local machine>]) everytime no matter what i try/do.
The packet imports csv data to a table, csv file location is local server.
job info "general"
Job info "steps" and step 1
run as: SQL Agent Service Account
Packet source: SQL server
login to server: use windows autentication
If i run the packet directly from SSIS store it works just fine, no error's at all and the table is populated.
I have several other SSIS packets thats works just fine to run with jobs (maintenance) but this one just wont work.
From what i can tell, the jobs that dosen't work have the same configuration as the one thats works.
I don't know what info i should provide is this case so be gentle
Sounds like a security issue, specifically with the service account starting the agent, perhaps it does not have the correct permissions to where the csv file is. To load a simple csv file to a table I would not even bother with SSIS, I would just use bulk insert.
BULK INSERT OrdersBulk
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'