Apr 242014
 
Share

A while ago I showed how to upload a file to a Sharepoint Document Library using a File Task in SSIS.

Here’s another solution…Simply cut and paste the following into a Script Task in your SSIS package. This is the simple version as the local file,
Sharepoint URL and service account credentials are hardcoded. You will also need to target your script task for .Net Framework 3.5


using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using Microsoft.SqlServer.Dts.Tasks.ScriptTask;
using System.Windows.Forms;
using System.Net;
using System.IO;

namespace ST_99ff82c376eb40a2829b74f045a2f3be

{

[System.AddIn.AddIn(“ScriptMain”, Version = “1.0”, Publisher = “”, Description = “”)]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
WebClient myWebClient;
string LocalFileName;
string DestinationURL;
bool FireAgain = true;

try

{

myWebClient = new WebClient();
LocalFileName = “F:\SomeFolder\SomeFile.csv”;

// Destination file name with year and month appended ( SomeFile_04-23-2014.csv )

DestinationURL = “https://servername/documentlibrary/somefile_” + DateTime.Now.ToString(“MM-dd-yyyy”) + “.csv”;

Console.WriteLine(LocalFileName);

myWebClient.Credentials = new NetworkCredential(“username”, “password”,”domain”);
myWebClient.Credentials = CredentialCache.DefaultNetworkCredentials;

Dts.Events.FireInformation(0, String.Empty, String.Format(“Uploading {0} to {1}”, LocalFileName, DestinationURL), String.Empty, 0, ref FireAgain);

// upload the file

myWebClient.UploadFile(DestinationURL,”PUT”, LocalFileName);

}
catch (Exception ex)
{
// Catch and handle error
Dts.Events.FireError(0, String.Empty, ex.Message, String.Empty, 0);
}
}
}
}