Getting Sharepoint list into SSIS

Download the setup:

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

Follow the guide if it doesn't appear as source/destination in your SSIS toolbox

http://dataqueen.unlimitedviz.com/2011/06/how-to-use-a-sharepoint-list-as-a-data-source-in-your-ssis-package/


Derived column:

TRIM( SUBSTRING( YOUR_COLUMN_NAME, FINDSTRING( YOUR_COLUMN_NAME, “;#”, 1 ) +2, 255)

example:
TRIM(SUBSTRING([Project ID*], FINDSTRING([Project ID*], ";#", 1 ) +2, 255) )


to clean ;# when Sharepoint list has lookups
http://www.viamacchina.com/2013/08/ssis-and-sharepoint-list-sources-with.html

or clean via script component:
http://microsoft-ssis.blogspot.nl/2010/12/do-something-for-all-columns-in-your.html

// This script adjusts the value of all string fields

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Reflection;            // Added

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    //  Method that will be started for each record in you dataflow

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        // Use Reflection to loop through all the properties of Row:

        // Example:

        // Row.Field1 (String)

        // Row.Field1_IsNull (Boolean)

        // Row.Field2 (String)

        // Row.Field2_IsNull (Boolean)

        foreach (PropertyInfo p in Row.GetType().GetProperties())

        {

            // Do something for all string properties: Row.Field1, Row.Field2, etc.

            if (object.ReferenceEquals(p.PropertyType, typeof(string)))

            {

                // Use a method to set the value of each String type property

                // Make sure the length of the new value doesn't exceed the column size

                p.SetValue(Row, DoSomething(p.GetValue(Row, null).ToString()), null);

            }

        }

    }

  

    // New function that you can adjust to suit your needs

    public string DoSomething(string ValueOfProperty)

    {

        // Uppercase the value

        ValueOfProperty = ValueOfProperty.ToUpper();

        return ValueOfProperty;

    }

}




//other notes:




Make sure to install the sharepoint list addon on the server side as well, and ensure the sql server agent has access to the sharepoint site.

That can be done by making a credential (with you Windows Login), and then a SSIS Package Exection Proxy with that credential. But ensure that the sql server actually can access localpages on the LAN..




To get sql server agent account name in case you want to add it to sharepoint:




SELECT DSS.servicename, DSS.startup_type_desc, DSS.status_desc, DSS.last_startup_time, DSS.service_account, DSS.is_clustered, DSS.cluster_nodename, DSS.filename, DSS.startup_type, DSS.status, DSS.process_id FROM sys.dm_server_services AS DSS;




DSS.service_account = domain\username