Previous Posts:
How to Execute SSIS package part#1
Using sp_start_job stored procedure to execute a sql agent job
sp_start_job is a system stored procedure stored in MSDB database, it is used to execute a SQL Server job which is already created. This stored procedure accepts various parameters, job name or job id being the mandatory parameter. Value of the variable “UserID” cannot be changed using this procedure.
syntax to execute the procedure is EXEC msdb.dbo.sp_start_job N’TestUser’
Using xp_cmdshell to execute a package from SSMS
Xp_cmdshell is off by default and needs to be enabled by a sysadmin. We have two ways to enable xp_cmdshell.
A. Enable using command
EXEC sp_configure ‘show advanced options’, 1
GO
– To update the currently configured value for advanced options.
RECONFIGURE
GO
– To enable the feature.
EXEC sp_configure ‘xp_cmdshell’, 1
GO
– To update the currently configured value for this feature.
RECONFIGURE
GO
B. Enable from SSMS using Mouse
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Features.
3. Click on xp_cmdshell and tick on checkbox of "Enable the xp_cmdshell".
The command would be like: EXEC XP_CMDSHELL ‘DTEXEC <Place Holder>’ Use DtexecUI to set the value of variable Mode as Xp_cmdshell. Then click on Command Line tab and copy the command and paste it in the place holder above (). Using this method to execute a package is not recommended because the use of xp_cmdshell requires sysadmin privilege and thus it opens up a security hole.
Using BIDS
To execute SSIS Package from BIDS press ‘f5’ or click the Execute button shown in the below figure.
Using SQL Agent by creating a job (scheduled or unscheduled)
Create a job to execute the package from file system. Right click the SQL Agent by connecting to SQL Server Management Studio and select New Job. Give the job name as TestExecution and then click on Steps. Click on New to add a job step Execute as shown below: 
SQL Agent Service Account is the default account which the SQL Agent will use while executing the package. Now click on Set Values tab shown in the above figure (Green colored box) and set the value of variable Mode as SQLAgent as shown below: 
Once the value of variable is set, finish the job by clicking OK. Don’t schedule the job (it can be scheduled by clicking on Schedule tab). Now right click the job and click on "Start Job at step…" as shown below: 
