Hello, I have created deployment utility for my SSIS project in visual studio. However I don't want my admin to run the wizard to install these packages into MSDB. instead I want to write a powershell script which can install my ssismanifest and all dtsx packages into MSDB database. Also put the ssis package configurations in a specified place. Can you please give me guidance on how can I automate the task of installation of packages using Powershell?

Got this from http://sqlserverpedia.com/blog/sql-server-bloggers/importing-and-exporting-ssis-packages-using-powershell/ hope this helps. Importing and Exporting SSIS Packages Using PowerShell by Chad Miller SQL Server PowerShell Extensions (SQLPSX) includes a set of function for working with SSIS which among other things allow you to import and export SSIS packages between the file system and msdb. The functionality is best illustrated by looking a few examples. Creating an SSIS folder Note: The SSIS module supports SQL 2005 through 2008 R2. By default the module is setup to use the 2008 or 2008 R2 assembly, to switch to 2005, comment/uncomment the appropriate assembly at the top of SSIS.psm1 file in the \Modules\SSIS folder. Once loaded an assembly can’t be unloaded (.NET thing), so you’ll need to start a new PowerShell host to switch between 2005 and 2008. Use the new-isitem function to create a folder. The following example imports the SSIS module and creates a folder called sqlpsx off of the root \msdb folder: view source <object id="highlighter_157231_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="423" /> <param name="_cy" value="423" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="0" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 1.1 2.2 3.import-module SSIS 4.new-isitem '\msdb' 'sqlpsx' $env:computername We can see the folder in SSMS: Importing SSIS Packages to MSDB Having created a folder, next I want to import SSIS packages on the file system to MSDB. In addition as part of the copy process I want to change the location where my SQL Server table-based Package Configuration points: File System dtsx files: I’ll use the copy-isitemfiletosql function… view source <object id="highlighter_501468_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="423" /> <param name="_cy" value="423" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="0" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 1.1 2.copy-isitemfiletosql -path "C:\Program Files\Microsoft SQL Server\100\DTS\Packages\*" -destination "msdb\sqlpsx" -destinationServer "$env:computername" -connectionInfo @{SSISCONFIG=".\SQLEXPRESS"} Note: The SSIS copy-* functions include a progress bar indicator: Exporting SSIS Packages from MSDB Now that I have SSIS packaged stored in MSDB, I’ll copy them back to the file system using the copy-isitemsqltofile function… view source <object id="highlighter_366866_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="5080" /> <param name="_cy" value="5080" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="-1" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 1.1 2.copy-isitemsqltofile -path '\sqlpsx' -topLevelFolder 'msdb' -serverName "$env:computername\sql1" -destination 'c:\Users\Public\bin\SSIS' -recurse Looking at the file system we see the dtsx files have been created: Note: The API ManagedDTS has some inconsistencies in usage, so the SQL Server instance ($env:computername\sql1) instead of just the computer name ($env:computername) is needed. Removing SSIS Packages and Folders from MSDB Note: Like any delete operation be careful! This isn’t a common operation, but for completeness I’ll remove the SSIS packages and folders I created. As a safety measure the remove and copy functions support the standard PowerShell WhatIf and Confirm parameters, so first I’ll run the command with –WhatIf: view source <object id="highlighter_196735_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="423" /> <param name="_cy" value="423" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="0" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 1.1 2.2 3. get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1" | remove-isitem -WhatIf 4. get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$_.name -like "sqlpsx*"} | remove-isitem -WhatIf This produces the following output: view source <object id="highlighter_49332_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="423" /> <param name="_cy" value="423" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="0" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 01.1 02.2 03.3 04.4 05.5 06.What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx1,Z003)". 07.What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx2,Z003)". 08.What if: Performing operation "Remove-ISItem" on Target "RemoveFromDtsServer(msdb\sqlpsx\sqlpsx3,Z003)". 09.... 10.What if: Performing operation "Remove-ISItem" on Target "RemoveFolderFromDtsServer(msdb\SQLPSX,Z003)". Satisfied with the results I’ll go ahead and remove the packages and folder: view source <object id="highlighter_616115_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash"> <param name="_cx" value="423" /> <param name="_cy" value="423" /> <param name="FlashVars" /> <param name="Movie" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="Src" value="http://media.sqlserverpedia.com/v14/flash/sh/clipboard.swf" /> <param name="WMode" value="Transparent" /> <param name="Play" value="0" /> <param name="Loop" value="-1" /> <param name="Quality" value="High" /> <param name="SAlign" /> <param name="Menu" value="0" /> <param name="Base" /> <param name="AllowScriptAccess" value="always" /> <param name="Scale" value="ShowAll" /> <param name="DeviceFont" value="0" /> <param name="EmbedMovie" value="0" /> <param name="BGColor" /> <param name="SWRemote" /> <param name="MovieData" /> <param name="SeamlessTabbing" value="1" /> <param name="Profile" value="0" /> <param name="ProfileAddress" /> <param name="ProfilePort" value="0" /> <param name="AllowNetworking" value="all" /> <param name="AllowFullScreen" value="false" /> </object> print? 1.1 2.2 3. get-isitem '\sqlpsx' 'msdb' "$env:computername\sql1" | remove-isitem 4. get-isitem '\' 'msdb' "$env:computername\sql1" | ?{$_.name -like "sqlpsx*"} | remove-isitem Summary Including the functions demonstrated in this post the SQLPSX SSIS module contains the following functions: Copy-ISItemSQLToSQL Copy-ISItemSQLToFile Copy-ISItemFileToSQL Get-ISData Get-ISItem Get-ISPackage Get-ISRunningPackage Get-ISSqlConfigurationItem New-ISApplication New-ISItem Remove-ISItem Rename-ISItem Set-ISConnectionString Set-ISPackage Test-ISPath In addition to the online help, each function implement get-help with examples. Related Posts: T-SQL Tuesday #005: SSIS Reporting Adventures in Powershell SSIS Administration Programming Execute SQL Server Integration Services (SSIS) Package from Powershell Providing Online Help for Powershell Modules

I am thinking you may not need the SSISInstallManifest, rather create a very simple PowerShell script to just execute the DtUtil like dtutil /SQL srcPackage /COPY DTS;destFolder\destPackage To place the config files to a location of your choice you can do Copy-Item c:\PackageConfig\MyConfig.xml c:\ProdConfig Arthur My Blog

Also found you a walk-through: Deploying a package using PowerShell: http://maxt2posh.wordpress.com/2010/04/23/deploying-ssis-packages-using-dtutil-exe-with-powershell-part-1%E2%80%A6/Arthur My Blog