Pass a list of Databases to an SSIS package to run Powershell script to backup these databases.

You need to have an array object to accept a list of database names and create an SSIS package with a parameter to run the powershell script. Set an agent job to pass the list value to run the package.
A little bit details of each component:

SSIS package

Step 1: Add a package scope variable: Var1 as string for example to receive the list value from the agent job;

Step 2: Add an “Execute Process Task”

Go to Process tab: add the path to point to Executable: C:\Windows\System32\WindowsPowerShell\v1.0\PowerShell.exe
Go to RExpressions tab: click on … to open up Property Expressions Editor and choose Arguments from Property tab to add the following Expression to include the variable:

“-ExecutionPolicy ByPass -command powershell ” + ” ‘C:\\temp\\call_backup_cmdlet_with_parameter.ps1’ ” + @[User::Var1]

Agent Job

In an agent job to run SSIS package to pass parameter as a list, set the parameter value from Job step Property (to run ssis) Set Values tab:
Under Properties:
Property Path \Package.Variables[Var1].Value
Value ‘myDb1,mydb2,myDb3′

If you script our the job, you can find this line:

@command=N’/FILE “\”E:\Packages\myPowerShell1.dtsx\”” /CHECKPOINTING OFF /SET “\”\Package.Variables[Var1].Value\””;”\””myDb1,mydb2,mydb3”\”” /REPORTING E’,

Powershell script:

Use an array parameter to accept the list and unpack the list with a loop to use the individual value.

#Get values from script
param([parameter(Mandatory=$true)][string[]]$myDBs = $(Throw "Please provide a DB name.")) 

# Test to see if the SQLPS module is loaded, and if not, load it
if (-not(Get-Module -name 'SQLPS')) {
  if (Get-Module -ListAvailable | Where-Object {$_.Name -eq 'SQLPS' }) {
    Push-Location # The SQLPS module load changes location to the Provider, so save the current location
   Import-Module -Name 'SQLPS' -DisableNameChecking  

$dt = Get-Date -Format yyyyMMddHHmmss

$svrname = "MC047012\MSSQL2014"
foreach ( $db in $myDBs ) { 


#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0


$bfil = "$workdir\$($db)_db_$($dt).bak"
$q = @"
TO  DISK = N'$bfil'
WITH  DESCRIPTION = N'Full backup of $db',
NAME = N'$db Backup',
Invoke-SQLCmd -ServerInstance $svr.Name  -Database master -Query $q -QueryTimeout 0
Catch [Exception]
 write-host "Database - $($db ): "$_.Exception.Message


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s