Problem
One of the most awaited features for SSIS developers is an undo functionality in the package designer of BIDS.
If you make any changes to the package, whether it be a logical change or a formatting change, once it’s done there is no way to undo the changes. Also if you are in the process of making changes and you have not saved the package, when you execute the package, without any prompt, BIDS will save the package and the changes are committed to the file. This means your previous changes are wiped out.
In this tip we will discuss a workaround that will equip you with an easy solution to achieve an undo functionality in BIDS without a time consuming process.
Solution
In order to derive the solution for the problem in question, we need to understand the behaviour of the BIDS Package Designer. For the scope of discussion of this article, we will use the same package that we used in the NULL defense series tips. You are free to use any package that you have handy or you can create a new package and put together a few controls, as our motive is to test the undo functionality.
Below is the screenshot of how my package looks.
If you look carefully, you will find a “*” beside the name of the file in the tab header. Keep your focus on this as we progress with our discussion.
As you may know, a SSIS Package file (.dtsx) is composed of structured XML. From the solution explorer, right-click on the package and select “View Code” to see the XML of the package that get’s created when we design and develop the package graphically using the package designer. Look carefully and you will find that the tab which shows this file also shows the “*” beside the name of the file indicating that the file has changed.
Modify your package and mess up the data paths in your data-flow and try to find a way to undo the change. The “Undo” option in the “Edit” menu is not active and the only option to escape from these changes is to close the package without saving and re-opening it again. But this is not a wise solution when you are making huge changes across the package and just due to one incorrect change you would have to loose all of your changes you made to your package.
Generally the concept behind the UNDO functionality is recording the changes made and reversing / removing those changes in the reverse chronological order. The changes made in the graphical designer environment make changes to the XML code behind the scenes. So the XML of the package keeps changing with each change.
Before making more changes, we would like to revert back to this point after we make a change, so let’s take a snapshot of the XML of the package. To do this add a new file to your solution from the “File” menu by selecting “New File” -> “XML File” as shown in the below screenshot.
After the file has been added, copy the entire code from the code window of your package and paste it into this new XML file. I have collapsed the XML code to make it easier to read.
Make some changes to the package and execute this changed package. I modified my package, executed the same and it looks like the below screenshot. The biggest problem that we face is that we do not have undo feature and as this package has been executed, package designer saves this package without prompting the user. So this would effectively mean that if you are just in the process of experimenting with some changes and you execute the package by mistake, your changes are committed.
Many developers employ the process of taking a backup of the file before starting any changes, but this leads to creating a huge number of backups of the file, which can be seen as a loose version of version control. Also it is a time consuming and confusing process (in my view) compared to the solution we are going to implement. And even if you are using some source control tool like TFS to use it as a means to facilitate reverting back changes, you would have to make a check-in of the file for every small change you make, which would not be a good idea from a source control policy viewpoint.
Go back to the XML file where we copied our entire code, copy this code, select the entire code from the Code window of the package and replace it with the code from the XML file. Save the file and open the designer tab of the package where you will find the focus moved to the Control Flow. Open the Data Flow and you should be able to find the package intact before we started messing up the package as can be seen in the screenshot below.
One can create as many level of undo’s required by creating that many number of XML files and taking a snapshot of XML of the package at different points. The advantage of taking a snapshot of XML over taking a backup of entire package is that one is not required to create a huge set of files as a backup and reverting back a change in the same package is very easy compared to discarding the changed package and using the package file from the set of backup files. After the changes are made to a milestone level, the package can be checked-in to a version control tool like TFS.
I hope that this article provides a workaround to the long awaited UNDO feature in SSIS Package Designer. I have created a Connect item which you can vote on, to request Microsoft for a list of such features that I feel should be added to BIDS and SSMS and the explanation of the list can be read here.
Next Steps
- Implement this technique along with creating backup files to compare which technique is faster, easier and efficient to implement for achieving undo functionality.
- Create multiple undo levels by taking multiple snapshots of your package.
