SQL Server Integrated Services Package Deployment

Category: SQL Server

SQL 2005: SQL Server Integration Services
Package Deployment

By: Zack Turnbow

Introduction

A decent amount of material has been covered in this introductory series on SQL Server 2005 Integration Services. Many of the basic data flow tasks have been covered along with some database maintenance tasks. It is now time to put these techniques into use.

I just signed up at Server Intellect and couldn't be more pleased with my Windows Server! Check it out and see for yourself.

Scenario

The package development is now complete and it is time to move the package into a working environment whether it is a testing environment or a production environment. Using some file configurations settings and SQL Management Studio, the developed packages will be deployed to a SQL server.

Implementation

If you have worked all the way through the series, you will have several packages to import. All though they each build on top of one another, this will give a good example on how to deploy several packages that are under the same SSIS project. So the first issue is how SQL server finds dtsx files. There are numerous ways but one that I prefer is to add some settings to the integration services configuration file to tell SQL server where to look for dtsx packages. Go ahead and open up Windows Explorer and navigate to C:\Program Files\Microsoft SQL Server\90\DTS\Bin.


[Click to see full-size]

Then open up MsDtsSrvr.ini in your favorite text editor add a meaningful name denoting the types of packages are contained in the folder and also add the physical folder path to where the dtsx files are located. For this example, Dev is the name and C:\SSIS is the folder path of where the files are stored. The completed changes should look like the highlighted area in the following screen shot.

Server Intellect offers Windows Hosting Dedicated Servers at affordable prices. I'm very pleased!


[Click to see full-size]

To make the changes go into effect, the SQL Integration Services process needs to be restarted. Open up SQL Configuration Manager and restart the SQL Integration Services process.


[Click to see full-size]

After the process has been restarted, it is time to open SQL Server Management Studio. From there click the connect button under Object Explorer and select Integration Services.

Try Server Intellect for Windows Server Hosting. Quality and Quantity!


[Click to see full-size]

To see the added packages, expand the Stored Packages tree and a folder with the name that was given will be found. Expand that folder to view all of the dtsx packages in that folder.


[Click to see full-size]

To verify the imported packaged work, a package needs to run. Since the last working package that worked with data was the BadData package will be ran. Right click on the BadData package and select Run Package. A dialog box will appear to give some options on running the package.

Server Intellect assists companies of all sizes with their hosting needs by offering fully configured server solutions coupled with proactive server management services. Server Intellect specializes in providing complete internet-ready server solutions backed by their expert 24/365 proactive support team.


[Click to see full-size]

Keep the default values and press the Execute button. When the package is executed, another window will appear to show the log of the package executing. It is in this window that any errors or warnings will be seen.


[Click to see full-size]

Once complete, go ahead and close both of the windows and the task of importing developed packages into SQL Server is complete.

If you're looking for a really good web host, try Server Intellect - we found the setup procedure and control panel, very easy to adapt to and their IT team is awesome!

What have we learned?

How to import dtsx packages into SQL Server 2005
How to manipulate the integration services configuration file to tell SQL Server where to look for valid SSIS packages.
How to verify if an imported package works or not.