CODEWITHSUNDEEP
c#sharepointssis
vladarde
vladarde

Reputation: 23

ssis script task to create folders in Sharepoint document library

I am trying to create an SSIS package that gets a folder tree structure from a remote SQL DB and recreates that structure in a document library in Sharepoint.

I tried to (hard) code a Script Task to create just one folder but I'm getting this error: http://img856.imageshack.us/img856/1386/helpel.png

After running only a part of this script: 

Microsoft.Sharepoint.Client.dll
Microsoft.Sharepoint.Client.Runtime.dll

public void Main()
    {
        ClientContext clientContext = new ClientContext("http://spdemo.example.com/");
        clientContext.Credentials = new System.Net.NetworkCredential("admin", "password", "SPDEMO");
        Web rootWeb = clientContext.Web;

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

I've scoured the internet for solutions, but haven't found something that works for me.

So basically i need to find out how to:

  1. create a folder
  2. populate it with sub-folders
  3. Copy files in bitestreams from SQL to Sharepoint, all in SSIS

Thanks in advance! Regards, Vlad Ardelean

Upvotes: 1

Views: 5594

Answers (2)

gmaran23
gmaran23

Reputation: 2248

Did you try running your code from a stand-alone windows application written in C# in Visual Studio? If I get errors in SSIS, my preference is to test that code thoroughly in Visual Studio and then port it to a script task in SSIS.

If you haven't done folder creation and file copy to SharePoint, the below links might help.

  1. Create a folder - http://thingsthatshouldbeeasy.blogspot.in/2009/09/how-to-add-folder-to-sharepoint-list.html

  2. You can do the create folder step recursively untill you have created the destination tree structure.

  3. Copy Files - http://msdn.microsoft.com/en-us/library/ms454491.aspx, http://msdn.microsoft.com/en-us/library/ms470176.aspx

I dont understand what you mean by copy file in bitestreams from SQL. If you want to read binary data from SQLServer via ADO.Net use System.Data.SqlDbType.VarBinary

Read the data from SQL server to a stream and write to a temp file, copy that temp file from local to SP. Or you may even write the data in a stream to SP directly without a temp file.

Upvotes: 0

vladarde
vladarde

Reputation: 23

After some research I found out that in order to reference a DLL in a SSIS Script Task it first has to be strong named

Instead, I have found an easier way to create folders and upload files:

public void CreateFolder(string url)
    {
        HttpWebRequest request = (System.Net.HttpWebRequest)HttpWebRequest.Create(url);
        request.Credentials = new NetworkCredential(user, pass);
        request.Method = "MKCOL";
        HttpWebResponse response = (System.Net.HttpWebResponse)request.GetResponse();
        response.Close();
    }

public void UploadDocument(byte[] file, string destinationName)
    {
        byte[] buffer = file;

        WebRequest request = WebRequest.Create(destinationName);
        request.Credentials = new System.Net.NetworkCredential(user, pass);
        request.Method = "PUT";
        request.ContentLength = buffer.Length;

        BinaryWriter writer = new BinaryWriter(request.GetRequestStream());
        writer.Write(buffer, 0, buffer.Length);
        writer.Close();

        HttpWebResponse response = (HttpWebResponse)request.GetResponse();
        response.Close();
    }

url : represents the url path of the folder you want to create

http://spsite.host.com/DocLib/FolderToCreate

destinationName: path + document name 

http://spsite.host.com/DocLib/FolderToCreate/DocToCreate.docx

Upvotes: 1

Related Questions