SSIS script delete or replace Sharepoint library file

Question

I'm trying to use an SSIS script task to copy an excel file into a Sharepoint library when a file with the same name already exists there. It doesn't matter if it deletes the old file first then copies the new or just copies and replaces the new. I can't figure out how to delete the old file and it won't copy the new until the old is gone. So far I have:

/*
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.IO;

namespace ST_be5f0817a6b54483a96a8c9e79402175.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
    };
    #endregion

    /*
    The execution engine calls this method when the task executes.
    To access the object model, use the Dts property. Connections, variables, events,
    and logging features are available as members of the Dts property as shown in the following examples.

    To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
    To post a log entry, call Dts.Log("This is my log text", 999, null);
    To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

    To use the connections collection use something like the following:
    ConnectionManager cm = Dts.Connections.Add("OLEDB");
    cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

    Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

    To open Help, press F1.
*/

    public void Main()
    {
        string fileDir = (string)Dts.Variables["fileDir"].Value;
        string SPDir = (string)Dts.Variables["SPDir"].Value;

        if (File.Exists(Path.Combine(SPDir, "filename.CSV")))
            {
                File.Delete(Path.Combine(SPDir, "filename.CSV"));
            }

        File.Copy(Path.Combine(fileDir, "filename.CSV"), Path.Combine(SPDir, "filename.CSV"));


        Dts.TaskResult = (int)ScriptResults.Success;
    }
}
}

I think I can delete the file by running a data flow component with the sharepoint list connector before the script and have the script just copy the file over, but I'm trying to avoid that many components and connections and that method just generally sounds more complicated.

Any help or advice would be welcome.

It's not deleting the file that's currently on Sharepoint. If I let it run as is then I get an error that the file already exists. If I comment out the if statement and the copy statement and just leave the delete statement, it doesn't give an error but also doesn't delete the file. — M T, Nov 10 '14 at 18:43
When you run it in the debugger, is it hitting the File.Delete, line, or is the if() testing false? — Tab Alleman, Nov 10 '14 at 18:50
As far as I can tell, it's hitting the delete line. I commented out the if part so that it reads //if (File.Exists(Path.Combine(SPDir, "filename.CSV"))) { File.Delete(Path.Combine(SPDir, "filename.CSV")); } //File.Copy(Path.Combine(fileDir, "filename.CSV"), Path.Combine(SPDir, "filename.CSV")); This doesn't give an error. Sorry, I'm not sure how to add line breaks here.. I thought it was supposed to be double spaces but that doesn't seem to do it. — M T, Nov 10 '14 at 19:04
There looks to be a lot of useful info in this post: http://stackoverflow.com/questions/6391711/how-to-delete-a-file-e-g-if-exist-delete-xxxxxxxx What if you try a test without the Path.Combine and just put in the full path to the file? — Tab Alleman, Nov 10 '14 at 19:13
It works with the full path, but I can't leave it like that. The path will change slightly on the production server vs the dev server. I'll have to figure out why there's a difference. Thanks for your help. — M T, Nov 10 '14 at 19:19
I made this harder than I had to. File.Copy(Path.Combine(fileDir, "filename.CSV"), Path.Combine(SPDir, "filename.CSV"), true); overwrites the existing file. — M T, Nov 10 '14 at 20:05

score 0 · Accepted Answer · answered Nov 20 '14 at 20:43

Fixed this issue by changing the relevant line to: File.Copy(Path.Combine(fileDir, "filename.CSV"), Path.Combine(SPDir, "filename.CSV"), true);

The boolean argument specifies whether to overwrite the existing file and is "false" if not specified.

SSIS script delete or replace Sharepoint library file

1 Answers1