delete Sharepoint file using Script Task

Question

I need to delete a Sharepoint file using the Script Task from SSIS. In Visual Basic, I've tried using the SPListItemCollection with imports Microsoft.Sharepoint but it doesn't recognize the namespace. I didn't find lots of threads on this subject or what I've found wasn't related with script task, so any help will be really appreciated. Many thanks

Update based on @Hadi answer

Thanks Hadi for your answer. I've given up the idea of using SPListCollection as it seems too complicated. Instead I'm trying to delete the file after it is downloaded from Sharepoint to the local folder. I would need help at the line that actually deletes the file. Here is the code:

Public Sub Main()
    Try

        ' get location of local folder 

        Dim dir As DirectoryInfo = New DirectoryInfo(Dts.Variables("DestP").Value.ToString())



        If dir.Exists Then
            ' Create the filename for local storage

            Dim file As FileInfo = New FileInfo(dir.FullName & "\" & Dts.Variables("FileName").Value.ToString())


            If Not file.Exists Then

                ' get the path of the file to download 

                Dim fileUrl As String = Dts.Variables("SHP_URL").Value.ToString()




                If fileUrl.Length <> 0 Then 

                    Dim client As New WebClient()



                    If Left(fileUrl, 4).ToLower() = "http" Then

                        'download the file from SharePoint 

                        client.Credentials = New System.Net.NetworkCredential(Dts.Variables("$Project::UserN").Value.ToString(), Dts.Variables("$Project::Passw").Value.ToString())

                        client.DownloadFile(fileUrl.ToString() & "/" & Dts.Variables("FileName").Value.ToString(), file.FullName)


                    Else
                        System.IO.File.Copy(fileUrl.ToString() & Dts.Variables("FileName").Value.ToString(), file.FullName)

                    End If
'delete file from Sharepoint

                    client.(fileUrl.ToString() & "/" & Dts.Variables("FileName").Value.ToString(), file.FullName).delete()

                Else

                    Throw New ApplicationException("EncodedAbsUrl variable does not contain a value!")

                End If

            End If

        Else

            Throw New ApplicationException("No ImportFolder!")

        End If

    Catch ex As Exception



        Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0)



        Dts.TaskResult = ScriptResults.Failure


    End Try

    Dts.TaskResult = ScriptResults.Success
End Sub

Did you add `Microsoft.Sharepoint.dll` as Reference inside the script task? — Hadi, Feb 09 '19 at 20:02
next time you should leave a comment on my answer to inform me that you have update the question, i will not be notified when writing it in the question — Hadi, Feb 10 '19 at 17:02

Hadi · Accepted Answer · 2019-02-10T17:54:34.567

Update 1 - Delete using FtpWebRequest

You cannot delete file using WebClient class. You can do that using FtpWebRequest class. And send a WebRequestMethods.Ftp.DeleteFile request as mentioned in the link below:

How To Delete a File From FTP Server in C#

It should work with Sharepoint also.

Here is the function in VB.NET

Private Function DeleteFile(ByVal fileName As String) As String
    Dim request As FtpWebRequest = CType(WebRequest.Create(fileUrl.ToString() & "/" & fileName), FtpWebRequest)
    request.Method = WebRequestMethods.Ftp.DeleteFile
    request.Credentials = New NetworkCredential(Dts.Variables("$Project::UserN").Value.ToString(), Dts.Variables("$Project::Passw").Value.ToString())

    Using response As FtpWebResponse = CType(request.GetResponse(), FtpWebResponse)
        Return response.StatusDescription
    End Using
End Function

You should replace the following line:

client.(fileUrl.ToString() & "/" & Dts.Variables("FileName").Value.ToString(), file.FullName).delete()

With

DeleteFile(Dts.Variables("FileName").Value.ToString())

Also you may use the following credentials:

request.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;

References

Initial Answer

I was searching for a similar issue from a while, it looks like you cannot delete a Sharepoint file in SSIS using a File System Task or Execute Process Task, the only way is using a Script Task. There are many links online describing this process such as:

Concerning the problem that you have mentioned, i think you should make sure that Microsoft.Sharepoint.dll is added as a reference inside the Script Task. If so try using Microsoft.Sharepoint.SPListItemCollection instead of SPListItemCollection.

score 0 · Answer 2 · answered Feb 12 '19 at 15:46

Thanks @Hadi for your help. For me it didn't work with FTPWebResponse. It worked with HttpWebRequest. Here is the script:

Dim request As System.Net.HttpWebRequest = CType(WebRequest.Create(fileUrl.ToString() & "/" & Dts.Variables("FileName").Value.ToString()), HttpWebRequest)

request.Credentials = New System.Net.NetworkCredential(Dts.Variables("$Project::UserN").Value.ToString(), Dts.Variables("$Project::Passw").Value.ToString())

request.Method = "DELETE"

Dim response As System.Net.HttpWebResponse = CType(request.GetResponse(), HttpWebResponse)

delete Sharepoint file using Script Task

Update based on @Hadi answer

2 Answers2

Update 1 - Delete using FtpWebRequest

Initial Answer