Post reply

Need to identify all DTS packages using a specific filename

  • May 16, 2006 at 6:55 am

    #113027

    Need to identify all DTS packages using file name: 'MyFile.xls'   I tried querying MSDB:

    select

     * from sysdtspackages where packagedata like '%MyFile.xls%'  and received error:

    Msg 403, Level 16, State 2, Line 1;  Invalid operator for data type. Operator equals LIKE, type equals varchar.

    (packagedata is a TEXT column) -- Any suggestions for retrieving all Pkg's containing reference to a given filename?

    BT

  • May 17, 2006 at 6:06 am

    #638381

    I'm not very familiar with the way DTS packages are stored in the table but I know they can be saved to a VB file which is plain text. You could save them as VB to a specific folder and use a GREP tool (XP and server 2k3 have QGREP) to find each package that contains the search string.

    Hope this helps.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • May 17, 2006 at 9:08 am

    #638455

    MG is correct, the only way to search a DTS package for a string is to save it as a VB file.  BTW, packagedata is IMAGE datatype, which is binary.  That's why 'LIKE' wouldn't work.

    Greg

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply