SQL Server Integration Services (SSIS) – Part 6 – Options to execute a package in SSIS

Karthikanbarasan
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 14379 red flag
Rating: 5 out of 5  
 1 vote(s)

This is part 6 of the series of article on SSIS. In this article we shall see how to deplay a SSIS package.

Introduction

In this article we will see on how to deploy a package once we are done with creating and building the package. Look around in to my previous articles on how to create, build and execute a package using SSIS at the below url’s.

Part 1

http://www.dotnetfunda.com/articles/article1164-sql-server-integration-services-ssis-part-1-basics-of-ssis-and-creati-.aspx

Part 2

http://www.dotnetfunda.com/articles/article1189-sql-server-integration-services-ssis-part-2-transforming-sql-data-to-.aspx

Part 3

http://www.dotnetfunda.com/articles/article1190-sql-server-integration-services-ssis-part-3-export-data-using-wizard-.aspx

Part 4

http://www.dotnetfunda.com/articles/article1191-sql-server-integration-services-ssis-part-4-import-data-using-wizard-.aspx

Part 5

http://www.dotnetfunda.com/articles/article1193-sql-server-integration-services-ssis-part-5-building-and-executing-a-.aspx


Approaches:

Once a package is created and build it successfully, we have 3 options to make a deployment. We will look into each approach with an example.

Approach 1:

DTEXEC command line utility

SQL Server provides a command line utility (DTEXEC.EXE) that helps the developers to execute the SSIS package. It can be directly used from the command prompt by moving around to the folder where the package is available and executing making use of this EXE.

DTEXEC /? Provides the list of available options to execute the package from the command prompt as shown in the below screen


So to execute the package go to that folder where the package is available and provide the syntax as shown in the below screen


This is the result once we execute a package in SSIS Command line utility. This example shows an error that package is not executed properly and it has some errors which need to be fixed.

For more details on DTEXEC utility refer to the below msdn article

http://msdn.microsoft.com/en-us/library/ms162810%28SQL.90%29.aspx

Approach 2:

SSIS Package Windows Application

This approach is straight forward a user interface option to execute the package. Microsoft has provided a user interface or we can say a tool kind of option to execute the SSIS packages. DTEXECUI.EXE is the User interface exe which performs the task of executing the package.

We can launch DTEXECUI.EXE by double clicking on the package itself directly (i.e. go to project folder and double click on *.dtsx file). It will open the graphical user interface as shown below.


As we can see there are many options available in order to execute the package based on our needs. If we want to follow the standard format then directly clicking on EXECUTE button at the bottom will do the task. We can navigate through each option and customize the package based on our needs.

You can have a look at the msdn article on DTEXECUI.EXE utility at the below url

http://msdn.microsoft.com/en-us/library/ms141707%28SQL.90%29.aspx

Approach 3:

SQL Server Agent Scheduling

The last and the final approach to execute the SSIS packages are the SQL Server Integration services Job step type which helps to schedule the package and execute it based on our needs. This is one of the easiest approaches since we have the UI to schedule the package and execute it without any user interactions.

In order to do these approaches go to SQL Server Management Studio à Connect to the Database using the credentials à open object explorer and go to JOBS à Select New Job and fill the details based on our needs




Conclusion

In this article we have seen the different options to execute the SSIS packages. We will look into the deployment options in the upcoming articles.

Page copy protected against web site content infringement by Copyscape

About the Author

Karthikanbarasan
Full Name: Karthikeyan Anbarasan
Member Level: Silver
Member Status: Member,Moderator,Microsoft_MVP,MVP
Member Since: 1/2/2011 3:00:14 AM
Country: India
Thanks Karthik www.f5Debug.net
www.f5Debug.net
www.f5Debug.net Around 5 years of experience in Microsoft technologies like ASP.Net, VB.Net, C#.net, SQL server, Biztalk Server, WCF, WPF, SSIS, SSRS, SSAS, AJAX and working as Senior Analyst in a US based MNC.

Login to vote for this post.

Comments or Responses

Posted by: Tripati_tutu on: 2/21/2011 | Points: 25
This series of article is really helpful.
Posted by: Karthikanbarasan on: 2/21/2011 | Points: 25
Thanks Tripati_tutu !!!

Login to post response

Comment using Facebook(Author doesn't get notification)