Introduction
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:
1. XML configuration
2. Environment variable
3. Registry Entry
4. Parent Package variable
5. SQL Server
XML 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.
XML 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
Follow the below steps to setup the package configuration:
1. 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.
2. Click on the enable package configurations
3. Click on the Add button and click Next.
4. Select the type of the configuration file and provide a suitable file name.
5. Click on the connection strings checkbox in the connection manager folder. Click Next.
6. Give a suitable name for the configuration for the particular package.
I 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.
Follow 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.
Provide 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.
1. Right Click on the solution and go to properties.
2. 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.
3. 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.
Please 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.
1. Click the do not show start page checkbox and click Next.
2. 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.
3. Provide the target server and the folder for deployment of the packages. I prefer giving the IP address of the server. Click Next.
4. Click Next.
5. Click Next.
6. Click Next.
7. Specify the production server and database name. Click Next
8. The wizard will validate the package. Click Next.
9. Click Finish.
Configure the MSDTC for the Package Running the Transactions
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
Start->Run 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:
1. Click Start, click Control Panel, click Administrative Tools, and then click Component Services. Expand Component Services, and then expand Computers.
2. Right-click My Computer, and then Distributed Transaction coordinator->Local DTC and then click Properties.
3.
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
4. 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.
]]>