I created a SSIS package in Visual Studio 2010 (BIDS). It's using an OLE DB Source and an OLE DB Destination. Both the source and destination use Windows authentication (a domain user) to connect and both are local to the Package.
In VS the connection is successful and I'm able to execute the package.
Now, I'm trying to schedule it in SSMS using SQL Server Agent.
I first connected to SSIS in SSMS and imported the package from my file system into MSDB. Then, I created a new job with one step to execute the SSIS package. I see my package level connections, so everything appears ok. I also used Windows Authentication for this step as well
However when I try to run the I got login errors.
- First, it said the login 'Domain\InstanceName$' failed.
- So I switched the windows authentication in the step within the job to sql server authentication. Now I get another error (it's slightly different) stating the login 'Domain\UserName' failed.
How do I get this to work as a schedule?