We now have a basic simple SSIS Package ready. We have also executed them to get some result. Are there any other ways to execute the SSIS Packages?
Yes, there are multiple ways to execute a SSIS package that you have. We will look into the various ways possible.
- BIDS
- DTEXEC.EXE command line utility
- DTEXECUI.EXE utility
- SQL Server Agent Job
BIDS: This is what we have been using all this while. You have the package in the solution explorer. If there is just one package in the solution, click F5 or Debug-> Start Debugging and the package will be executed for you.
If you have more than one package in your solution, you could right click the package in the solution and click Execute Package.
DTEXEC.EXE Command line utility: Using the DTEXEC.EXE command line utility one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store. The syntax to execute a SSIS package which is stored in a File System is shown below.
DTEXEC.EXE /F "C:\File Path\Package.dtsx"
DTEXECUI.EXE: In the Command prompt, type DTEXECUI.EXE. Execute Package Utility will open. You can browse your package from File System, SQL Server or SSIS Package Store. As of now we have seen just the File System, we will cover the others later. Select File System, browse the file and click Execute. Your package will be executed and you can see the progress and the output.
SQL Server Agent Job: This is used to execute the job from SQL Server job and used to schedule the job so that the package can be run as we want automatically. Below are the steps to be followed:
Go to SQL Server Management Services and expand SQL Server Agent. Ensure that the Agent has started and you see the green icon next to SQL Server Agent. If not, right click SQL Server Agent and click Start.
Next go to Jobs and select New Job.
New Job window will open; provide a name to the job ensure that it is enabled .
Go to Steps in the left panel and click New.
The New Job Step window will pop up (figure below). Here put the Step Name.
Set Type to SQL Server Integration Services Package
Set Package Source to File System
Set the Package Path in the Package and click OK.
Next, go to Schedule on the left panel and click New. New job schedule window will pop up as in figure below. Here you will see all the options to schedule the job. It may be recursive or executed one time as per your wish. Play around to learn more.
Once you have set up the schedule, click OK and again OK on the New Job popup. You Job is ready to be fired. It will execute as per the time you set. In case you want to run it manually from here, you need to expand jobs go to the job you just created. Right click the job and select “Start Job at step…” (as in figure below)
You will notice a Start Job window pop up showing the Progress of the job. Once it goes green (refer figure below) your job is successful and you can verify that the package has done its job.
That is it; I hope you have the basic understanding of SSIS. Will start on advance topics shortly. Till then explore the work of ETL.