Problem: One of the Junior SQL Server Developers in my company approached me yesterday with a dilemma. He was developing an SSIS Package which imports data from a comma separated text file and he wanted to know the different ways in which one can execute an SSIS Package in SQL Server 2005 and higher versions. At first I started to tell him, but figured it would be smarter to document the options and share the information.
Solution: In SQL Server 2005 and higher versions there are different ways in which one can execute an SSIS package. Let us go through each option one by one.
Execute SSIS Package Using SQL Server Business Intelligence Development Studio (BIDS)
During the development phase of the project developers can test the SSIS package execution by running the package from Business Intelligence Development Studio a.k.a. BIDS.
1. In Solution Explorer, right click the SSIS project folder that contains the package which you want to run and then click properties as shown in the snippet below.
2. In the SSIS Property Pages dialog box, select Build option under the Configuration Properties node and in the right side panel, provide the folder location where you want the SSIS package to be deployed within the OutputPath. Click OK to save the changes in the property page.
Execute SSIS Package using SQL Server Agent Job
Using a SQL Server Agent Job one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store. This can be done by creating a new SQL Server Agent Job and then by adding a new step with details as mentioned in the snippet below.
1. In New Job Step dialog box provide an appropriate Step name, then choose “SQL Server Integration Services Package” option as Type from the drop down list, and then choose “SQL Server Agent Service Account” as Run as value.
2. In the General tab choose the File System as Package Source and provide the location of the SSIS package under Package option.
3. Click OK to save the job step and click OK once again to save the SQL Server Agent Job
4. That’s it now you can execute the SQL Server Agent Job which will internally execute the SSIS package.
Note: You can also execute the SSIS package using the Export and Import Wizard once it is created using the wizard.
Solution: In SQL Server 2005 and higher versions there are different ways in which one can execute an SSIS package. Let us go through each option one by one.
Execute SSIS Package Using SQL Server Business Intelligence Development Studio (BIDS)
During the development phase of the project developers can test the SSIS package execution by running the package from Business Intelligence Development Studio a.k.a. BIDS.
1. In Solution Explorer, right click the SSIS project folder that contains the package which you want to run and then click properties as shown in the snippet below.
2. In the SSIS Property Pages dialog box, select Build option under the Configuration Properties node and in the right side panel, provide the folder location where you want the SSIS package to be deployed within the OutputPath. Click OK to save the changes in the property page.3. In Solution Explorer, right click the SSIS Package and then click Set as Startup Object option as shown in the snippet below.
4. Finally to execute the SSIS package, right click the package within Solution Explorer and select Execute Package option from the drop down menu as shown in the snippet below.
Execute SSIS Package using 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:\BulkInsert\BulkInsertTask.dtsx"
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:\BulkInsert\BulkInsertTask.dtsx"
Execute SSIS Package using DTEXECUI.EXE Utility
Using the Execute Package Utility (DTEXECUI.EXE) graphical interface one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store.
Using the Execute Package Utility (DTEXECUI.EXE) graphical interface one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store.
1. In command line, type DTEXECUI.EXE which will open up Execute Package Utility as shown in the snippet below. Within the Execute Package Utility, click on the General tab and then choose the Package source as “File System”, next you need to provide the path of the SSIS package under Package option and finally click the Execute button to execute the SSIS package.
The Execute Package Utility is also used when you execute the SSIS package from the Integration Services node in SQL Server Management Studio.
Execute SSIS Package using SQL Server Agent Job
Using a SQL Server Agent Job one can execute an SSIS package that is stored in a File System, SQL Server or an SSIS Package Store. This can be done by creating a new SQL Server Agent Job and then by adding a new step with details as mentioned in the snippet below.
1. In New Job Step dialog box provide an appropriate Step name, then choose “SQL Server Integration Services Package” option as Type from the drop down list, and then choose “SQL Server Agent Service Account” as Run as value.
2. In the General tab choose the File System as Package Source and provide the location of the SSIS package under Package option.
3. Click OK to save the job step and click OK once again to save the SQL Server Agent Job
4. That’s it now you can execute the SQL Server Agent Job which will internally execute the SSIS package.
Note: You can also execute the SSIS package using the Export and Import Wizard once it is created using the wizard.
Comments