Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

SSIS Package Copy/Paste New GUID (ID) Fix

Posted by denglishbi on March 25, 2011

When doing development with Integration Services (SSIS) in Business Intelligence Development Studio (BIDS) it is quite common to copy an existing SSIS package to begin work on a new one.  This could be because you have a package template that you are using in your environment or possibly just to back up your package because you want to make a modification and are not using a source control system (definitely not a best practice to not be using a source control system).

The one thing you need to be aware of at least when performing a copy/paste of an existing package in BIDS 2005 and 2008 is that each package is setup with a unique identifier known as a GUID.

SSIS_BIDS2008_PackageGUID

When you copy and paste the existing package to begin working on a new version of the package the cloned version of the package will keep all of the same values of the template copy of the package.

image

This can become very problematic when you have SSIS logging enabled and are trying to troubleshoot a package and all of your packages have the same GUID value assigned to them.  So in BIDS 2005 and 2008 the way to get around this problem was to use the drop-down list in the ID property to generate a new ID (GUID) value to the copied (cloned) version of the master package.

image

NOTE: Doing this only assigns a new ID (GUID) value to the package.  All of the other items in the package will all still have the same ID values as the template package

The other option you have in changing the ID values is to leverage the BIDSHelper add-in that is available on CodePlex.  I had forgotten about this option and a special thanks goes out to Bhavik Merchant (Blog | Twitter) for reminding me about this through Twitter land yesterday.

image

The nice thing about doing this with the BIDSHelper add-in is that it will not only assign the package a new ID value, but also all of the other items within the package!

image

This is a great feature because if you have the setup like I did above with a Data Flow Task and are going to use Data Viewers you might have run into this issue before – SSIS package copy paste data flow task (dft) data viewer issue.   This bug is in BIDS 2005 and 2008 (has been fixed in 2008 R2 though).  When you add a Data Viewer into your cloned package and then go back to your template package you will see that the Data Viewer exists in both!  Wow, what a cool feature and it really freaks you out when you run your cloned package and get all of these popups, especially if you have 10 or 15 cloned packages in a single project.  Really neat, NOT.

Cloned Package 2

image

Template Package

image

But if you are using the BIDSHelper add-in this would resolve the issue because it assigns new ID values to all of the items, so this would not be an issue.

Cloned Package 1

image

Lets fast forward now into BIDS 2008 R2.  If you copy and paste a package in BIDS 2008 R2 the new package automatically gets assigned a new ID (GUID) value.  So the issue of having multiple packages with the same ID values has been resolved out-of-the-box with no additional actions required by the developer.  A nice little feature that I experienced yesterday and noticed.  I had still been using the generate new ID option without even realizing the copied package already had a new ID value. 

BIDS 2008 R2

Template Package

image

Cloned Package

image

The trick is that you have to actually rename the package and it will assign a new ID value to the copied package.

This doesn’t assign new values to the other items, but the issue that you experience with the Data Flow Task and Data Viewers is fixed in BIDS 2008 R2.

Cloned Package

image

Template Package

image

So with BIDS 2008 R2 you get new ID values for each copied SSIS package and the issue with using Data Viewers in copied Data Flow Tasks has been fixed as well.  You might still want to leverage the functionality of the BIDSHelper though to assign all items in the package a new GUID (ID) value.

Pretty cool and I didn’t even realize this was updated in BIDS 2008 R2.

UPDATE (4/11/2011): Acutally realized that this is fixed in BIDS 2008 as well.  The reason I didn’t notice it before is that because you need to actually rename the package file after the copy/paste.  Once you do this it will provide the package a new GUID (ID) value.

About these ads

5 Responses to “SSIS Package Copy/Paste New GUID (ID) Fix”

  1. Chris Teter said

    Just a heads up, copying and pasting objects in 2005/2008 with the same ID does more than just mess with data viewers. If you are ever executing a package that calls another package and both have a container or object with one of the duplicate IDs, it will execute them both at the same time. I have a package where a logging container was executed, then another package was called in a subsequent container. The second package had the same logging container at the start, and it ended up throwing an error in the first (calling) package in the logging container even though the control flow had already passed that point.

    I used the BIDS Helper recommended in the post, and it corrected the problem.

    • denglishbi said

      Thanks for the heads up. I ran into another funny experience the other day. While I was running a package I copied it to work on a new one. While I was working on the new one (and the other one was still running in BIDS) the data flow task in the cloned (copied) version started up. I was startled and was wondering what was going on. I realized that I was stuck in a jam at that point because now I had two data flow tasks doing the exact same thing and I didn’t want to stop it at all because it was loading a ton of data. When it was all over I didn’t have any duplicate records and I was left scratching my head. What I did probably was not a good idea, but you never know what an end-user will do once you put the tools at their disposal:)

  2. theSuda said

    Thanks.. I just knew that we need a new GUID, this post explained ‘why’ in simple way :) Thanks..

  3. [...] is a similar blog post on Dan English’s site. Share this:TwitterFacebookLike this:LikeBe the first to like this post. Categories: SQL Server [...]

  4. [...] for easy access. Also, when you are using your template for a new package you must generate a new GUID, otherwise SQL Server will be unable to differentiate between your new package and other packages [...]

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

 
Follow

Get every new post delivered to your Inbox.

Join 90 other followers

%d bloggers like this: