{"id":330,"date":"2014-02-18T14:20:43","date_gmt":"2014-02-18T20:20:43","guid":{"rendered":"https:\/\/jackdonnell.com\/?p=330"},"modified":"2015-12-27T22:26:25","modified_gmt":"2015-12-28T04:26:25","slug":"sql-query-to-script-dtutil-exe-ssis-export-commands","status":"publish","type":"post","link":"https:\/\/jackdonnell.com\/?p=330","title":{"rendered":"SQL Query to script DTUTIL.exe SSIS Export Commands"},"content":{"rendered":"<p>There are several reasons why DBAs\u00a0will need to export\/import SSIS packages for upgrades, development environments and\u00a0backups. \u00a0Many use SSMS to do imports and exports of the dtsx packages. It isgreat unless you need to export several or even all the packages. Microsoft supplies a command line tool <a title=\"SSIS commandline utility dtutil.exe\" href=\"http:\/\/technet.microsoft.com\/en-us\/library\/ms162820.aspx\" target=\"_blank\">dtutil.exe<\/a> to import, export, delete, update and verify SSIS packages.<\/p>\n<p>I wrote the script below to query msdb and script out the dtutil commands. I save the output to text or file. It can be executed\u00a0windows batch file(.cmd or .bat).\u00a0 If your environment uses configuration files(<a title=\"SSIS Configurations\" href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/cc895212.aspx\" target=\"_blank\">.dtsconfig<\/a>), make sure you grab a copy of those, too.<\/p>\n<pre lang=\"tsql\" line=\"1\" escaped=\"true\">USE msdb\r\nGO\r\nSET NOCOUNT ON \r\nGO \r\n-- Scripts  DTUTIL commands \r\n-- Results to Text \r\n-- Script out commands to execute on target server\r\n-- Output to be used as a windows batch file\r\n-- Tested: SQL 2008 R2  and SQL 2012\r\n-- REMOVE  \" ---- \" before saving as a .cmd or .bat file\r\n-- JCD 01\/29\/2013\r\n\r\nDECLARE @folderlocation VARCHAR(300)\r\n\r\n-- Set location of folder to store \r\nSET @folderlocation = 'S:\\SSISPackages' \r\n\r\n--------------------------\r\n--- Do not alter below ---\r\n--------------------------\r\n\r\n-- Add Source Servername to folder structure (ex.C:\\SSISExport\\MyServer\\)\r\nSET @folderlocation = @folderlocation + '\\'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\\'\r\n\r\n--- Create Folder Structure\r\nSELECT  'MKDIR \"'+@folderlocation+'\"'\r\nUNION\r\nSELECT 'MKDIR \"'+ @folderlocation+RTRIM(foldername)+'\"'\r\nfrom [msdb].[dbo].[sysssispackagefolders]\r\n\r\n-- Create DTUtil Commands\r\nselect 'DTUtil \/SQL ' + \r\n'\"\\' + CASE WHEN DATALENGTH(RTRIM(F.foldername)) &gt; 0 THEN RTRIM(F.foldername)+'\\' ELSE '' END +\r\nP.name + '\"' +  ' \/SourceS \"'+CONVERT(VARCHAR(50),ServerProperty('SERVERNAME'))+'\" \/Encrypt  FILE;\"'+@folderlocation+ \r\nCASE WHEN DATALENGTH(RTRIM(F.foldername)) &gt; 0 THEN RTRIM(F.foldername)+'\\' ELSE '' END + P.name + '.dtsx\";0 \/Q'\r\nfrom [msdb].[dbo].[sysssispackages] P inner join [msdb].[dbo].[sysssispackagefolders] F \r\non P.folderid = F.folderid\r\nORDER BY F.foldername\r\n\r\nGO<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Dynamically script out the dtutil commands to export SQL Server Integration Services (SSIS) packages.<\/p>\n<p class=\"read-more\"><a href=\"https:\/\/jackdonnell.com\/?p=330\">Read more &raquo;<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[266,22,290,5],"tags":[303,321,320,315,249,35,36,295,319,52,291],"class_list":["post-330","post","type-post","status-publish","format-standard","hentry","category-dba","category-sql-server","category-ssis","category-t-sql","tag-dba","tag-dtutil","tag-export","tag-query","tag-script","tag-select","tag-sql","tag-sql-server","tag-ssis","tag-ssms","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/330","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=330"}],"version-history":[{"count":11,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/330\/revisions"}],"predecessor-version":[{"id":504,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=\/wp\/v2\/posts\/330\/revisions\/504"}],"wp:attachment":[{"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=330"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=330"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackdonnell.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=330"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}