Exporting Multiple SSIS packages from MSDB
-
If we need to export a package from MSDB into the file system we can do it using the SQL Management Studio...However, if we need to export all the SSIS packages available from MSDB onto the file system all at once without doing one at a time...Is there a smart way to achieve this ?
-
Yes, you can create a script to do it.
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
I have a similar question (I think). I'm attempting to practice upgrading from SQL 2005 to SQL 2008 on my test machine. I've moved over all my db's and log-ins. I'm wanting to move the SSIS packages, too. On my production server, they're all stored in the msdb. Can I just restore the prod msdb over my test msdb? I'm thinking that seems too simple and I'm missing something.
-
Keep in mind that msdb is a system database and contains information related to the source system. Given that, don't restore msdb from another server.
If you know know the path(in SSIS) you can build a script using dtutil.exe to pull them out of the server.
A while back I wrote a utility to import and extract DTS packages from a server.. Its on CodePlex.. I looked at doing the same for SSIS but didn't think there was a great need, I might have been wrong..
CEWII
-
Pooh. Ok, what if I go into the production server, connect to Integration Services & when I get to the export step, I export it to my test SQL box from there? I'm trying to avoid having to export to the file system and then re-import, simply because there are so many packages and I want to save some time. If that's not a good idea, though, I will do it the long way.
-
How many packages and did you use paths in SSIS?
CEWII
-
47. Yes, I used paths, but I can still access those paths from my test box.
-
lduvall (1/21/2011)
Pooh. Ok, what if I go into the production server, connect to Integration Services & when I get to the export step, I export it to my test SQL box from there? I'm trying to avoid having to export to the file system and then re-import, simply because there are so many packages and I want to save some time. If that's not a good idea, though, I will do it the long way.I have used tsql and SSIS to actually do this for me. You can take either the latest package or all revisions, dump them to a staging table and then transfer that table to the new server and then copy the data back into the appropriate tables (since data export and standard methods in ssis don't see the dts and ssis tables in msdb). Much faster and rather easy to do.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Here are three sample scripts. You would need to modify the first two to have them dump into the staging table.
/* SQL 2005 */
SELECT T1.*
FROM dbo.sysdtspackages90 AS T1
INNER JOIN (
SELECT [id], MAX([verbuild]) AS [verbuild]
FROM dbo.sysdtspackages90
GROUP BY [id]) AS T2
ON T1.[id] = T2.[id]
AND T1.[verbuild] = T2.[verbuild]
/* sql2008 */
SELECT T1.*
FROM dbo.sysssispackages AS T1
INNER JOIN (
SELECT [id], MAX([verbuild]) AS [verbuild]
FROM dbo.sysssispackages
GROUP BY [id]) AS T2
ON T1.[id] = T2.[id]
AND T1.[verbuild] = T2.[verbuild]
/*Extract to File System SQL 2005*/
select 'dtutil /SQL "' +[p].[name] + '" /COPY FILE;"c:\myTestPackages\'+[p].[name]+'.dtsx"'
from msdb.dbo.sysdtspackages90 [p]
And here is a sample of inserting into the appropriate table from the staging table on SQL 2005
Insert into sysdtspackages (name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype)
Select name,id,versionid,description,categoryid,createdate,owner,packagedata,owner_sid,packagetype
From DTSPackageTransfer
Where Description <> ''
I sense a blog post coming on this topic.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
I was thinking about adding to the utility I wrote for DTS to do this as well..
CEWII
-
I never written scripts before and I need to copy all these MSDB Packages to a folder on the C: drive. I need something that can populate the SAMPLEPACKAGENAME in the command below with all the packages names from sysssispackages table. Some packages are not in the root of MSDB and would require the folder name to be specified in the quotes so sysssispackages may not be the best table to use since it does not specify the location.
DTUTIL /SQL "SAMPLEPACKAGENAME" /COPY FILE;C:\SSISPACKAGE\SAMPLEPACKAGENAME.DTSX /QUIET
-
Here is an article I wrote on the topic to export the packages.
http://jasonbrimhall.info/2010/12/28/ssis-multiple-file-export/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
Hi,
I'm sorry if I missed the solution here but I need to export SEVERAL projects (folders) with tens of SSIS packages from Sql Server 2008 MSDB to Sql Server 2012 MSDB.
Any smooth way to do this?
Ville
-
I know this is a very old post, but I too wanted to do this today.. I found a PowerShell script similar to what I have below but the select was not correct.
Here is the script to use, Just change the $SQLInstanceSQL to your SQL instance name and the $fullfolderPath parent folder name. This will create 1 subfolder per package under the parent folder specified.
Param($SQLInstance = "SRVER\InstanceName")
add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue
add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue
cls
$Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "select p.name, CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg
FROM MSDB..sysssispackages p join
msdb..sysssispackagefolders f on p.folderid = f.folderid
where f.foldername NOT LIKE 'Data Collector%'"
Foreach ($pkg in $Packages)
{
$pkgName = $Pkg.name
$fullfolderPath = "C:\SSIS_Packages_DBA\$pkgName\"
if(!(test-path -path $fullfolderPath))
{
mkdir $fullfolderPath | Out-Null
}
$pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx"
} -
--1) Create Folder SSISPACKAGE IN C drive
--2) Run Script to generate DTUTIL statements to export ALL SSIS packages from the MSDB package store to the file system using DTUTIL
--3) Run required DTUTIL scripts using command prompt
USE MSDB
GO
;WITH FOLDERS AS
( -- Capture root node
SELECT
cast(PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
WHERE
PF.parentfolderid IS NULL
-- build recursive hierarchy
UNION ALL
SELECT
cast(F.FolderPath + '\\' + PF.foldername AS varchar(max)) AS FolderPath
, PF.folderid
, PF.parentfolderid
, PF.foldername
FROM
msdb.dbo.sysssispackagefolders PF
INNER JOIN
FOLDERS F ON F.folderid = PF.parentfolderid
), PACKAGES AS
( -- pull information about stored SSIS packages
SELECT
P.name AS PackageName
, P.id AS PackageId
, P.description as PackageDescription
, P.folderid
, P.packageFormat
, P.packageType
, P.vermajor
, P.verminor
, P.verbuild
, suser_sname(P.ownersid) AS ownername
FROM
msdb.dbo.sysssispackages P
)SELECT
-- assumes default instance and localhost
-- use serverproperty('servername') and serverproperty('instancename')
-- if you need to really make this generic
'DTUTIL /sourceserver ' + @@SERVERNAME + ' /SQL "'+ F.FolderPath + '\\' + P.PackageName + '" /En file;"C:\SSISPACKAGE\'+'\\' + P.PackageName +'.dtsx";0 /Q' AS cmd
FROM
FOLDERS F INNER JOIN
PACKAGES P ON P.folderid = F.folderid
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply