I’ve just started using SSIS up from DTS and found the security has advanced quite alot since SQL 2000.
I initially found issues, and from the forums around the internet it appears many others also initially experience issues, therefore below is a workthrough and solution for creating an Scheduled Job for a SSIS Package in 2008. I've also tried to include some explanations on why and what we need to do, to get everything working as expected. This post assumes you know your way around SSMS and BIDS, if anything is unclear or you would like to comment or add information, please feel free. Its best to read through the entire post before trying to follow the steps, particularly the explination at the bottom, it will help you understand how everything fits together.

Create Your SSIS Package

1. Design and build your SSIS package in Business Intelligence Development Studio (BIDS).  
2. Ensure you can execute the package without any issues, be sure to include error handling, especially if your using external ad-hoc parameters. As you are building this package to be run as a scheduled job it will run under a specific account and typically not the account or permissions you are using to create the package, therefore to make things easier you should set the ‘ProtectionLevel’ of the package to “EncryptSensitiveWithUserKey”.
3. Save the package and exit BIDS.

Add Your Package to the MSDB Store

1. Log onto Integration Services via SSMS.
2. Under the Stored Packages node, right click on MSDB and select “Import Package”, select the package, etc... 
3. Adjust any connection strings as required. (In my case I have used trusted connections to connect to the relevant data sources) This is an important step as it may come back to haunt you later, especially if your utilising the file system or components external to the package.

Setup the Necessary Security, Accounts, etc

1. Create Credentials – this is used to map a Proxy to a SQL Login and Windows Login
   1. Within SSMS à Security à Credentials, select new.
   2. Name: <Credential Name>, Identity: <Windows account>, Password: <password for account specified as the identity account>
2. Create a SQL Login – this account will be used to run your scheduled job.
   1. Set the Login to have the sysadmin server role.
   2. Grant access to the MSDB database and allocate SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole.
   3. In this case I have also granted DatabaseMailUserRole for alerting an operator if the job fails.
   4. Set the default database for the login to your target database and grant any other database permissions as required, although being a sysadmin access should not be a problem.
   5. Map to the credentials just created.
3. Create a Proxy
   1. Within SSMS à SQL Server Agent  à Proxies à SSIS Package Execution, select new.
   2. Fill in the name, select the crediential, select the principal as the login just created and select the subsystem to be “SQL Server Integration Services Package”

Create Scheduled Job

1. Hopefully I don’t have to explain to you how to create a scheduled job, just remember to set the executing user as the login you created.
2. Execute the job and observe the results.

Explanation

Ok, now to explain everything, to make things simple and to avoid confusion I’ll start with definitions taking them straight from Microsoft.

Credential:

A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server. Most credentials include a Windows user and password.

Proxy:

A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user.

What have we just created, How does it work?

• We have just created a SSIS package which is executed by a SQL Server Agent scheduled Job. This job executes under the <SQL login> login.
• When trying to execute the SSIS package SQL (the agent) needs to access external resources and or a subsystem (in this case the SSIS subsystem), it finds the SQL Server Agent proxy account associated with the <SQL Login>, the proxy points to the credential you created called: <Credential>.
• The credential holds account information for a specific Windows account, this information is sourced all the way back through the proxy and this account information is utilised to execute the SSIS package.

A last note: the above can be performed using TSQL, and there are many solutions out there which reference the commands, for this example however I feel it is easier to start with the interface to gain an understanding and visulisation of how each of the components fit together.

Hi all,

To begin with I'd just like to say thanks for checking out my BLOG, hope the information you find within this soon to be vast knowledge base will help you.
I've been meaning to start a BLOG for quite a while now, I've done my research and regularly read other's BLOG's but instead of joining a generic or specific BLOG site I've decided to create my own site and embedded a BLOG within it (http://blogengine.codeplex.com/).
Why do I now have a BLOG?

• To record and store skills and information that I have gained through my experiences.
• To develop my communication skills at both a personal and professional level.
• Provide something back to the online community.

Within the coming BLOG's I hope to include posts in the subjects of:

• Information Technology (IT)
  • General Tips & Tricks
  • Systems & Application Administration
    • Windows XP, 2000, 2003, 2008
    • Application Troubleshooting
  • Database Development & Administration
    • SQL Server 2000, 2005, 2008
    • SSIS (DTS)
  • Business Intelligence & Reporting
    • SSRS
    • Crystal Reports
  • Professional Conduct & Responsibilities
  • Application Programming
    • C# (Windows & Web)
    • Best Practises
• Project Management
  • Planning
  • Analysis
  • Best Practises
• Law
• Health & Fitness

As you can see the contents of this BLOG cover a wide range of areas, of these (such as IT, Health & Fitness, and Project Management ) I am trained, qualified and experienced in, others such as I Law, I will be undertaking formal education soon. This BLOG and all posts I write because of my interest, experience or knowledge of the area. Please feel free to comment, rebutt, request more information or add to my posts, all feedback (constructive) is welcome.

Regards,

Dave