SSIS: Delete old files with PowerShell (via an ‘Execute Process Task’) #FileRetentionPeriod

Today I wanted to delete old files from certain folders based on a given retention period of 60 days. In my case the folders ‘Processed’ and ‘Incorrect’.
A while a go I wrote a PowerShell maintenance script that just loops through a list of pre-defined paths/folders and deletes files which are older than the given number of days…but I realised today, why not execute that similair PowerShell-script within SSIS?
Well…here’s how you could do that and in fact it’s easier that you might think!

In my example I made a Squence Container at the end of my Control Flow in SSIS which holds all the Process Tasks that delete the so called ‘old files’:


First step to do is to create two variables:

@[User::ProcessedFolder] (or any other name) = "D:\DWH\Import\"
@[User::PowerShellProcessedArguments] = "ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"
IMPORTANT: Make sure to modify the extention-property above in case you want to delete other types of files.

Sub-folders and files:
In case you would like to have it to also look at sub-folders, add ‘-Recurse‘ just after the extention-argument, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item"

Test run (-WhatIf):
In case you would like to verify the syntax, just add ‘-WhatIf‘ at the end, for example:
"ls -Path '"+ @[User::ProcessedFolder] +"' -Filter '*.csv' -Recurse |? {$_.LastWriteTime -lt (get-date).AddDays(-60)} | Remove-Item" -WhatIf"

Next, drag an ‘Execute Process Task‘ to your Control Flow. Now double click on it to open the editor and go to ‘Process‘. Set these properties:

Execute Process Task Editor -> Process properties:
Executable = C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe

Arguments = (blank)
You can leave this one blank (will be filled by an expression during execution, see below).

WindowStyle = Hidden
Otherwise it will pop-up a PowerShell-window everytime it executes one of these. It will now pop-up a single command-window for less than a second.

It should now look like (perhaps except the Arguments-property):

Now go to ‘Expressions‘ and make sure that the ‘Arguments‘-property will be given the value of @[User::PowerShellProcessedArguments]:

That’s it!
Now run it a couple of times 🙂

I’ve used SQLCMD before to delete old files via a stored procedure, but this is a bit dangerous due to the fact that it requires you to enable SQLCMD:

Or use a single PowerShell script to delete all files in specific folders during maintenance?

More things you could do with PowerShell? Click here.

PowerShell: Delete old files based on a given retention period (days) #maintenance

How to delete old files based on a given retention period (days) with PowerShell?
Well…it’s easy! And you might want to use it to clean-up a few old backup files (or even *.csv *.sql or *.txt).

Basically it consists of two functions, namely ‘DeleteOldFilesWithExtension‘ and ‘DeleteOldFiles‘ (without looking for certain extensions):



function global:DeleteOldFilesWithExtension([string]$Path, [int]$Daysback, [string]$Extension)
{ #Begin Function

#$Daysback = "-7"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
#$Path = "D:\PowerShell\SQLScripts"

#Based on Creation Date:
#Get-ChildItem $Path -Include $Extension -Recurse | Where-Object { $_.CreationTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

#Based on Modified Date:
Get-ChildItem $Path -Include $Extension -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

} #End Function

function global:DeleteOldFiles([string]$Path, [int]$Daysback)
{ #Begin Function

#$Daysback = "-7"
$CurrentDate = Get-Date
$DatetoDelete = $CurrentDate.AddDays($Daysback)
#$Path = "D:\PowerShell\SQLScripts"

#Based on Creation Date:
#Get-ChildItem $Path -Recurse | Where-Object { $_.CreationTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

#Based on Modified Date:
Get-ChildItem $Path -Recurse | Where-Object { $_.LastWriteTime -lt $DatetoDelete } | Remove-Item -Confirm:$false -force -recurse

} #End Function

DeleteOldFiles "D:\PowerShell\SQLScripts" -7
DeleteOldFilesWithExtension "D:\PowerShell\BackupScripts" -7 "*.sql"


The bottom two lines are actually calling/executing the functions:
(FunctionName) (Path/Folder) (RetentionPeriodInDays) (ExtentionType – optional)

So save the scripts noted above as ‘DeleteOldFiles.ps1‘ and you’re able to execute it via a SQL Agent Job or from the command line.

How to execute?:
Execute this statement in Command (CMD) or via a SQL Agent Job (PowerShell step):


