I just want to confirm that what I am seeing is correct.
I have created a new SSIS package, on my workstation. It loads data from a flat file to a table. Nice and simple. When I run it through Business Intelligence Studio on the workstation it runs OK.
Now I save the package to the server and create a job. The package fails with a login error on the SQL Server I an trying to load the data to. I adjusted the package to save the password but it still fails.
If I log on to the server with the account running SQL and SQL Agent and update/save the package then the job will work.
So, it seems that in order to save the login information you need to create the package with the account that is going to run it, otherwise it doesn't work.
Is there an alternative?