DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports Events Over 2 million developers have joined DZone. Join Today! Thanks for visiting DZone today,
Edit Profile Manage Email Subscriptions Moderation Admin Console How to Post to DZone Article Submission Guidelines
View Profile
Sign Out
Refcards
Trend Reports
Events
Zones
Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
  1. DZone
  2. Data Engineering
  3. Data
  4. Loading Reference Data from a SharePoint List using SSIS

Loading Reference Data from a SharePoint List using SSIS

Nick Haslam user avatar by
Nick Haslam
·
Feb. 05, 12 · Interview
Like (0)
Save
Tweet
Share
9.19K Views

Join the DZone community and get the full member experience.

Join For Free

recently, i’ve been working on a project where the reference data is stored in sharepoint lists. while it is possible to get the information out of the sql server database directly, using something like the t-sql below, it’s a bit messy.

select      dbo.userdata.tp_id,
   dbo.userdata.tp_listid,
   dbo.userdata.tp_author,
   dbo.userdata.nvarchar1,
   dbo.userdata.nvarchar2,
   dbo.userdata.nvarchar3
from            dbo.lists
inner join
                 dbo.userdata on dbo.lists.tp_id = dbo.userdata.tp_listid
 where    (dbo.lists.tp_title like 'testlist')

i wasn’t able to use this to get the data out, as the client doesn’t allow direct access to the sharepoint database, which is entirely reasonable, given that it’s their corporate intranet.

to get around this, i found a very useful set of additional modules for integration services ( http://sqlsrvintegrationsrv.codeplex.com/ ), one of which is a sharepoint list source and destination. these then allow you to read the data directly.

using the sharepoint list source & destinations

1. the first step is to download the sharepoint list source and destination module from http://sqlsrvintegrationsrv.codeplex.com/ , and install it.

2. having done that, you need to start up bids (bi development studio / vs 2008) and create an ‘integration services package’.

3. you’ll need to add the two new data flow items into the toolbox (in tools > choose toolbox items, in the ssis data flow items section)

image

4. add a dataflow task to the control flow in the ssis package.

image

5. right click on the connection manager section at the bottom of the control flow, and choose spcred (connection manager for sharepoint connections). click ok, when the dialog for the sharepoint connection opens.

image

6. then drill into the data flow task, to take you to the data flow. in there, drag in a sharepoint list source

image

7. right click on the list source, choose show advanced editor. in the connection managers tab, pick the sharepoint connection you created in step 5.

image

8. next, click on the component properties tab. in this tab, you need to specify the name of your sharepoint list (sitelistname) and the url of your sharepoint server (siteurl). the siteurl is the parent site within which your list appears. if you want to filter the information from sharepoint, you can modify the camlquery section in here, and add a sharepoint caml query .

image

9. once you’ve populated this, click on refresh, and if everything is working, you’ll be able to move to the next tab. if there are errors (such as an incorrect siteurl), you’ll get errors like the one below.

image

10. moving on to the column mappings tab, then gives you a list of fields and mappings, representing the available fields from sharepoint (on the left) and fields that will be available to pass out of the list source (on the right). you can remove fields that are not relevant here, if you’d like, then click ok, to return to the data flow.

image

11. we need to add an ole db connection manager, by right clicking connection managers at the bottom, and choosing ‘new ole db connection’.

12. to get the sharepoint list contents into a database table, we need to add an ole db destination, so drag that into the data flow and hook the green output from the sharepoint list source to the top of the ole db destination. you’ll then see that there is a red x on the ole db destination, so we need to make some changes.

image

13. since we need to make changes to the ole db destination, double click on the ole db destination. as shown below, we need to specify a table for the sharepoint data to go to. the drop down list has a list of the tables in the database connected to the ole db connection manager, so pick a table (if you’ve made one already) or click new to create a new table.

image

14. then click ‘mappings’ on the left, and it’s possible to link the field in the source (sharepoint list) to your destination table.

image

15. you’ll then be able to run this ssis package, and assuming all is running successfully, you’ll see green boxes.

image

note: any text fields that are stored in sharepoint lists, are stored as unicode strings in the database (so nvarchar).

further documentation on using these adapters is available here .

source: http://blog.nhaslam.com/2012/01/26/loading-reference-data-from-a-sharepoint-list-using-ssis/

SharePoint Data (computing) Database Reference data

Opinions expressed by DZone contributors are their own.

Popular on DZone

  • Implementing Adaptive Concurrency Limits
  • How To Avoid “Schema Drift”
  • Comparing Flutter vs. React Native
  • Hackerman [Comic]

Comments

Partner Resources

X

ABOUT US

  • About DZone
  • Send feedback
  • Careers
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 600 Park Offices Drive
  • Suite 300
  • Durham, NC 27709
  • support@dzone.com
  • +1 (919) 678-0300

Let's be friends: