inRiver PIM: Deleting Orphaned Resources
When resource entities are deleted in inRiver PIM, the actual resource is not deleted with it. After a while, you can end up with lots of unused resources in your PIM database. You can safely get rid of these resource files, and I think you should. In the installation of inRiver PIM that I’m working with, we’re using three databases, one being the main inRiver PIM database, one is the resource database, and the last one is for logging. Splitting everything up this was recommended to me, and I’m happy with this setup.
First, I created a view in my resource database:
CREATE VIEW [dbo].[View_OrphanResourceFiles]
SELECT Value AS Id
WHERE FieldTypeId = 'ResourceFileId'
Then, in a periodic cleanup job, I use the following C# code to delete the actual resources:
private static void DeleteOrphanedResources()
var connectionString = ConfigurationManager.ConnectionStrings["pim.resource"].ToString();
const string query = "SELECT Id FROM [View_OrphanResourceFiles]";
var dataTable = new DataTable();
using (var connection = new SqlConnection(connectionString))
var command = new SqlCommand(query, connection);
var da = new SqlDataAdapter(command);
if (dataTable.Rows != null && dataTable.Rows.Count > 0)
foreach (DataRow row in dataTable.Rows)
if (row == null)
int.TryParse(row.ToString(), out id);
private static void DeleteResourceFile(int id)
var ok = RemoteManager.UtilityService.DeleteFile(id);
// There is a known bug in inRiver PIM: exception is always thrown here!
private static void EnsurePimConnection(PimCredentials credentials)
if (RemoteManager.Instance != null)
RemoteManager.CreateInstance(credentials.Url, credentials.UserName, credentials.Password);
// PimCredentials is a simple class that holds the UserName, Password and Url to
// the inRiver PIM instance, and reads these from the Web.config file during the
// class instantiation.
Note the bug in the comment; this has been reported to inRiver.