Different ways to execute a SQL Server SSIS package

By:   |   Comments (14)   |   Related: 1 | 2 | 3 | 4 | More > Integration Services Execute Package Options


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.

solution explorer

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.

ssis property pages

3. In Solution Explorer, right click the SSIS Package and then click Set as Startup Object option as shown in the snippet below.

set as startup object

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 package

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"

windows system

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.

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.

execute package utility

The Execute Package Utility is also used when you execute the SSIS package from the Integration Services node in SQL Server Management Studio.

run package

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.

new job step

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.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Monday, January 28, 2019 - 8:47:08 PM - Irfan Back To Top (78898)

 Can we execute SSDT-2012 package into SQL-Server-2012 using SQL-Job "File System"? Currently I am trying to execute in this way but not able to do this, not getting the exact problem.

Friday, October 6, 2017 - 2:00:51 PM - Lauren Glenn Back To Top (66979)

Very nicely and succinctly explained.  I was trying to run a DTSX file that I designed to run on one server and migrate it to another destination database while hoping to not have to recreate the whole thing in SSMS again.  This is a huge help.  Thanks. 

 

Tuesday, June 23, 2015 - 6:03:54 AM - Dhee Back To Top (37987)

Hi All,

I have created the SSIS package, its working fine in my local PC as well as from Shared path if I Trigger the BAT file manually, if I create the Windows Scheduler from My local PC its running fine.

But the issue is if I create the Windows scheduler in the Server it’s not working, in this server SQL Server was not installed, it’s not showing any error message also, I am unable to figure out the what will be the issue.

My understanding is SQL Server not installed in The server because of that only it’s not running,

Please let me know your views on this issue.

Thursday, September 4, 2014 - 8:53:56 AM - Juan Perez Back To Top (34380)

would like to know, if possible, such as running a package from a stored procedure. I did not find information in the manual sql.

 

thanks! 

 

Thursday, September 4, 2014 - 8:50:25 AM - Juan Perez Back To Top (34379)

Quisiera sable, si es Posible, de como ejecutar procedimiento de almacenamiento de la ONU desde ssis Paquete un. ¿Cual seria el codigo? no encontre nada en el manual del servidor de sql 2012.- 

Desde ya, muchas gracias !! 

 

 

 

Thursday, August 1, 2013 - 4:05:25 PM - DAVE Back To Top (26091)

So if I have Visual Studio 2010 and create a package that connects to one database and pulls data to another database (glorified execute sql task) and it is saved on my local hard drive, can I execute it without having to deploy to a full installation of SSIS on some server somewhere?  If I am already connected to the two db's and can test my package for errors, I would think I should be able to.  But it was not entirely clear to me in the options above such as BIDS  where you mention:

"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" 

Can I just deploy the package to my fileshare and then follow your BIDS instructions above or will that not work?

Thanks for all the help, just need some clarity.

Wednesday, December 12, 2012 - 7:42:49 AM - Mike Mohr Back To Top (20910)

Thank you for taking the time to share with others!!!

Thursday, October 11, 2012 - 10:46:59 AM - Rajesh Back To Top (19880)

I am having a stored procedure without any parameter and it return 2 rows which is the name of the excel file name.

I have to use this file name and concatenate with the folder path and by using the For each loop i have to export the data from the excel sheet to the destination table.

Can any one help me? Thanks.

 

Tuesday, August 14, 2012 - 5:43:52 AM - himanshu Back To Top (19031)

 

using Microsoft.SqlServer.Dts.Runtime;

this namespace is not exsist in vs2008

Friday, May 4, 2012 - 6:06:11 AM - Jitendra Back To Top (17263)

Hi, I want to open Command window and then specific loation then i want to give and commnad there.

Is it possible to do it using SSIS packages.

 

Regards,

Jitendra

Tuesday, July 7, 2009 - 12:12:11 PM - Wayne Back To Top (3704)

I have some SSIS packages that have to go out and pull information from other servers that are not SQL Servers and use an ODBC style database. Since the database is protected by a password I have created an exe file with the script to run the SIS packages using Task Scheduler. In trying to run them with SQL Server Jobs it would error out all the time due to the password issue so I found this to be a great work around.

Tuesday, July 7, 2009 - 6:08:30 AM - divya Back To Top (3703)

 Just check the code below..

using System;
using Microsoft.SqlServer.Dts.Runtime;

namespace RunFromClientAppCS
{
  class Program
  {
    static void Main(string[ args)
    {
      string pkgLocation;
      Package pkg;
      Application app;
      DTSExecResult pkgResults;

      pkgLocation =
        @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
        @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
      app = new Application();
      pkg = app.LoadPackage(pkgLocation, null);
      pkgResults = pkg.Execute();

      Console.WriteLine(pkgResults.ToString());
      Console.ReadKey();
    }
  }
}
 
For more information have a look at http://msdn.microsoft.com/en-us/library/ms136090.aspx

Tuesday, July 7, 2009 - 2:53:42 AM - mario witdoek Back To Top (3702)

Hello

I know Powershell is .NET related but has someone a class or vb.net code to trigger SSIS packages from .NET? This is because we have already a framework that triggers x amount of actions for our customer at a certain time. We would like to add also SSIS packages.

TIA

mario

Monday, July 6, 2009 - 3:38:01 AM - ChadMiller Back To Top (3694)

You can also use Powershell (or any .NET language) + Microsoft.SqlServer.Dts.Runtime Namespace to execute SSIS packages. Here's a Powershell script:

http://chadwickmiller.spaces.live.com/blog/cns!EA42395138308430!273.entry














get free sql tips
agree to terms