It's easy to trigger a lot of obscure errors with SQL Server Agent when scheduling an SSIS package to run within a job. The key problems occur when you override connection strings within the GUI.

It's easiest to demonstrate with an example. Here's the dummy back-end:

Create Database Development
Go
Create Table Development.dbo.Destination (Value Int)
Go

Create Database Production
Go
Create Table Production.dbo.Destination (Value Int)
Go

And an SSIS package called Agent Test Package. It has a connection called "SQL Connection" which points to the Production database and writes 1 row to the Destination table.

Then I set up a simple job to execute this package on the file system with all of the defaults.

Does it work?

Of course it does because that's the extent of Microsoft's testing! Just kidding. But let's see what happens when I override the default connection string to point at the Development database.

Wah wah.

Here's the error:

Message
Executed as user: W08R2S08\Agent2. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6000.34 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Argument "SQL" for option "connection" is not valid.  The command line parameters are invalid.  The step failed.

And what does that mean? Well if you think to investigate the Command Line tab of the job step it shows the likely culprit depends on the internals of your package (which is why it's so hard to Google and find the cause of the error:

/FILE "C:\Package\Agent Test Package.dtsx"  /CONNECTION "SQL Connection";"\"Data Source=.\SQL2008;Initial Catalog=Development;Integrated Security=True;Application Name=""SSIS-Agent Test Package-{F9D69628-6DF1-46EE-8E52-CF8E9E1ADEF2}.\SQL2008.Development"";\"" /X86  /CHECKPOINTING OFF /REPORTING E

That's right, it doesn't like the space in the connection name within the package! How shoddy. So I wrote a new SSIS package, only changing the connection name to not have a space, and set up another agent job with the override.

Wah wah. It also fails! Why this time?

Message
Executed as user: W08R2S08\Agent2. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6000.34 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Option "-Agent" is not valid.  The command line parameters are invalid.  The step failed.

A different error! This is the content of the Command Line tab:

/FILE "C:\Package\Agent Test Package No Space.dtsx"  /CONNECTION "SQL_Connection";"\"Data Source=.\SQL2008;Initial Catalog=Development;Integrated Security=True;Application Name=""SSIS-Agent Test Package-{F9D69628-6DF1-46EE-8E52-CF8E9E1ADEF2}.\SQL2008.Development"";\"" /X86  /CHECKPOINTING OFF /REPORTING E

In this case, the stupid thing extracted the Application Name and then barfed because it doesn't like spaces OR dashes in there!

These don't work:

Data Source=.\SQL2008;Initial Catalog=Development;Integrated Security=True;Application Name="SSIS-Agent Test Package-{F9D69628-6DF1-46EE-8E52-CF8E9E1ADEF2}.\SQL2008.Development";
Data Source=.\SQL2008;Initial Catalog=Development;Integrated Security=True;Application Name="A B C";
Data Source=.\SQL2008;Initial Catalog=Development;Integrated Security=True;Application Name="A-B-C";

But if you remove all spaces and dashes from the Application Name it works. It's a pretty sad bug.

Now to clean up.

Use	master
Alter Database Development Set Single_User With Rollback Immediate
Alter Database Production Set Single_User With Rollback Immediate
Drop Database Development
Drop Database Production

Source files for the packages are here if you'd like to test them for yourself.