Schedule SSIS Package Without Deploying.
You can schedule your package before you completely deploy it. you just need to build your SSIS Package and you will get executable “.dtsx” file.
Path for “.dtsx” file is different according to SQL Server versions.
For SQL Serve 2008 : “your package path \bin\*.dtsx”
For SQL Serve 2012 : “your package path \bin\Development\*.dtsx”
There are two ways to schedule SSIS Package if you have created package through “File System”.
1) SQL Server Agent Job.
You need to create new job in your SQL Server.
Step1 : Connect your local sql server –> expand SQL Server Agent –> right click on job and click on new job.
Step2 : Give “Call Package” to your job name.
Step3 : Click on “New” button on Steps tab.
Step4 : On New Job Step – Select SQL Server Integration Package in TYPE and File System in Package Source and give “.dtsx” file path.
Step5 : Schedule your package as you convenient time.
2) Windows Schedule Task.
You can’t call “.dtsx” file directly from Schedule task, you must need to create batch file which calls “.dtsx” file and letter you can create schedule task for that batch file.
Step1 : Create batch file with following code.
"C:\Program Files\Microsoft SQL Server\110\DTS\Binn\dtexec.exe" /FILE "D:\Work\SSIS\Nirav's Diary\Nirav's Diary\bin\Development\GetSpaceInfoWithWMI.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EWCDI
save it to “D:\Work\SSIS\BatchFiles\GetSpaceInfoWithWMI.bat”
Step2 : Create Windows Schedule Task.
Click on Start button — > Run –> and Type — > control schedtasks and click ok.
Step3 : Click on Task Scheduler Library on left pane of Task Scheduler, it will show a window to create new task.
Now on the right side of Task Scheduler –> click on Create Task under Actions pane. It will open one pop menu to create new task.
Give schedule name and select option “Run whether user is logged in or not”.
Step4 : Select Trigger tab from Create Task window –> click on new to schedule time.
Step5 : Select Action tab from Create Task window –> click on browse to map file path.
Click on “OK” button to complete it. windows will ask for user name and password.
Please apply administrator user and password or any other user which have a permission to execute schedule task.