I have created a package in Management Studio to import data from a text file on another server. The package should create a table in my database and import the data into the table in my SQL database. This package was created using a domain administrator account with windows authentication.
The package is saved as an SSIS package in MSDB and I can see the package in Management Studio by connecting to Integration Services. If I right-click on the package and run it, the package works successfully. However, if I set up a Sql Server Agent job and place the package as step one as a SSIS package using the Sql Agent Service account, the package fails. The log only says that step one failed. The create table part of the job works, because the table is created correctly.
1. How can I get this working I've checked all the permissions and directory mapping and everything looks good.
2. What values do I use to create a log for the step What are the correct choices for log provider and connection string
Would appreciate any help - this has been a problem for almost a month now!
Thanks
Sql Server Agent cannot run a package
DJSQ
I had a similar problem. Turned out to be the protection level on the SSIS package properties. The default protection level is "Encrypt package with user key" or something like that. If you want the SSIS package to run with the SQL Agent's access, you must change the protection level to "rely on server storage and roles for access control" when you import the package into Integration Services from BIDS.
PS. If this doesn't help, you might get better response on this question if you put it on the Integration Services forum. I think that's where I picked up the above bit of info.