Common Questions / Answers

MSDN Documentation: http://msdn.microsoft.com/en-us/library/hh368261.aspx
MVP Article Sample (Ivan Sanders): http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx

Setup and Execution

Q: What version of Windows Installer is needed to install the adapters?

  • You need Windows Installer 4.5 - http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=8483

Q: After upgrading SQL 2008 -> 2012, I'm not seeing the adapters - How to fix?

  1. Upgrade the package from SQL 2008 to SQL 2012. I did this by opening the project in Data Tools and following the Upgrade wizard
  2. You will get a nasty message about the SharePoint list component
  3. If you look in C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents you will see SharePointListAdapters.dll
  4. Re-install the SharePointList adapterc
  5. The same dll will now also exist in C:\Program Files\Microsoft SQL Server\110\DTS\PipeLineComponents
  6. Open up Data Tools and the package that has been upgraded
  7. You will notice an error condition on the data flow component that contains the task for the SharePoint source on the control fow
  8. Switch to this data flow component
  9. Right click and select SSIS Toolbox
  10. Important - click on the Toolbox panel and choose Refresh Toolbox
  11. Hopefully you should see both the source and destination components in the common section of the toobox
  12. Unfortunately you have to redo the settings for the SharePoint component. Its a bit of a pain but a bit of copy and paste from the old SQL 2008 solution (which you should have backed up before the migration) gets the package working in a couple of minutes.

Q: I'm getting PerformUpgrade errors and with the new adapters, my components do not load, help!

  • A one-time publickey change was made which requires you to replace: 933a2c7edf82ac1f with f4b3011e1ece9d47 in your DTSX files.

Q: The SharePoint Adapters do not show up in my toolbox after installation

  • This can happen when the components are installed near the SQL engine, but not on the drive with the actual SSIS components. Here is the workaround:
    1. Open Business Intelligence Development Studio, open a package, and then click Choose Toolbox Items on the Tools menu.
    2. In the Choose Toolbox Items dialog box, click the SSIS Data Flow Items tab, and then check SharePoint Destination and SharePoint Source and click OK.
    3. Or, using the above steps, look at the other components in the toolbox to get an idea where the other components are installed at - and then reinstall the SharePoint components using the corrected path.
  • The SharePoint source and destination components should now appear in the toolbox for the data flow task. You can add the source and destination components to the data flow of the package.

Q: When using the SharePoint Credential, it works in Design but not at Runtime

Q: OR... The list adapters work in Design mode, but do not work at runtime (Unauthorized error).

  • This is because you are not using the default user and need to create a safe location to store the credential.
  • Check out the following site for how to store the username/pw so that you can run it outside of the designer: http://support.microsoft.com/kb/918760

Q: How do you suggest I use this in SSIS and execute it in my organization?

  • One way to setup this SSIS, is to use SQL Agent to schedule the job. However, make sure that the "Type" of the SQL Job Step is set to SQL Server Integration Services Package. After doing so, you will soon find that the package is executed using the account running Sql Agent. That can be changed doing the following steps:
    1. Open SQL Server Management Studio and connect to your SQL Server
    2. Under your Server -> Security -> Credentials; Add a new Credential with the login needed for your SharePoint.
    3. Under SQL Server Agent -> Proxies -> SSIS Package Execution; Add a new Proxy and select the Credential you just created in the field marked "Credential Name".
    4. Open the SQL Job Properties and on the step which runs your SSIS Package, the dropdown will now have the Proxy you created to run the Job under a different user account.

Q: Is it possible to make an ssis variable and pass this to the SITEURL in the SharePoint List Source?

Q: I am not seeing any of the new components after installation?

  • If you had VS opened during installation, then be sure to close it and reopen it to see the sharepoint components.

Pulling Data from SharePoint

Q: I'm getting this error: System.Net.WebException: The remote server returned an error: (417) Expectation failed.

  • Check your proxy settings in IE on the machine executing the DTS Package, as one user indicated Squid proxy settings caused this issue for them.

