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?
- Upgrade the package from SQL 2008 to SQL 2012. I did this by opening the project in Data Tools and following the Upgrade wizard
- You will get a nasty message about the SharePoint list component
- If you look in C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents you will see SharePointListAdapters.dll
- Re-install the SharePointList adapterc
- The same dll will now also exist in C:\Program Files\Microsoft SQL Server\110\DTS\PipeLineComponents
- Open up Data Tools and the package that has been upgraded
- You will notice an error condition on the data flow component that contains the task for the SharePoint source on the control fow
- Switch to this data flow component
- Right click and select SSIS Toolbox
- Important - click on the Toolbox panel and choose Refresh Toolbox
- Hopefully you should see both the source and destination components in the common section of the toobox
- 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:
- Open Business Intelligence Development Studio, open a package, and then click Choose Toolbox Items on the Tools menu.
- 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.
- 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:
- Open SQL Server Management Studio and connect to your SQL Server
- Under your Server -> Security -> Credentials; Add a new Credential with the login needed for your SharePoint.
- 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".
- 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;#