In this article we will show how to use a stored procedure included in the RSSBus SSIS Components for SharePoint to download files from SharePoint. While the article uses the RSSBus SSIS Components for SharePoint, the same process will work for any of our SSIS Components.
Step 1: Open Visual Studio and create a new Integration Services Project.
Step 2: Add a new Data Flow Task to the Control Flow screen and open the Data Flow Task.
Step 3: Add an RSSBus SharePoint Source to the Data Flow Task.
Step 4: In the RSSBus SharePoint Source, add a new Connection Manager, and add your credentials for the SharePoint site.
Step 5: Now from the Table or View dropdown, choose the name of the Document Library that you are going to back up and close the wizard.
Step 6: Add a Script Component to the Data Flow Task and drag an output arrow from the 'RSSBus SharePoint Source' to it.
Step 7: Open the Script Component, go to edit the Input Columns, and choose all the columns.
Step 8: This will open a new Visual Studio instance, with a project in it. In this project add a reference to the RSSBus.SSIS2008.SharePoint assembly available in the RSSBus SSIS Components for SharePoint installation directory.
Step 9: In the 'ScriptMain' class, add the System.Data.RSSBus.SharePoint namespace and go to the 'Input0_ProcessInputRow' method (this method's name may vary depending on the input name in the Script Component).
Step 10: In the 'Input0_ProcessInputRow' method, you can add code to use the DownloadDocument stored procedure. Below we show the sample code:
String connString = "Offline=False;Password=PASSWORD;User=USER;URL=SHAREPOINT-SITE";
String downloadDir = "C:\\Documents\\";
SharePointConnection conn = new SharePointConnection(connString);
SharePointCommand comm = new SharePointCommand("DownloadDocument", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.Clear();
String file = downloadDir+Row.LinkFilenameNoMenu.ToString();
comm.Parameters.Add(new SharePointParameter("@File", file));
String list = Row.ServerUrl.ToString().Split('/')[1].ToString();
comm.Parameters.Add(new SharePointParameter("@Library", list));
String remoteFile = Row.LinkFilenameNoMenu.ToString();
comm.Parameters.Add(new SharePointParameter("@RemoteFile", remoteFile));
comm.ExecuteNonQuery();
After saving your changes to the Script Component, you can execute the project and find the downloaded files in the download directory.
SSIS Sample Project
To help you with getting started using the SharePoint Data Provider within SQL Server SSIS, download the fully functional
sample package. You will also need the SharePoint SSIS Connector to make the connection.
You can download a free trial here.
Note: Before running the demo, you will need to change your connection details in both the 'Script Component' code and the 'Connection Manager'.
