Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  Transact-SQL (2000)  extracting text from DTS packages (DTS hell)

Author  Topic  AlanS Starting Member 28 Posts Posted - 2007-09-05 : 15:42:47 I've been assigned the task of building a system that will search the text elements of SQL Server 2000 DTS packages for user-specified strings. The interface needs to be entirely within SQL Server itself (i.e., Enterprise Manager and Query Analyzer only), and all the coding must be in T-SQL only. Since there are many DTS packages, and packages are being added and edited more or less constantly, the solution must either directly search all of the current package versions, OR it must automate the process of extracting the text from them to some other format that can be directly searched. Most of this seemed pretty straightforward at first, but I've spent several days in extremely frustrating (and so far unsuccessful) research on how to extract the text elements from DTS packages. I'm hoping someone here knows how to do it. I've seen several scripts that extract or search DTS packages, but they're all in VB or VBScript, which I'm prohibited from using. I tried to read the text directly from where it normally resides (msdb.sysdtspackages.packagedata), but I cannot figure out how to extract the text from that field, whose data type is "image". I've also seen a T-SQL script (http://www.dbazine.com/sql/sql-articles/larsen8) which automates the process of saving DTS packages to another server, but that uses one of the SaveAs... methods of the Package automation object. I was hoping to adapt that to save DTS packages as VB files, which are text files that can be directly searched. In Enterprise Manager, you can manually save a single DTS package in VB format, but there is no SaveAs... method for that format or, apparently, any other format where the text might appear unscrambled. Does anyone have a solution to this, or has Microsoft once again succeeded in rendering what should be a simple operation into something that is incredibly tedious, if not impossible? Thanks in advance for any insights.