A solution for searching inside DTS packages – Part 2 / 2

In my previous blog post, I had talked about finding a script to be used in my search solution. Once you have that, the rest of the puzzle just falls into place.

I created an SSIS package to call the VBScript and then load the output files into a SQL database.

ScriptDTSPackages

As depicted in the screenshot above, the solution basically consisted of two loops. One for iterating through the DTS packages on the file system, another for importing the output files into a SQL database.

Due to the simplicity of the solution, I won’t go into details showing how the whole thing was built. I do however have a few tips for you if you are considering building one to suit your purpose.

1. In the Execute Process Task, wrap the VBScript inside a batch file:
cscript //nologo “C:\Script_Single_DTS_Package.vbs” “/i:%1” “/o:%2”

2. Use a Flat File source for the file. The content of the file should all be loaded into a single column.

I used SSIS because that’s available, the same solution could’ve been implemented entirely in a scripting language such as Powershell or better still using a good old DTS package to complete the cycle. Winking smile

A solution for searching inside DTS packages – Part 1 / 2

Being able to search through all your source code for impact analysis is nice when you are undergoing database refactoring, it is especially important if not all your code is wrapped up in stored procedures.

As I was working extensively with (hundreds of) DTS packages at one stage, it was not always feasible to open up each package and inspect the SQL statements within, a more efficient approach was obviously required.

It didn’t take me long to find a script which would become the core component of my solution. All I needed to do was to modify the script so it could work with DTS packages that were stored on the file system. In addition, I went one step further and loaded all the text output into a database and in the end I had effectively built a sys.sql_modules table for DTS packages.

My script can be found here. In my next blog post, I will show you how I put all the pieces together and formed a working solution.