August 10, 2015 at 9:28 am
In SQL Server 2008 R2, msdb contains a folder hierarchy for SSIS packages. You can see the tables and some procs that are apparently undocumented (at least I can't find the docs).
What I'm looking for is an easy way to script out the folder creation. I will use this to recreate the hierarchy on a second server.
Any clues/tips/scripts for doing this?
Gerald Britton, Pluralsight courses
August 11, 2015 at 1:29 pm
Here's what I did. May not be the best approach but it works:
SELECT 'EXEC dbo.[sp_ssis_addfolder] ''00000000-0000-0000-0000-000000000000'', N'''
+ [foldername] + ''';' + char(10) + char(13)
FROM [dbo].[sysssispackagefolders]
WHERE foldername <> ''
AND parentfolderid = '00000000-0000-0000-0000-000000000000'
FOR XML PATH('')
GO
Gerald Britton, Pluralsight courses
August 1, 2016 at 10:25 am
Here is some T-SQL to script the folders creation.
It takes care of the hierarchy, but please note that the folders named 'Data Collector' and 'Maintenance Plans' are excluded.
WITH
ssis_folder([folderid], [parentfolderid], [foldername],[level])
AS
(
SELECT
[folderid]
,[parentfolderid]
,[foldername]
,1 AS [level]
FROM
msdb.dbo.sysssispackagefolders c1
WHERE
[foldername] NOT IN ('Data Collector','Maintenance Plans')
AND
[parentfolderid] = '00000000-0000-0000-0000-000000000000'
UNION ALL
SELECT
child_folder.[folderid]
,child_folder.[parentfolderid]
,child_folder.[foldername]
,parent_folder.[Level] + 1 AS [level]
FROM
msdb.dbo.sysssispackagefolders child_folder
INNER JOIN ssis_folder parent_folder
ON child_folder.[parentfolderid] = parent_folder.[folderid]
)
SELECT
'EXEC [msdb].[dbo].[sp_ssis_addfolder] @parentfolderid='''
+ CONVERT(nvarchar(50),sf.[parentfolderid])
+ ''', @name = '''
+ sf.[foldername]
+ ''', @folderid = '''
+ CONVERT(nvarchar(50),sf.[folderid])
+ ''';'
+ char(13)
+ char(10)
FROM
ssis_folder sf
FOR XML PATH('');
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply