Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System Expand / Collapse
Author
Message
Posted Tuesday, July 8, 2008 10:33 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:47 AM
Points: 118, Visits: 238
Comments posted to this topic are about the item DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System
Post #530477
Posted Wednesday, July 9, 2008 6:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:46 AM
Points: 14, Visits: 238
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!
Post #530693
Posted Wednesday, July 9, 2008 8:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 4:39 AM
Points: 1,865, Visits: 593
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
Post #530872
Posted Wednesday, July 9, 2008 9:18 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, November 20, 2014 4:22 PM
Points: 752, Visits: 918
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/
Post #530945
Posted Wednesday, July 9, 2008 9:25 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:47 AM
Points: 118, Visits: 238
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..
Post #530954
Posted Sunday, September 7, 2008 5:45 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:51 PM
Points: 173, Visits: 430
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
Post #565120
Posted Tuesday, September 23, 2008 11:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 9:57 AM
Points: 17, Visits: 157
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?
Post #574678
Posted Wednesday, September 24, 2008 4:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 9:46 AM
Points: 14, Visits: 238
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
Post #575015
Posted Thursday, January 8, 2009 5:20 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:59 AM
Points: 119, Visits: 401
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
...
...
Post #632984
Posted Thursday, January 15, 2009 4:36 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:51 PM
Points: 173, Visits: 430
@chileu17

That script is meant for KATMAI ( SQL 2008 ).

Cheers,
Vampire


--In 'thoughts'...
Lonely Rogue
Post #636978
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse