DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System
-
Comments posted to this topic are about the item DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System
-
What would be the syntax for copying all SSIS packages stored in msdb? Is this possible with dtutil? I am having a difficult time finding this.
For example: /sql packagename seems to only be used for one package at a time. I am interested in copying all packages in the msdb store to a fileshare, as I will be rebuilding the server.
Any help on this would be great!
-
i think you would need to use sql loop through the msdb database to create a script which you can then save to a batch file and run that. haven't got time to research it and post the script here, but i have seen it on t'internet.
Life: it twists and turns like a twisty turny thing
-
This was an useful and well written article. Thank you for providing it.
rj_prov (7/9/2008)
What would be the syntax for copying all SSIS packages stored in msdb? Is this possible with dtutil? I am having a difficult time finding this.I have never tried this, but it should be relatively easy to dump all of the names into a table variable and then use that table variable to dynamically generate and execute each command. Depending on how many packages you have, this would be likely to take a while, but it should produce the desired results.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/ -
You need to get the list of packages from by running the following query select * from msdb..sysdtspackages and then dynamically generate the scripts for copying each of the package. Once the syntax is generated you can run it once and copy all SSIS packages at one go..
-
Thanks for the post as i hav been searching for a solution of similar kind.
However, I am left with a doubt after reading this post...
As said by the Author that we can copy the SSIS pkgs deployed into MSDB as Physical SSIS pkg Files onto our local hard drives.. does he mean that those MSDB SSIS pkgs are recreated as original Physical SSIS pkg..that can be later be opened for editing.
I need to know this bcoz..we are planning to upgrade our server from 2005 to 2008..bfore that we planned for backups. So if i copy the MSDB SSIS pkgs thru DTUTIL tool...can i later use those physical pkgs else where...
I Request all to help me in clarifying my doubt ...
Thanks,
Vampire.
--In 'thoughts'...
Lonely Rogue -
it was a nice post by i would like to know how to export a sql package to a SSIS Package Store with DTUTIL...any clues?
-
Here is the query that I ended up using. Run this, then copy the results to a batch file:
--The query gets a list of all package names in msdb and creates a command line.You may have to add quotes around the file and folder names with embedded blanks.
select foldername as FolderName,
[name] as PackageName
into #PackageNames
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders 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
-
Hi everyone I am trying to obtain some extra information from my ssis packages but I am not getting anywhere. I am using two tables to obtain some information from them. The tables are:
[msdb].[dbo].[sysdtspackages90] and[msdb].[dbo].[sysdtspackagefolders90]
But I have found this post that says that the tables sysssispackages and sysssispackagefolders exist, which version of sql server are you guys using? Any opinion is welcome. Thanks!!!!
rj_prov (9/24/2008)
Here is the query that I ended up using. Run this, then copy the results to a batch file:....
....
from msdb.dbo.sysssispackages pkg
join msdb.dbo.sysssispackagefolders fld
...
...
-
That script is meant for KATMAI ( SQL 2008 ).
Cheers,
[font="Comic Sans MS"] Vampire[/font]
--In 'thoughts'...
Lonely Rogue -
Vampire (1/15/2009)
@chileu17That 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!
-
Vampire (1/15/2009)
@chileu17That 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!
-
Admin- my browser was hanging and it looks like this got posted multiple times...
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply