Archive for October, 2011

h1

Use BIxPress Quick Deploy For Faster SSIS Deployment

October 27, 2011

Over the past few blog entries I’ve discussed multiple ways of deploying SSIS packages, including native and 3rd party methods.  Each one takes you through some sort of wizard to help you select the proper options for the package.  While this is helpful the first time or two you deploy a package to the same server, after a while it tends to be a drag to have to walk through the same steps, and the same configuration over and over. 

BIxPress has a nice feature that eliminates the need to walk through the wizard every time. Its called Quick Deploy.  With Quick Deploy you can single select a package or multi select a group of packages, as long as they are all going to the same location.  Let’s take a look at how it works.

Now let me say right off the bat that you will have to do a little configuring the first time you use Quick Deploy, or if you are using Quick Deploy to deploy to a new environment.  To use Quick Deploy right click on a package and select Quick Deploy SSIS Packages(BIxPress)

image

This will launch a new window.

image

In this new window you can see a dropdown under target location.  Since this is our first time using the Quick Deploy the list is empty.  Lets click on <New Location> and add a location to deploy our packages to.

image

The first thing you have to do with this new window is click the add location button.  I have already done that in this step so it appears grayed out.  Next you need to specify the location type, either File System, SQL Server or SSIS Package Store.  Also give this location a title so you can easily identify it from the drop down from the Quick Deploy window.   Since I’m using the File System as my location type I need to select the path, which I can browse to.  Once all that is done, click okay and it will return you to the original Quick Deploy window.

image

As you can see the drop down that was blank earlier is now populated with the location that we just configured. At this point, if you want to click Start Deploy, you can go right ahead and deploy the package and close the wizard. 

However if you want to use some of the options available from the main wizard for deploying SSIS packages with BIxPress then click the check box next to Use Options From Main Wizard.

image

By clicking on the view settings box next to Use Options From Main Wizard you can see the options from the main wizard.

image

Now if you want to change any of these options you will have to open the main BIxPress SSIS Package Deployment Wizard and change the deployment options there.

image

Click next and leave that window open. Return to the Quick Deploy window and click on the view settings box again and you will see different configurations.

image

With all of that set you can now deploy the package.  The next time you want to deploy the package to that location all you have to do is select that location from the drop down, and click Start Deploy. This is a great way to save some time by not having to run through a wizard time and time again.

Advertisements
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.

h1

Deploying a single SSIS packages from BIDS

October 20, 2011

In my previous posts I detailed the process for using a Deployment Manifest to deploy SSIS packages as well as using the Import package option from Management Studio while logged into the SSIS Server.  In this post I’ll walk you through the steps of deploying a single package right from the BIDS environment.

Lets start by opening up a package inside of BIDS.

image

Once you have created the package and it is ready for deployment click anywhere in the design pane and then File-> Save Copy of <Package Name> as. 

image

This will launch a window for deploying the packages.  From this point forward it is pretty much the same as Importing the package into SSSIS.

From the new menu screen you must first select where the package you want to import is located, either SQL Server, File System or SSIS Package Store.

image

If you are using SQL Server you must next tell it the server name and the type of authentication to use (and username and password if you use SQL Server Authentication), then specify the package in SQL Server. If you are using the file system just specify the package path. You can also set the package protection level here as well.

Click ok and the package has now been deployed. Just as in the Import process please note that this will only deploy the package, so if you have any package configurations you must deploy those separately.

h1

Import Package into SSIS server

October 19, 2011

In my last post I talked about creating a Deployment manifest, so in keeping with the SSIS package deployment them I thought I would cover another method of deploying SSIS Packages.  This method requires you to log into the SSIS server itself.  Lets walk through the steps together.

Open Management Studio and change the option to Integration Services in the Server Type box:

image 

Next you need to set the Server Name property, I’m using my local machine, but yours might be the same as your SQL Server or another server altogether.  Once you have the Server Type and Server Name set then connect to the server.

On the left hand side the object explorer should be visible.  You should see the name of your server and two folders underneath it:

      1. Running Packages
      2. Stored Packages

It is the Stored Packages folder that we are concerned with. If you expand the folder you should see two subfolders

    1. MSDB
    2. File System

These folders indicate where you want to import the packages too, either in the file system or into MSDB.  Right click on the folder or your choice and select “Import Package” (I’m using the file system in this example)

image

From the new menu screen you must first select where the package you want to import is located, either SQL Server, File System or SSIS Package Store.

image

If you are using SQL Server you must next tell it the server name and the type of authentication to use (and username and password if you use SQL Server Authentication), then specify the package in SQL Server.  If you are using the file system just specify the package path.  You can also set the package protection level here as well.

