Working SSIS package fails as a job, probably due to data flow connection

Question

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?

Answer 1

All right, I think I've got it. I'm relatively new to this, so forgive any bad terminology.

I got on the right path with this: https://stackoverflow.com/questions/31755979/storing-password-in-ssis-package

Basically, the external connection to the Amazon DB was the issue. I had the correct credentials stored in the SSIS package to make the connection, but by default sensitive information (such as passwords) are encrypted in such a way that is specific to the user who created the package. So the package ran fine for me, but it wouldn't work for anyone else, including the job.

To fix this, you need to change the default Package protection to give the package a sort of master password, so that the package will run correctly for anyone, as long as they have the password for the package. In the project properties, you can find ProtectionLevel and switch the value to another option (I chose "EncryptSensitiveWithPassword.") You'll enter a password, then be prompted to also change the child package files. Get into the package properties and change protection level there too. Enter the same password on the line above. Save it.

Now, when you import package into Integration Services you'll be prompted to enter the password. Then you'll be prompted again when you set up the job. But with the password entered it runs successfully.

Hope this helps somebody.