Package and MSDTC Transaction Configuration


Once application development is complete, we need
to move deployment packages to the production environment. To simplify the deployment process Microsoft has provides a utility tool called the deployment manifest which
takes care of the package deployment. However, if your package utilizes transactions
we need to take some measures in order to avoid connection
related errors.

This article focuses on creation on
deployment of packages and steps to consider before running a package with
transactions in the production environment.  

How to Create Deployment Manifest File?

In this worked example I have a few  packages in the place which
need to be moved from the development to the production environment therefore I
need to  apply package configurations on all the packages. Now if I want to
create a package configuration file we can create it in following different ways:

XML configuration

Environment variable

Registry Entry

Parent Package variable

SQL Server

Configuration is the most widely mostly used method and the simpliest way to create the
package configuration therefore I will be using this approach for this demonstration. Though
most people also prefer building their package configurations using a SQL
Server table  since we can apply table encryption.

is inherently less secure as it is more vulnerable to the security attacks since the user
name and password to your database are viewable in the XML file.

Setup Package Configuration

the below steps to setup the package configuration:

To begin with applying the package configuration,
right click anywhere in the control flow tab  and click on package
configurations. A package configuration wizard will open which will help you
with the guided steps to create the package configurations for your package.

Click on the enable package configurations

Click on the Add button and click Next.

Select the type of the configuration file and
provide a suitable file name.

Click on the connection strings checkbox in the
connection manager folder. Click Next.

Give a  suitable name for the configuration for
the particular package.

intend to use the same package configuration file throughout the solution, so
that the package connection can be configured in the same file and will be reflected
across all the files at the same time.

  steps 1-4 and browse to the previously stored configuration file. When you
specify the existing configuration file you ewill be prompted whether you want
to overwrite the existing file or reuse the existing file. I will choose the
reuse existing option.

a name for the configuration file and click finish.

Building the Solution

We need to make following changes in the
solution properties  in order to create the deployment utility.

Right Click on the solution and go to

Click on the deployment utility tab on the left
and set the createdeploymentutility property to true. You can also specify the
Deployment output path to a separate folder. This folder is important as the
deployment manifest file along with the required packages in the solution will
be placed in this folder.

Build the solution by right clicking on the
Solution and clicking Build.

4. Go to the Project Folder->bin->Deployment you will see the
required files with the manifest file with the name same as of the project.

Double click on the deployment manifest
file to start the deployment of the packages. a wizard will open to guide you
step by step towards the deployment.

note when deploying the parent/child packages deploy the child first in SQL
Server and then while creating the parent package select SQL Server as the
location of the packages in the Execute Package task. In this way your parent
package will never fail.

Click the do not show start page checkbox and
click Next.

Select SQL Server deployment as it the most
secure deployment. As the packages in the file can be deleted or can
be moved to another location. The packages deployed in the SQL Server reside in
the MSDB database. Click Next.

Provide the target server and the folder for 
deployment  of the packages. I prefer giving the IP address of the server.
Click Next.

Click Next.

Click Next.

Click Next.

Specify the production server and  database
name. Click Next

The wizard will validate the package. Click

Click Finish.

Configure the MSDTC for the Package Running the

If your package has the transactions in
them you will have to start the Distributed Transaction coordinator service
from the services .

To start the services

command->services.msc->Start Distributed Transaction coordinator service
on the Source and the Production servers, in case  if you are building the data
warehouse  and wish to pull the data from the source system.

To run the packages with the transaction
smoothly just configure all the servers (Source and Production) involved
in the packages with the following setting:   

Click Start, click Control Panel, click
Administrative Tools, and then click Component Services. Expand Component
Services, and then expand Computers.

Right-click My Computer, and then Distributed
Transaction coordinator->Local DTC and then click Properties.

Make sure that the following check boxes
are selected:
• Network DTC Access
• Allow Remote Clients
• Allow Remote Administration
• Allow Inbound
• Allow Outbound
• No Authentication Required

Make sure that the DTC Logon Account is set to
NT AUTHORITY\NetworkService.

5.  Add MSDTC.exe as an exception in Windows Firewal to complete the process.


No comments yet... Be the first to leave a reply!