Error when executing SSIS package through SQL Server Agent job

Question

I need to find a way to create a backup of a table's schema. I've been trying to do so using a Transfer SQL Server Object, but I don't see how you can specify the specific source and destination table to copy the schema to/from when using this type of control flow item in a package. Once I can figure this out, I will be scheduling this to run automatically. I would appreciate any assistance with this. Thanks!

Update
Thanks, all. I have the following problem - I have the Transfer SQL Server Objects control flow item working when executing from Visual Studio, and it is copying the table structure from one server/database to another server. I have created this as an SSIS package and imported it onto the original server (the one having the table structure copied), however, when trying to run the package job, I keep getting the error saying, "Failed to connect to server...there were errors during task validation...package execution returned DTSER_FAILURE (1)...the step failed." Does anyone have any idea of why it would run fine in Visual Studio, but not as a job?

Answer 1

Bit late, but maybe this answer will help someone else. There are (at least) two things that can cause an execution difference between you executing the package in BIDS and SQL Server running the package through a job.

1) The package protection level in BIDS package project
The ProtectionLevel property of your package is set to one of the following values:

• DontSaveSensitive
• EncryptSensitiveWithUserKey
• EncryptSensitiveWithPassword
• EncryptAllWithPassword
• EncryptAllWithUserKey
• ServerStorage

The default value is for some insane reason EncryptSensitiveWithUserKey. This means that sensitive information in the package is encrypted based on the credentials of the user who created the package. This also means that if another user (someone who did not create and save the package) opens or runs the package you get an error - that is, if there is any sensitive information in the package.

So you want to change this default value to something more sensible like DontSaveSensitive (our standard value) or EncryptSensitiveWithPassword.

2) The executing account in SQL Server Agent job
The standard value for the Run as field in the Job Step window is the SQL Server Agent Service Account. This account is configured when SQL Server is installed and permissions depend on the account that was chosen.

So it could be the case that this account does not have permission to do something that is defined in your package. Connect to another database server, for example.

You can choose another account to run the package, by setting up a proxy. More information on this can be found elsewhere on StackOverflow, see here for example.