biztalk services - Page 471

Export all SSIS packages from msdb using Powershell

- by jamiet

Have you ever wanted to dump all the SSIS packages stored in msdb out to files? Of course you have, who wouldn’t? Right? Well, at least one person does because this was the subject of a thread (save all ssis packages to file) on the SSIS forum earlier today. Some of you may have already figured out a way of doing this but for those that haven’t here is a nifty little script that will do it for you and it uses our favourite jack-of-all tools … Powershell!! Imagine I have the following package folder structure on my Integration Services server (i.e. in [msdb]): There are two packages in there called “20110111 Chaining Expression components” & “Package”, I want to export those two packages into a folder structure that mirrors that in [msdb]. Here is the Powershell script that will do that: Param($SQLInstance = "localhost") #####Add all the SQL goodies (including Invoke-Sqlcmd)##### add-pssnapin sqlserverprovidersnapin100 -ErrorAction SilentlyContinue add-pssnapin sqlservercmdletsnapin100 -ErrorAction SilentlyContinue cls $Packages = Invoke-Sqlcmd -MaxCharLength 10000000 -ServerInstance $SQLInstance -Query "WITH cte AS ( SELECT cast(foldername as varchar(max)) as folderpath, folderid FROM msdb..sysssispackagefolders WHERE parentfolderid = '00000000-0000-0000-0000-000000000000' UNION ALL SELECT cast(c.folderpath + '\' + f.foldername as varchar(max)), f.folderid FROM msdb..sysssispackagefolders f INNER JOIN cte c ON c.folderid = f.parentfolderid ) SELECT c.folderpath,p.name,CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) as pkg FROM cte c INNER JOIN msdb..sysssispackages p ON c.folderid = p.folderid WHERE c.folderpath NOT LIKE 'Data Collector%'" Foreach ($pkg in $Packages) { $pkgName = $Pkg.name $folderPath = $Pkg.folderpath $fullfolderPath = "c:\temp\$folderPath\" if(!(test-path -path $fullfolderPath)) { mkdir $fullfolderPath | Out-Null } $pkg.pkg | Out-File -Force -encoding ascii -FilePath "$fullfolderPath\$pkgName.dtsx" } To run it simply change the “localhost” parameter of the server you want to connect to either by editing the script or passing it in when the script is executed. It will create the folder structure in C:\Temp (which you can also easily change if you so wish – just edit the script accordingly). Here’s the folder structure that it created for me: Notice how it is a mirror of the folder structure in [msdb]. Hope this is useful! @Jamiet

Search Results

Search found 14213 results on 569 pages for 'biztalk services'.

Page 471/569 | < Previous Page | 467 468 469 470 471 472 473 474 475 476 477 478 | Next Page >

- by jamiet

- by michaela.seika(at)oracle.com

- by Shub Lahiri, A-Team

- by Mike.Hallett(at)Oracle-BI&EPM

- by JuergenKress

- by Irem Radzik

- by JuergenKress

- by JuergenKress

- by Lionel Dubreuil

- by Bob Rhubart

- by Lionel Dubreuil

- by Marco Russo (SQLBI)

- by David Dorf

- by Lionel Dubreuil

- by Lionel Dubreuil

- by david.butler(at)oracle.com

- by Tanu Sood

- by JuergenKress

- by Michael Hylton

- by Michael Hylton

- by Koushal Deshpande

- by user753488

- by Sylvie MacKenzie, PMP

< Previous Page | 467 468 469 470 471 472 473 474 475 476 477 478 | Next Page >