h1

Use BIxPress as your SSIS deployment utility

October 24, 2011

In my previous three posts I detailed the different methods for deploying SSIS packages.  While these methods can get the job done, there are some key functionalities missing.  For instance you can’t deploy package configurations from any of the native tools in SSIS.  This means that after you deploy the package to your QA server you then have to go and move the package configurations to the QA server as well.  BIxPress makes it possible to deploy the package and the configurations just by running through the deployment wizard.  Let’s take a look at the process.

There are three ways to launch the deployment wizard in BIxPress:

    • The BIxPress tab on the Menu Bar

image

    • Right click on a package inside of BIDS

image

    • Launch BIxPress from the start menu and select the Deployment icon

image

The first screen of the wizard lets us configure some basic options in SSIS

image

  1. The package deployment options lets us configure what to do if a package of the same name already exists.  The options are to “Prompt if Exists”, “Always Overwrite” or “Skip if Exists”  The default is to prompt and it is the option I typically choose, because with BIxPress it is possible to deploy more than one package at a time and I’d rather be safe than sorry especially if I’m deploying to a QA environment.   You can also change the CheckPoint folder location here as well.
  2. Another option inside of the Package Deployment Options that we can configure is to Change the Package Protection Level.  This option is turned off by default. You can also have BIxPress copy the folder structure and compile script/task components during deployment.
  3. This is one of my favorite features in BIxPress, the Configuration Deployment Options.  Here you can tell it if you want to deploy the configuration files, what to do if they already exist, “Prompt if Exists”, “Always Overwrite”, “Skip if Exists”,“Prompt if Different”, “Always Different” or “Skip if Different”.  The default is to “Prompt if Different” which again is what I would recommend so that you are not overwriting config files on QA with config files from Dev.  The other really cool thing is the ability to change the package configuration file location.  I’m sure its happened to all of us, but the file structures available on Dev aren’t always available on QA and Prod so the C:\SSISConfig folder you are using on Dev will have to be moved to the E:\SSISConfig location on QA instead.  This can be a real pain without BIxPress.  Every package you deploy to QA you would have to open up and change the location of the config file.  If your deploying one or two, its probably not a very big deal, but when you are deploying dozens of packages, let me tell you it can be a pretty big deal.  With BIxPress you can not only copy the config file to the new location using the wizard, but by telling it the new location it will update the packages you are deploying to use that location as well.  This is a huge time saver. Let me say that again.  This is a HUGE time saver.
  4. Finally you have the option to set credential for the remote file system, either using your current windows credentials or optionally logging in under a different set of credentials to the remote file location.  Please note that you must be a member of the Administrators group on the remote machine to preform file system operations during deployment.

Once you have configured this screen to your satisfaction you can move on by clicking next.

image

On this screen we will configure the more package specific options.

  1. First we must tell BIxPress where our packages are located, either in the File System, on SQL Server or in the SSIS Package Store.
  2. Once the location is identified, we can now select which packages we want to deploy.  I really like this option in BIxPress much better then how it is handled natively.  In the three options I blogged about last week, you were limited in how many packages you could deploy.  Using the Deployment Manifest you had to deploy all of the packages in your project period.  If you chose to import a package into SSIS you had to do it one at a time.  With BIxPress you can choose multiple packages not just on or all, and it can be done across projects.
  3. Now we need to tell BIxPress the destination location, again it can be either in the File System, on SQL Server or in the SSIS Package Store.  I’m using the File system in this example.
  4. Next select the desired location on the file system
  5. Click on the Deploy button.

If you had a package already in that location with the same name then you would be prompted if that is the option you selected.  I think that the big pluses of this deployment method are the ability to deploy the configurations side by side with the packages, the ability to change the path of the configuration file inside the package and finally the ability to deploy multiple packages.  Please download BIxPress and give it a try, you won’t be disappointed.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: