SSIS, dtsx and deployment packages

Question

I'm just trying to understand SSIS packages a bit better and how they are deployed. Correct me I'm wrong but for any deployment, I believe there needs to be at least two files a .SSISDeploymentManifest and a .dtsx. The .SSISDeploymentManifest acts as the equivalent windows installer package which points to the .dtsx. The dtsx is the actual package of "stuff" that is referenced as an external file some how when you run the installer. When you install it, the package gets added to a list of ssis packages for that instance.

My further questions:

• If i wanted to keep previous version of the same package, can I just copy the bin directories with the two above files and keep separately should I need to roll back to a previous package?
• Where are these packages installed to? How does SSIS know where the packagess are?

Answer 1

Correct me I'm wrong but for any deployment, I believe there needs to be at least two files a .SSISDeploymentManifest and a .dtsx. The .SSISDeploymentManifest acts as the equivalent windows installer package which points to the .dtsx. The dtsx is the actual package of "stuff" that is referenced as an external file some how when you run the installer. When you install it, the package gets added to a list of ssis packages for that instance.

Your assumptions are mostly correct. You don't need the deployment manifest, but it can be handy. Also, you don't need to deploy to the SQL Server instance. You have the option to deploy to the file system as well. I'll explain both below.

Regarding your 1st question:

Version Control:

Make sure you're developing and checking in your dtsx packages via visual studio. Label your releases in sourcesafe or whatever version control you're using. If you are checking in and labeling, then you should be able to easily roll back to a previous version. As you mention, you also can just save a copy of your old bin directory but naturally put them in dated subfolders or something. However, this does not take the place of proper version control.

Regarding your 2nd question:

Deployment:

As the other poster states, you first have a decision to make:

a) Deploy packages to the file system b) Deploy packages to MSDB

There are benefits to each, and everyone has their preference. I have used both, but I prefer the filesystem because it's more transparent, however there is more to maintain.

See this post for much more on this: http://blogs.conchango.com/jamiethomson/archive/2006/01/05/SSIS_3A00_-Common-folder-structure.aspx

The code is in the dtsx package. Generally,in order to make your packages portable you also abstract your connection strings and other configurable information into a config file (.dtsconfig) or environment variable (no file needed). See BOL to learn more about configuration.

The manifest file contains metadata about which dtsx and config files to install. If you open one, you'll see it's a simple readable xml file.

The manifest file makes it easy to hand over to a DBA to deploy (ask them to double-click the manifest file and follow directions, but they'll need instructions.

To me, the manifest file is more useful for deploying to SQL Server than to the file system. Really, all it does is make a copy of the dtsx and config files and puts them where you tell it. You could just as easily instruct the DBA to copy your dtsx files to a common folder on the server, and the config files to another folder on the same server.

Then when you schedule your jobs using SQL Agent, you specify that you're going to run an SSIS package that is stored on the file system and browse to where it's located. If you're using configurations, then there's a tab to specify where the config file is located.

There is so much to know about configuring/deployment/versioning of SSIS packages. But hopefully this will get you started on the right path.