Click ok and the package has now been imported.  Please note that this will only bring in the package, so if you have any package configurations you must deploy those separately.  Next time we’ll cover deploying a package from BIDS using the SaveCopyofPackageAs option.

h1

Creating a Deployment Manifest in SSIS

October 18, 2011

Creating an SSIS package is only one part of the puzzle when it comes to fully automating your data migration applications.  If your company is like many organizations the person developing the SSIS package is not necessarily the person who will be deploying that package into a QA or Production environment.  The question then becomes, how to we manage handing a completed package to the appropriate people when it comes time to deploy?  There are several options for this process, and the one I’m going to focus on today is the Deployment Manifest. 

The Deployment Manifest is not a one size fits all solution to deploying your SSIS packages.  I would say that it is only useful when deploying multiple packages at one time, for example an entire data warehouse ETL solution.  Let me explain.  When you create a Deployment Manifest it is done at the project level, so all of the SSIS packages that exist in that project will be included in the manifest. That can be a problem if your project has any packages that aren’t ready for deployment, or if the packages won’t all be deployed to the same server.  However if you have a contained project, and all the packages are ready for deployment, the manifest is a great way to deploy multiple packages, and the manifest comes with a handy dandy user interface.

Lets quickly walk through the process.

  • Open you SSIS Project and in the Solution Explorer pane right click on the project name and click Properties

image

  • This will open the Project Properties Pages. From there select the Deployment Utility from the page

image

  • Change the CreateDeploymentUtility property to True and click ok.

image

  • Next right click again on the project name and select build from the menu.

image

  • To find your newly created manifest file navigate to your project folder and then inside the project folder to the bin folder and then to the deployment folder. 

image

  • Take the contents of the folder, or just copy the folder to a designated location for the deployment manager to pick them up, or however you plan on getting them the files.  Once there, they just need to double click on the manifest file and it launches a wizard.  They can then walk through the wizard and in the end you will have successfully deployed all the packages in that project.

image

As I said earlier this is not a one size fits all deployment solution, but it does have its place.  I this helps you make the right choice in your deployment options.

h1

Using HASHBYTES() to compare columns

October 15, 2011

Recently, while at a client engagement, I was building some SSIS packages an issue came up where they didn’t want to use the CHECKSUM() function in TSQL to do column comparisons because the results can be inaccurate on some rare occasions.  I personally have never come across this but others here at Pragmatic Works have.  So we have two options freely available to work around this issue.  The first is the third party component plugin that you can get free at codeplex called Multiple Hash.  The client wasn’t comfortable with having to install this component on multiple servers throughout the environment so that option wasn’t available to me.  Instead I had to use the HASHBYTES() function in TSQL

HASHBYTES() is much more reliable than checksum when it comes to producing accurate results, but it comes at a slight cost. 

The first thing to note is how to construct the HASHBYTES() function.  In the first part you tell the function which algorithm you are going to use.  I’m using SHA1, but be aware that they single tics ‘ ‘ are required followed by a comma.  Then you must concatenate the columns you wish to use together as seen below.

 

image

There you can see its already a bit more arduous than using CHECKSUM(), but not that big of a deal to concatenate a bunch of columns.  Lets look at the results.

image

Uh-Oh here is our first problem.  HASHBYTES() doesn’t work with NULL values inside any columns.  So we’ve got to handle the Nulls in our query using the ISNULL() function.

image

Now the results look like this:

image

Then next thing that you have to look at is how HASHBYTES() handles(or more accurately doesn’t handle) Data types.  Here the ID column is an INT data type, but the same holds true for any non-string data type.

image

We get an error saying that the data type is wrong for the HASHBYTES() function

image

So now we have to CAST every column that is a non-string data type.

image

Now after this fix our results look better.

image

So as you can see already there will be a good deal more T-SQL coding involved with using HASHBYTES then with CHECKSUM(). But this isn’t all.  The last little gotcha isn’t quite as obvious as the first two.  Lets go back to our Null handling query.

image

I’ve gone and edited the data some for this example. Please also note that HASHBYTES() is case sensitive meaning that if you have the same spelling but different casing at an individual character level the hash value returned will be different.

image

The rows are different from one another but when concatenated together for the HASBYTES() function they produce the same exact value. So to handle this we have to update our code again.  We are going to add a rarely used character to the concatenation so ensure that they results will return correctly.

image

Here I chose to use a pipe to basically delimit the columns thereby making them different from each other. The result is much better.

image

If we take a quick look at results of the two concatenations we can see why we get the different results

image

The delimited column is obviously different from one row to the next, and the Non-Delimited column is exactly the same for each row.

While HASHBYTES() is definitely more accurate and reliable than using CHECKSUM(), there are some hoops to jump through to insure you get the results you need.  I hope this helps you guys out.