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.
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.
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.
It is a bit unfortunate that some of us today still need to work with DTS packages. When confronted with problems from using the antique toolset, the first thing that one does naturally was to curse the longevity of old technologies.
I have encountered one such problem today working with a simple DTS package. No matter how much I wished for SSIS for my next Christmas present, it was not December. At the end of the day, the problem still needed to be addressed in the DTS package.
The package had a transform data task which loaded a file into a database. I was happily clicking around until I ended up at the destination tab and stopped by the dreadful dropdown box. It was not wide enough to show the full table names, all I could see was the first few characters of the table names. How would I know which table I have selected?
I was pretty sure there must be a way to get around it, after all DTS packages have been around for yonks, someone must have seen this behaviour and reported it. After searching on Google for 2 minutes, I happened to stumble across this little gem. I couldn’t believe my luck, the solution was dead simple but who would’ve guessed. All I had to do was to move the DTS designer window to the primary monitor!