PowerShell "E:\PowerShell\DeleteOldFiles.PS1"

Other PowerShell scripts
Script all SQL database objects with PowerShell?

More things you could do with PowerShell? Click here.

SSIS PowerShell Script Task

Today, I wanted to create a SSIS package which uploads/publishes a CSV-file with deltas to (IBM) MQFT by using PowerShell.
Without going into details about MQFT, I would like to make a note here on how to execute a PowerShell script within an SSIS package.

Note: I’ve chosen to execute the PowerShell within a Script Task, but you’re also able to execute a PowerShell script within an Execute Process Task. More info on that can be found here, here and here.

I found this great blog post which helped me with my first steps:

By using a Script Task, you’re able to execute a PowerShell script.
You may want to save the following code as a template:

Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

namespace ST_e0f329a65fa94200835440a6efd0c0c2.csproj
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

#region VSTA generated code
enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure


/// This method is called when this script task executes in the control flow.
/// Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
/// To open Help, press F1.

public void Main()
// Code to create a runspace and execute the PowerShell script the pipeline:
Runspace ps = RunspaceFactory.CreateRunspace();
Pipeline pipe = ps.CreatePipeline();

using (pipe)
//---->>This psline variable will contain your PowerShell script:
string psline = "\"Hello World\"";

Dts.TaskResult = (int)ScriptResults.Success;
//Dts.TaskResult = (int)ScriptResults.Failure;

The issue I faced was that our server didn’t have the Windows SDK or PowerShell SDK installed. So building the project (C# 2008 in BIDS 2008R2) returned an error:


Apparently, it is unable to include:
using System.Management.Automation
using System.Management.Automation.Runspaces

To resolve this, run the following statement within a PowerShell console to generate the required DLL-file (more info here):
Copy ([PSObject].Assembly.Location) C:\Temp

A DLL-file is generated in C:\Temp which is called ‘System.Management.Automation.dll‘.

We need to refer to this DLL-file within the C#-project (Edit the Script Task within BIDS).
To accomplish this, Right-click on References and select ‘Add Reference‘:


Open the ‘Browse‘-tab and select the DLL-file in C:\Temp:


Last thing, we need to make sure that the DLL-file is copied with the VS project. Right-click on the DLL and open the Properties. Now, select True for ‘Copy Local’:

Close the window by clicking OK and (re-)Build the project.

That’s it! Click on ‘Save All‘ and close the Visual Studio environment (script editor) and return to SSIS.
Click OK to close the Script Task and save the SSIS package. You may want to execute the Script Task to verify that everything is working properly 🙂


Enjoy using PowerShell within SSIS!

Use PowerShell to script all database objects

Last week I started working with PowerShell for the first time.
My goal was to create a script that would somehow script all indexes (CREATE-statements) in specified databases.

When I was done Google-ing for all kind of solutions, I combined all sorts of existing PowerShell scripts. The resuls was a single PowerShell script that simply scripts all database objects within the specified database (like the ‘Generate Scripts‘-option does in SSMS/SSDT)!

Modify the script to adjust a few settings!
In the header section, you may want to change the destination folder for the output scripts. Look for:
$SavePath = "C:\PowerShell\SQLScripts\" + $($dbname) + "\" + $DateFolder

Another things is that you’ll need to define which databases on which server needs to be scripted in the bottom section of the PowerShell script:

#Execute function for all PM's user databases:

Script-DBObjectsIntoFolders '.' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'localhost' 'AdventureWorks2012'
Script-DBObjectsIntoFolders 'servername\instancename' 'AdventureWorks2012'

How to execute?:
Execute this statement in Command (CMD) or via a SQL Agent Job (PowerShell step):

PowerShell "C:\PowerShell\ScriptAllObjectsInDatabase.PS1"


The Result?:
It creates a new folder every time it is executed, the name of the folder will be a timestamp.

Schedule the thing!

I used the command (CMD) steptype of SQL Agent, because I simply didn’t got it working as a PowerShell steptype:

Download the PowerShell script here: ScriptAllObjectsInDatabase
Don’t forget to save it as ‘ScriptAllObjectsInDatabase.PS1‘!

*** UPDATE ***
In case you would also like to script XML Indexes, add these code-lines to the script on the right locations:

$scriptr.Options.XmlIndexes = $True
$scriptDrop.Options.XmlIndexes = $True