Scheduling an SSIS Package 1

[Dick999]

I have a simple package with two tasks. The first simply gets a date from an SQL2005 table. The second is a data flow task that transfers data from an ACCESS table to a SQL2005 table. When I run it from Studio it works like a charm. I tried to schedule it as a SQL Server Agent job. The job has one step, the package. The package is stored in a folder (Package Source: File System) on the database server and I use the full path (NOT drive letters) to identify it. When I try to run the job it crashes at step 1, the only step, after 00:00:01!

Any ideas?
TIA

 

[paulkeys]

Sounds like an issue with the Protection Level property on the package - if it's set to EncryptSensitiveWithUserKey, it will only allow the developer to run it - not the SQL Server Agent.

One way around this is to have EncryptSensitiveWithPassword, as this will allow the SQL Server Agent to run the package (so long as the password was entered when you created the job

Hope this helps...

 

[Dick999]

Thanks paulkeys. I did as you suggested and worked perfectly (after I corrected a data source which used a drive letter instead of a full path).

 

[id97073347]

where can I set the "EncryptSensitiveWithUserKey" in my package?

 

[paulkeys]

when the package is open in BIDS, click on the background of the COntrol Flow tab, and the Package Protection property is available within the package properties