Q: Using the SiteListViewName, I still see all of the columns of the original list

  • This is because views only filter the data rows from the backend that we're querying against, they do not filter the column data. If you want to remove columns from the Adapter, you can use the advanced tab and simply remove them. See the "Selecting Columns" Section of the MSDN Instructions.

Q: Wow, this adapter is SLOW to transfer my data! I Selected the source listview and am not happy.

  • Default usage selects every column in SharePoint. Often, most columns are extraneous or not necessary for your ETL. Those can be removed, see the "Selecting Columns" section of the MSDN Instructions.
  • Secondly, you can also have multiple SharePoint Sources in a dataflow pointing to the same list, bringing different columns (and the ID) and join them up using SSIS to parallel load lists with many columns.

Writing Data into SharePoint

Q: When updating my SharePoint List, every row is marked as modified, is there anything wrong with this?

  • Technically, no. However, it means the job will run slower as all of the list data is being sent to SharePoint to update each time. A more efficient way, since you are already updating particular Row IDs (if updating) is to use the SSIS components to filter and pass only the changed rows into the Destination Component. That will provide a more specific update and speed up the entire process significantly.
  • Here is one MSDN article on using the split function for doing this: http://msdn.microsoft.com/en-us/library/ms137886.aspx

Q: Why are columns not showing up in the Destination Adapter, when there are numerous fields in the List?+

  • The destination columns are filtered to excluded fields SharePoint has identified as readonly or hidden.

Q: What Datatype should I Use?

  • The easiest way to find out what to use to put things in SharePoint, is to use the source adapter and take a sample row out of SharePoint.
    • Number Columns: Any Float type, Any Integer Type, or Currency (based on your data)
    • Date/DateTime Columns: DT_DBTIMESTAMP, UTC format is assumed.
    • Yes/No Columns: Single Byte Integer or DT_BOOL
    • Text (Single Line): DTWSTR or DTNTEXT
    • Text (Multi-Line): DTWSTR or DTNTEXT (SharePoint auto-formats this to HTML)
    • Choice Columns: DTWSTR or DTNTEXT: This is a coded string which begins and ends with ;# Values are strings and separated by ;# Example: ;#First;#

Last edited Jan 23, 2013 at 12:55 PM by kevinidzi, version 19

Comments

fdaske Mon at 7:36 AM 
You can also follow these instructions to sync SQL with SharePoint:
http://www.layer2solutions.com/en/community/FAQs/cloud-connector/Pages/SQL-Sync-to-Office365.aspx

It works for almost any data source (e.g. OData, XML, Exchange, web services, file shares) not SQL only.

frankskfoo Dec 18, 2013 at 4:45 AM 
Why in Destination Adapter I couldn't see sharepoint lists output column?
How do I map input column to sharepoint lists column?

r_ean Sep 11, 2013 at 7:10 PM 
i had a similar issue when my machine password expired, followed the instructions on this site and package worked perfectly. credits to http://sqlstuff.weebly.com/ssis.html
suggested further reading on the dll http://support.microsoft.com/kb/555631

dasari_ramacharan May 27, 2013 at 1:21 PM 
Even after install you do not see the "Sharepoint Connection manager' after installing your package on your deployment server then check that this file exists C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents\SharePointListAdapters.dll For me it failed to install so I created an Administrator command prompt and re-ran the installation and this time it worked. Thanks 'DavidBridge ' for the comment. Hi Team Please add this in the FAQ.

BobSanders Feb 13, 2013 at 12:17 PM 
Is there any sample code of dynamically creating a CAML query using an expression - for example to pass in a "last-run" date to use for incremental loads?

kevinidzi Aug 3, 2011 at 6:22 AM 
PLEASE post general questions in the discussion thread. Comments about FAQ related items are relevant here.

kevinidzi Jul 28, 2011 at 3:27 PM 
Paging should be handled automatically and there is a field for BatchSize which can be set to tune it down some.

brettvp Jul 21, 2011 at 10:27 PM 
Can you provide a sample of paging thru large result sets that would otherwise overflow the buffer.