Pragmatic Works

Enabling your business intelligence enterprise.
Welcome to Pragmatic Works Sign in | Join | Help
in Search

Brian Knight

Renumbering Package IDs in an Automated Fashion

I'm now a huge advocate of using package templates. For more info on how to use them see this post. Template provide a handy way to determine the best practices for your company and reuse those over and over again. Well there's one slight problem with using them. The package that uses the template inherits the PackageID of the template. Why is this a problem? Well, if you're using the System::PackageID variable for logging, all of your packages now will report that they are the template package.

So, you have two options. Ultimately, you must renumber each package to a unique value. You can do this manually in the package Properties pane. You can also do this in an automated method by using the dtutil.exe application and the /I switch as shown below:

dtutil.exe /I /FILE "PackageName.dtsx"

The best way I feel is to create a batch file with the following code that will loop through every package in a directory and renumber it:

for %%f in (*.dtsx) do dtutil.exe /I /FILE "%%f"

Please note though that once you use DTSutil.exe to do this, it will re-arrange your package. Don't worry, your package will still work but the appearance may be a bit rearranged.

Note: You can vote to change the behavior of SSIS here to fix that problem: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=241901

Published Mar 12 2008, 09:04 AM by Brian Knight
Filed under:

Comments

 

jamiet said:

Hey Brian,

"This behavior will be fixed in SQL Server 2008."

What's the fix exactly?

This technique (i.e. using dtutil.exe) is useful but still doesn't solve the problem of tasks/containers in packages created from the same template having the same IDs.

By the way, how about producing that fix as a Powershell cmdlet?

cheers

-Jamie

March 12, 2008 8:37 AM
 

Brian Knight said:

I went back in my old emails and had one where they were supposed to have this addressed but in looking at this link: connect.microsoft.com/.../ViewFeedback.aspx

I don't see the Resolved Flag on it so that problem fix is still at risk I would assume :(.

March 12, 2008 9:32 AM
 

stevefromoz said:

Hey Brian, long time no talk.

Was mucking around with the SSIS template idea you had, got a workaround /alternative that actually populates the unique ID, saves you having to reset them at all.   It does require you to perform an extra step in creating the template (create a *.vstemplate file also) but once you do that, it's available in the teplates list and fires up with a spankin new id every time.

PM me over on SSC.com and i'll shoot the sample files thru.

March 13, 2008 11:10 PM
Powered by Community Server (Non-Commercial Edition), by Telligent Systems