tag:blogger.com,1999:blog-4583365003930185416.post5594360470146872446..comments2024-01-29T01:48:06.546-06:00Comments on World of Whatever: SSIS Package Extract from MSDBBill Fellowshttp://www.blogger.com/profile/09363163555016521189noreply@blogger.comBlogger8125tag:blogger.com,1999:blog-4583365003930185416.post-24269101025760942292016-07-14T16:05:35.895-05:002016-07-14T16:05:35.895-05:00Hi Dan,
As I understand it, dtutil.exe is part of...Hi Dan,<br /><br />As I understand it, dtutil.exe is part of the Integration Services Service installation for SQL Server. The executables (dtutil, dtexec) perform a licensing check to verify they can run. The <a href="https://msdn.microsoft.com/en-us/library/cc645993(v=sql.120).aspx" rel="nofollow">2014 feature page</a> specifies that SSIS only works with Enterprise/BI/Standard which is why your export is failing.<br /><br />Best you can do is recreate the maintenance plans on the target servers or look at alternatives for your backup/defrag etc. <a href="https://ola.hallengren.com/" rel="nofollow">Ola's Scripts</a> or <a href="http://minionware.net/" rel="nofollow">MinonWare</a> are both solid, free offerings.Bill Fellowshttps://www.blogger.com/profile/09363163555016521189noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-1973789238152068092016-07-14T15:54:35.919-05:002016-07-14T15:54:35.919-05:00When I run the script I get back my packages and t...When I run the script I get back my packages and the one I am trying to copy from 1 server to another:<br />dtutil /sourceserver WIN-9DRARJUL1KP\MSSQL /SQL "\Maintenance Plans\Nightly Full Backups" /copy file;".\Nightly Full Backups.dtsx"<br />but when I run that command I get the following error<br /><br />This application requires one of the components: Integration Services, Business Intelligence Studio, Management Tools -<br />Basic or Database Engine to be installed by SQL Server 2014 Standard, Enterprise, Developer, Business Intelligence, or E<br />valuation Edition. To install a component, run SQL Server Setup and select the component name.<br /><br />I have scripted a complete build of my SQL servers (Web Ed) and scripted the SQL dbs and user creation and all I have left is to script the my 5 maintenance plans<br />thanks<br />Anonymoushttps://www.blogger.com/profile/08301073196491892598noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-83557832211349652092016-03-31T13:03:27.620-05:002016-03-31T13:03:27.620-05:00Hi Bill, thanks for the post. Like a previous que...Hi Bill, thanks for the post. Like a previous question you had posted on this page, I'm trying to COPY ALL packages from a SQL SERVER 2008 R2 box to a new SQL SERVER 2014 box. I first tried to copy a specific package from the MSDB folder on one server to the other but got this error:<br /><br />C:\Program Files\Microsoft SQL Server\100\DTS\Binn>DTUTIL /SQL "\DashboardDM_Imp<br />ortAbsences" /DestServer "APPDEV2014" /COPY SQL;"\DashboardDM_ImportAbsences" /Q<br />UIET<br />Microsoft (R) SQL Server SSIS Package Utilities<br />Version 10.50.1600.1 for 64-bit<br />Copyright (C) Microsoft Corporation 2010. All rights reserved.<br /><br />Error (0xC001404A) while checking for the existence of folder "\" on SQL Server.<br /><br />Description: While trying to find a folder on SQL an OLE DB error was encountered with error code 0x80004005 (Login timeout expired).<br />Source:<br /><br />C:\Program Files\Microsoft SQL Server\100\DTS\Binn><br /><br />I then tried to execute a script from the source server to my local file system and got this error:<br /><br />C:\DTUtilCommandsToCopySSISPackagesToNewServer>dtutil /sourceserver appdev-db-1<br />/SQL "\ExportMunisEmployees" /copy file;".\ExportMunisEmployees.dtsx"<br />Microsoft (R) SQL Server SSIS Package Utilities<br />Version 10.50.1600.1 for 64-bit<br />Copyright (C) Microsoft Corporation 2010. All rights reserved.<br /><br />The package \ExportMunisEmployees can not be saved to the file system since its<br />protection level is server storage. Use the encrypt action to change the protec<br />tion level.<br /><br />C:\DTUtilCommandsToCopySSISPackagesToNewServer><br /><br />I keep running into these issues. I can't believe that MS does not have a COPY TASK in SSIS or SSMS that copies MULTIPLE packages from one server to another that may or may not have a different version of SQL SERVER on it.<br /><br />Very exasperating. At any rate, I digress. Can you provide any insight on how to get this done simply?<br /><br />Thanks.<br /><br />Anonymoushttps://www.blogger.com/profile/15193978532559933486noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-27065872881398579902016-01-22T06:45:48.502-06:002016-01-22T06:45:48.502-06:00Bill, Thank You for the quick reply. I do see the ...Bill, Thank You for the quick reply. I do see the dtutil.exe on the right location for both SQL 2008 R2 and SQL 2014. The script works only partially. Some of the packages are exported and not all of them. I tried to run them for individual folders within MSDB and it is the same behavior. <br /><br />Basically We are upgrading/migrating from SQL 2008 R2 to SQL 2014. I was able to export everything out of SQL 2008 R2 and imported into SQL 2014. I am done with the upgrade now and wanted to export everything out of the new 2014 server and check into TFS. I am just stuck at the last step (export packages from 2014).<br /><br />Thanks,<br />Sen.Anonymoushttps://www.blogger.com/profile/01495360921015327644noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-16013713382664083332016-01-21T16:20:38.432-06:002016-01-21T16:20:38.432-06:00Sen,
Without seeing your environment, my first gue...Sen,<br />Without seeing your environment, my first guess would be that you need to ensure that you're using the correct version of dtutil for your SQL Server. The following list is for default locations of SQL Server 2014-2005 in descending order.<br /><br /><br />- C:\Program Files (x86)\Microsoft SQL Server\130\DTS\Binn\dtutil.exe<br />- C:\Program Files (x86)\Microsoft SQL Server\120\DTS\Binn\dtutil.exe<br />- C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\dtutil.exe<br />- C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\dtutil.exe<br />- C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtutil.exe<br />Bill Fellowshttps://www.blogger.com/profile/09363163555016521189noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-76779221669716098892016-01-21T16:12:53.468-06:002016-01-21T16:12:53.468-06:00Thanks for the script. Like the posting says the e...Thanks for the script. Like the posting says the export works for SQL 2008 R2 and for some reason doesn't work when i try to export it out of SQL 2014. <br />Please see the error message.<br /><br />Could not load package "\Outbound Interfaces\ssis_Extract_PHS_Provider_Roster" because of error 0xC0011008.<br />Description: Error loading from XML. No further detailed error information can be specified for this problem because no Events object was passed where detailed error information can be stored.<br /><br />Thanks,Sen.Anonymoushttps://www.blogger.com/profile/01495360921015327644noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-32239279388891732222015-07-08T13:56:37.159-05:002015-07-08T13:56:37.159-05:00Here is a version that works with 2005.. .Enjoy an...Here is a version that works with 2005.. .Enjoy and thanks!!!<br /><br /><br />;<br />WITH FOLDERS AS<br />(<br /> -- Capture root node<br /> SELECT<br /> cast(PF.foldername AS varchar(max)) AS FolderPath<br /> , PF.folderid<br /> , PF.parentfolderid<br /> , PF.foldername<br /> FROM<br /> msdb.[dbo].[sysdtspackagefolders90] PF<br /> WHERE<br /> PF.parentfolderid IS NULL<br /><br /> -- build recursive hierarchy<br /> UNION ALL<br /> SELECT<br /> cast(F.FolderPath + '\' + PF.foldername AS varchar(max)) AS FolderPath<br /> , PF.folderid<br /> , PF.parentfolderid<br /> , PF.foldername<br /> FROM<br /> msdb.[dbo].[sysdtspackagefolders90] PF<br /> INNER JOIN<br /> FOLDERS F<br /> ON F.folderid = PF.parentfolderid<br />)<br />, PACKAGES AS<br />(<br /> -- pull information about stored SSIS packages<br /> SELECT<br /> P.name AS PackageName<br /> , P.id AS PackageId<br /> , P.description as PackageDescription<br /> , P.folderid<br /> , P.packageFormat<br /> , P.packageType<br /> , P.vermajor<br /> , P.verminor<br /> , P.verbuild<br /> , suser_sname(P.ownersid) AS ownername<br /> FROM<br /> msdb.[dbo].[sysdtspackages90] P<br />)<br />SELECT <br /> -- assumes default instance and localhost<br /> -- use serverproperty('servername') and serverproperty('instancename') <br /> -- if you need to really make this generic<br /> 'dtutil /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\' + P.PackageName + '" /copy file;".\' + P.PackageName +'.dtsx"' AS cmd<br />FROM <br /> FOLDERS F<br /> INNER JOIN<br /> PACKAGES P<br /> ON P.folderid = F.folderid<br />-- uncomment this if you want to filter out the <br />-- native Data Collector packages<br />-- WHERE<br />-- F.FolderPath <> '\Data Collector'TiePact28https://www.blogger.com/profile/14633609818987739648noreply@blogger.comtag:blogger.com,1999:blog-4583365003930185416.post-62750070495927423822013-03-04T06:22:37.064-06:002013-03-04T06:22:37.064-06:00Can you please give the dtutil command to copy fro...Can you please give the dtutil command to copy from dev server with a specific name to local server instance or file system. I have tried many combinations but simply gives me an error that a particular path on dev server doesn't exist.Thanks in advance.Saurab Raohttps://www.blogger.com/profile/06582786586864061244noreply@blogger.com