Home Forums Article Discussions Article Discussions by Author Discuss content posted by Ashish Kumar Mehta DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System RE: DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System

  • March 25, 2009 at 9:06 am

    #966029

    Vampire (1/15/2009)

    @chileu17

    That script is meant for KATMAI ( SQL 2008 ).

    Cheers,

    [font="Comic Sans MS"] Vampire[/font]

    Yes, I posted the wrong version for SQL 2005- sorry for the confusion.

    Here is the SQL 2005 version:

    SELECT foldername AS FolderName,

    [name] AS PackageName

    INTO #PackageNames

    FROM msdb.dbo.sysdtspackages90 pkg

    JOIN msdb.dbo.sysdtspackagefolders90 fld ON pkg.folderid = fld.folderid

    ORDER BY FolderName,

    PackageName

    SELECT 'dtutil /SQL ' + '"' + CASE WHEN LEN(FolderName) = 0 THEN ''

    ELSE FolderName + '\'

    END + PackageName + '"' + ' /COPY FILE;"C:\OutputFolder\'

    + CASE WHEN LEN(FolderName) = 0 THEN ''

    ELSE FolderName + '\'

    END + PackageName + '.dtsx' + '"' + ' /QUIET' --suppresses prompt to delete if an older file with same name exists

    FROM #PackageNames

    DROP TABLE #PackageNames

    -----------------------------------------------

    I have added quotes to the folder paths, as you will need these if your paths contain spaces. Also, special thanks to Mike Condon for providing me with this script!