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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Ashish Kumar Mehta » DTUTIL Utility to Copy / Delete SSIS Deployed...

13 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Ashish Kumar Mehta

Posted Tuesday, July 08, 2008 10:33 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:14 AM
Points: 109, Visits: 204

Comments posted to this topic are about the item DTUTIL Utility to Copy / Delete SSIS Deployed in MSDB to File System

Post #530477

DbRob

Posted Wednesday, July 09, 2008 6:24 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:12 AM
Points: 14, Visits: 219

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

hodgy

Posted Wednesday, July 09, 2008 8:36 AM

SSCommitted

Group: General Forum Members
Last Login: Thursday, May 09, 2013 8:55 AM
Points: 1,865, Visits: 556

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

timothyawiseman

Posted Wednesday, July 09, 2008 9:18 AM

Right there with Babe

Group: General Forum Members
Last Login: Thursday, February 14, 2013 12:01 PM
Points: 743, Visits: 900

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

Ashish Kumar Mehta

Posted Wednesday, July 09, 2008 9:25 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 11:14 AM
Points: 109, Visits: 204

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

Lonely Rogue

Posted Sunday, September 07, 2008 5:45 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:34 AM
Points: 165, Visits: 405

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

lfguzman_25

Posted Tuesday, September 23, 2008 11:57 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 19, 2012 6:07 AM
Points: 17, Visits: 154

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

DbRob

Posted Wednesday, September 24, 2008 4:17 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, May 10, 2013 9:12 AM
Points: 14, Visits: 219

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

chileu17

Posted Thursday, January 08, 2009 5:20 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 26, 2011 8:28 AM
Points: 119, Visits: 400

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

Lonely Rogue

Posted Thursday, January 15, 2009 4:36 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 3:34 AM
Points: 165, Visits: 405

@chileu17

That script is meant for KATMAI ( SQL 2008 ).

Cheers,
Vampire

--In 'thoughts'...
Lonely Rogue

Post #636978

« Prev Topic | Next Topic »

13 posts, Page 1 of 2

1 2 »»

Permissions