SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System


DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System

Author
Message
Ashish Kumar Mehta
Ashish Kumar Mehta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 245
Comments posted to this topic are about the item DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System
DbRob
DbRob
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 260
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!
hodgy
hodgy
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2863 Visits: 596
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

timothyawiseman
timothyawiseman
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 920
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/
Ashish Kumar Mehta
Ashish Kumar Mehta
SSC Veteran
SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)SSC Veteran (204 reputation)

Group: General Forum Members
Points: 204 Visits: 245
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..
Lonely Rogue
Lonely Rogue
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 441
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
lfguzman_25
lfguzman_25
SSC Journeyman
SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)SSC Journeyman (77 reputation)

Group: General Forum Members
Points: 77 Visits: 167
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?
DbRob
DbRob
SSC-Enthusiastic
SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)SSC-Enthusiastic (134 reputation)

Group: General Forum Members
Points: 134 Visits: 260
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
chileu17
chileu17
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1041 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
...
...

Lonely Rogue
Lonely Rogue
SSC Eights!
SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)SSC Eights! (813 reputation)

Group: General Forum Members
Points: 813 Visits: 441
@chileu17

That script is meant for KATMAI ( SQL 2008 ).

Cheers,
Vampire

--In 'thoughts'...
Lonely Rogue
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search