Recently I was tasked to create an SSIS application that will query a database, split the results with certain criteria and create CSV file for every result and upload the file to a Sharepoint Document Library site. I've search the web and compiled the steps I've taken to build the solution.

Summary:

A) Create a proxy class of WSS Copy.asmx.

B) Create a wrapper class for the proxy class and add a mechanism to check if the file is existing and delete method.

C) Create an SSIS and call the wrapper class to transfer the files.

 

A) Creating Proxy Class

1) Go to Visual Studio Command Prompt type wsdl http://[sharepoint site]/_vti_bin/Copy.asmx this will generate the proxy class (Copy.cs) that will be added to the solution.

2) Add Copy.cs to solution and create another constructor for Copy() that will accept additional parameters url, userName, password and domain.

 

public Copy(string url, string userName, string password, string domain) { this.Url = url; this.Credentials = new System.Net.NetworkCredential(userName, password, domain); }

3) Add a namespace. 

 

 B) Wrapper Class

Create a C# new library that references the Proxy Class.

 

 

 

 

C) Create SSIS

SSIS solution is composed of:

 

1) Execute SQL Task, returns a single column rows containing the criteria.

2) Foreach Loop Container - loops per result from query (SQL Task) and creates a CSV file on a certain folder.

3) Script Task - calls the wrapper class to upload CSV files located on a certain folder to targer WSS Document Library

Note: I've created another overload of CopyFiles that accepts a Directory Info instead of file location that loops thru the contents of the folder.

Designer View

Variable View