I'm working in SQL Server 2012. I've built out an SSIS package that is made up of a few scripts and 2 data flow tasks. The data flow tasks move data from the local SQL server out to an Amazon SQL server.
The whole package runs fine in Visual Studio. I imported it into the Stored Packages in Integration Services, and I can successfully run it from there.
However, when I set up a job and run that SSIS package as the one and only step, it fails. The only error message I get is that Step 1 failed. I can't trouble shoot why it fails because it otherwise runs successfully in Visual Studio and even in the Integration Services.
I've done a similar project before successfully, and what's different here and where I suspect the issue is in the Data Flow tasks. I suspect that the connection to the Amazon SQL Server is failing, that the password isn't getting properly passed. Again, I've got that information correct in the package, but somehow it's not running with that.
Can anyone point me to the way to properly pass the authentication info when the SSIS package is run as a job?