By: Siddharth Mehta | Comments (3) | Related: More > Integration Services Development
Problem
The primary way to develop SSIS packages is by using Business Intelligence Development Studio (BIDS). The logic developed in an SSIS Package is not only the transformation logic, but also an algorithm blueprint that an analyst might provide to the developer in the form of a Technical Specification Document (TSD).
There are two problems that we will discuss:
1) Firstly if we have a lot, more than 50, tasks and/or transforms in a package and these are placed in a hasty manner on the design layout of the package, it becomes really tough to figure out which component is where in the package.
2) If one wants a blueprint of the package logic, there is no way to export a visual of the Data Flow or Control Flow into Visio or PDF format.
In this tip we would discuss a solution that will solve both these problems.
Solution
Firstly, we start looking at our primary problem of facilitating a search feature for our package. To see the real need for this search feature, we need a very messy package with lots of transforms in it. Fortunately, we do need not to design such a package, because one is already available for use from Microsoft that ships with Microsoft Samples.
This package can be found at the following path in a typical SSIS installation (in my case I have installed SSIS 2008 R2 version, so the path is based on that): "C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Package Samples\AWDataWarehouseRefresh\AWDataWarehouseRefresh\bin". This package is called AdventureWorks Datawarehouse Refresh package.
If you open this package from BIDS, you will see why I selected this package, as this suits us best for the feature that we are going to facilitate for this package. See the screenshot below and try to think of how easy or difficult it can be to find all the transforms that contain the word "Dim" for example in the entire package.
After going through the above scary screenshot, let's try to speed up the pace towards our solution. The first option is to use the Find feature that is available in BIDS, but that would just list search results in the results window and clicking on any of those would not move your focus on the tasks or transforms for which the result is displayed. Also it would fetch the search results from the XML of the package, so it would list everything it found with the word we provided for our search, including properties, name and others. Our intention is to find the control based on it's name.
Now we come to the first way that would give us what we are looking for, though it's not the solution. Go to the Properties window and select any transform that has the word "Dim" in it and the focus will move to that particular transform which you selected in the properties window. This is the exact functionality we are looking for, but not limited to a control by control selection. We should be able to provide a word to find and then it should go find the results for us.
Finally, we come to our solution. The first step towards this solution is to have a utility that can "Print to PDF". There are many freeware tools available you can find on the web, which can convert your document into a PDF format document. For the purpose of this demonstration, I chose a freeware utility called "DoPDF", which is a freeware tool to Print to PDF. If you already have such a utility installed on your machine you can ignore this step, else download it from here and install it.
After the installation is complete, keep the DataFlow tab of your package open, and select the "Print" option. If this PDF utility is installed on your machine, you should be able to find "doPDF" as a printer. Select it and specify settings as shown in the figure below to print the DataFlow to a PDF document. After this is done, open the document and it should look like this PDF which you can view here.
After opening the document in your PDF Reader, open the Search Window, which should look like the figure below. I have used "Dim" as the search keyword and for experimenting the capabilities further, I have also selected the option of CaseSensitive search as True.
The search results should look something like the below figure and when you select any particular result, the focus would be moved to the area where the text appears. The interesting feature in the process of exporting to PDF is the entire visual is not converted to an image, the text is still preserved in the document as text which helps searching of the document.
Needless to say, but apparently our next problem of export is automatically solved. Actually this is the by-product of the solution that we devised for our first problem. An SSIS Developer can hand-over this PDF document to the technical design analyst and the analyst should be able to determine the logical algorithm from this blueprint of logic implementation. This is not the best solution for searching for components as you would always have to work with BIDS and this PDF, but until we have this feature in BIDS it's a very handy way to search for components quickly and the only time wasted is switching between two windows which should be tolerable considering the value it brings to the development and debugging process.
Next Steps
- Download and install any Print to PDF utility on your machine.
- Pick any of your most complex packages and try to print the control flow and data flow to a PDF document.
- Search for different tasks or transforms and record the search results.
About the author
Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
