December 13, 2007 0

Deleting Files with SSIS

By Andrew Konkol in SSIS

A common practice is to have user’s upload images to a directory that is readable from IIS, store that upload path to a database, and then construct a relative path to that upload directory for use within your web application. What happens if that data has to be removed?

Example Scenario: You are running your own version of a social networking web application which users can upload photos of themselves and save it in a profile. That user wishes to remove themselves from your social networking site and you must delete all database records pertaining to them as well as all the pictures (binaries) that they have uploaded.

Possible Solutions:

Pull the full path for all the binaries from your database and create a batch/vb script to delete the picture files.
Leave the images there, because after the record containing that image’s path is deleted it will never be used (bad idea, you would be eating up un-necessary space).
Create a SSIS package to pull the relative path from the database, construct a UNC or local path and delete the picture files

Creating an SSIS package to remove files:

Step 1: Creating the stored procedure

You need to create a stored procedure to retrieve all of the records that contain some sort of path to where the image is stored. Quite frequently this path is inserted into the database table as a path that is relative to your IIS instance. The stored procedure below allows you to supply a user id (named @pUserID) and returns all records in the UserPictures table for that particular user id. Notice that I am flipping the direction of slashes, why do you ask? Since relative paths are commonly stored for image paths they look like /images/someotherfolder/somephoto.jpg, if we are to delete pictures that are on the local path or UNC path we need to flip the direction of the slashes.

   1: CREATE Procedure GetImagesByUserID   2: @pUserID int   3:     4: AS   5: BEGIN   6:     7: SELECT replace(RelativePath,'/','\')   8: FROM UserPictures   9: WHERE UserID=@pUserID  10: END

Step 2: Create a new SSIS package with variables

Although you should keep the number of variables in your package to a minimum, create the following variables:

UserID has the value of ’911′ as a piece of sample data you will pass into the @pUserID later on

ImageRecords is of data type ‘Object’ because it will hold the resulting record set from the stored proc created in Step 1.

Step 3: Retrieve image paths using ‘Execute SQL Task”

This task will supply the UserID variable to our stored proc and execute it.

Configure the ‘Execute SQL Task’ to:

Return a Full Result Set
Use ADO.NET as the ConnectionType
Select your connection
Write the SQL statement to execute the stored proc

Click on the Parameter Mapping tab and click “Add”

Configure:

Variable Name is User::UserID
Direction is Input
Data Type is Int32
Parameter Name is @pUserID
Parameter Size does not matter

Click on the Result Set section and click “Add” and set the Result Name to ’0′ and the Variable Name to User::ImageRecords, this will assign the output from the stored proc to your ImageRecords variable.

Step 4: Loop through each record returned using a ‘Foreach Loop Container’

Use a Foreach Loop Container to iterate through the resulting record set from the step above. *Its not generally a good idea to iterate through every record of a record set (especially with thousands of records), but in this case it is very useful.

Click on the newly created Foreach Loop Container and create a new variable called CurrentFile, this will provide a variable for the ‘Foreach Loop Container’ to assign the image path to.

CurrentFIle is a variable for the ‘Foreach Loop Container’ to assign the image path to
BaseDir is a variable to which you assign a UNC or local path name (\\myfileserver\mywebapp\, C:\Data)
FullPath is a variable which builds the full path for the image

Click on the FullPath variable and go to the Properties window

Configure:

EvaulateAsExpression is True
Expression is @[User::BaseDir] + @[User::CurrentFile]

Double click on the ‘Foreach Loop Container’ and click on the Collection tab and configure:

Enumerator is a Foreach ADO Enumerator
ADO object source variable is User::ImageRecords this will tell the loop to iterate through the records in the ImageRecords variable.