Sometimes you run into a situation where you want to allow users to update data in a simple fashion, and incorporate the data into a data warehouse. For example the client’s ERP system maintains the data element called Departments but doesn’t have the ability to store Department Groups for reporting since it is not relevant to the ERP system. Using a SharePoint list as a data source can be an easy solution. Users can update it very simply, your ETL package can add or update new Departments from the ERP system via the data warehouse, and you can pull the user entered data back into the data warehouse. There are some drawbacks to it, such as lack of data integrity checks on the SharePoint side. But if you build your ETL package right, you can correct any integrity issues which might be created by user error.
Here is what I will demonstrate, using a SharePoint list to manage the applying Department Groups to Departments which are maintained in the accounting system:
PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS
PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE
PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST
PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST
PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST
PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE
PART 1 – DOWNLOAD AND INSTALL SSIS TOOLBOX ITEMS
1. From the server where you will be building the SSIS solution, open your browser and navigate to this URL. http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652
2. Download, run and follow the instructions to install SharePointListAdaptersSetup.msi (2008) or SharePointListAdaptersSetupForSqlServer2005.msi
3. Close BIDS if it is open
4. Open BIDS from the Tools menu select Choose Toolbox Items. Go to the SSIS Data Flow Items tab. – **See SQL Server 2012 Update at the end of this post.
5. Add a checkmark to the SharePoint List Destination and SharePoint List Source items.
6. Open up or create a new Integration Services solution. Go to the Data Flow tab.
7. Open up the Toolbox
8. Drag SharePoint List Source from the General section…
…to the Data Flow Sources section.
9. Drag SharePoint List Destination from the General section…
…to the Data Flow Destinations section.
Now you are ready to use a SharePoint List as a source or destination in your packages.
PART 2 – USE THE SHAREPOINT LIST AS A DATA SOURCE
1. Create a Data Flow in your package.
2. Go to the Data Flow tab and drag the SharePoint List Source into the data flow as your source.
3. Double click on the source. Scroll down to the bottom where it says SiteURL.
4. Paste the URL for the SharePoint site into the SiteURL (exclude anything from the word Lists onward in the URL)
Example: if your SharePoint site URL for Lists is
http://ca-shpt01/Reporting/Lists/Department%20Groups/AllItems.aspx
then only type
5. Type the SiteListName. For example if your List name is Departments,
then type “Departments”.
6. If you are wanting to import data from a particular view of this list in SharePoint, other than the default view, “MyView” for example:
then add the name of the view in the SiteListViewName, otherwise leave it blank.
7. Click the Refresh button at the bottom of the box to load the SharePoint List metadata into your Data Flow.
8. Then click on the Column Mappings tab to see the columns available from your SharePoint list.
9. Drag a Data Flow Destination onto your data flow and proceed as normal to pull data into your destination from the SharePoint list.
You’ll need to know a little about SharePoint lists to understand what data lives in which column. You’ll need the ID column if you will be doing any updating from the data warehouse into the SharePoint list. The Title is whatever item is the linked item in the SharePoint list – meaning if a user clicks on one of the items in the SharePoint list, which field is set up as the hyperlink to the item (usually the first column visible in the list).
Because there is often a push and a pull of data to and from the SharePoint list I tend to create a table in the data warehouse specifically for the data from the SharePoint list and I let the source data dictate the table structure by clicking on the New button for the “Name of the Table or view”. In this way, when the data gets pushed back to SharePoint there are no data type issues.
PART 3 – INSERT RECORDS INTO THE SHAREPOINT LIST
Keep in mind that the SharePoint list does not manage any data integrity, so if you add duplicate records with the same linked “Title”, Department Name in this case, you will get duplicate records in your list. Be sure to configure your data source to insert only new records, not existing records. Here we will add any new Departments that have been added to the ERP system (if any) and push them up to SharePoint.
1. Add a Data Flow to your package.
2. Go to the Data Flow tab and drag an OLE DB source as your source. Configure your source to pull any new Departments that you want to insert into the SharePoint list. In this example SP_Department is the data which has been imported from the SharePoint list, and DIM_Department holds the Departments imported from the ERP system:
SELECT �
DP.Department_Code,
DP.Department_Description
FROM SP_Department as SP RIGHT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
WHERE SP.ID IS NULL
3. Drag the SharePoint List Destination into the data flow as your destination.
4. Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above. In addition, ensure that the BatchType is set to Modification. “Modification” is used for Updates and Inserts, “Deletion” is of course for Deletions.
5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab. Map the Source columns to the Destination columns. Since we are INSERTING records, be sure NOT to map to the ID field. Doing so would have the data flow expect an update rather than an insert.
Note that the Department_Description is being mapped to the Title field, which is the linked SharePoint field described in Part 2 – item 9, above.
6. Run your data flow task and check the SharePoint list for the added records.
PART 4 – UPDATE RECORDS INTO THE SHAREPOINT LIST
Here we want to update the Department descriptions in SharePoint, in case a user has decided to edit it. The only item the user should be editing is the Department Group.
1. Add a Data Flow to your package.
2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to update the Department descriptions for Departments that already exist in the SharePoint list. For example:
SELECT �
SP.ID,
DP.Department_Code,
DP.Department_Description
FROM SP_Department as SP INNER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code
3. Drag the SharePoint List Destination into the data flow as your destination.
4. Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above. Esure that the BatchType is set to Modification.
5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab. Map the Source columns to the Destination columns. Since we are UPDATING records, BE SURE TO map to the ID field. Without the ID the data flow will implement an insert rather than an update.
6. Run your data flow task and check the SharePoint list for the updated Department Descriptions.
PART 5 – DELETE RECORDS INTO THE SHAREPOINT LIST
Here we want to delete any Department records from the SharePoint list that a user may have entered in error. The user training indicated to users that Departments should only be added from the ERP system and not by users.
1. Add a Data Flow to your package.
2. Add an OLE DB Source to the Data Flow tab and configure the source with the correct query to select the Departments for deletion from the SharePoint list. The only field you need in the result is the ID field which maps to the ID in the SharePoint List. If the ID is in this query it will get deleted from the SharePoint list. In this example we will remove any duplicate records, and remove any Departments that don’t exist in the ERP system:
–DELETE DUPLICATE Department Codes. Take the oldest record.
SELECT ID FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1)
AND ID NOT IN
(SELECT MIN(ID) FROM SP_Department
WHERE Department_Code IN (
SELECT DP.Department_Code FROM SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code�
GROUP BY DP.Department_Code HAVING COUNT (sp.id) > 1))
UNION
–DELETE Department Codes that don’t exist in ERPSELECT SP.ID FROM SP_Department as SP LEFT OUTER JOIN
dbo.DIM_Department as DP ON SP.Department_Code = DP.Department_Code
WHERE DP.Department_Code IS NULL
3. Drag the SharePoint List Destination into the data flow as your destination.
4. Double click on the SharePoint List Destination and configure the SiteUrl, SiteListName and SiteListViewName the same way you did in Part 2 steps 4-6 above. In addition, ensure that the BatchType is set to Deletion.
5. Click on the Refresh button in the bottom left of the Advanced Editor window and select the Input Columns tab. Map the ID input column to the ID destination column.
6. Run your data flow task and check the SharePoint list for the deleted Departments.
PART 6 – BUILD DATA INTEGRITY INTO YOUR PACKAGE
Putting this all together will enforce the data integrity for this simple list. Here is what this package looks like.
Here is what each step does.
1. Truncate Tables: Truncates these tables –
DIM_Department
DIM_Department_Group
SP_Department
2. Populate DIM_Department – populates the DIM_Department table from the ERP system.
3. Retrieve Dept Group from SP – Populates the DIM_Department_Group table from another SharePoint List. This is used as a drop down in the Department SP list for users to select the correct department.
4. Retrieve SP_Depts – Populates the SP_Department table from the SharePoint list. See Part 2 above.
5. Update SP Dept Descriptions – updates any modified Department descriptions. See Part 4 above.
6.Add Missing Depts to SP – adds any new or missing Departments to the SharePoint list. See Part 3 above.
7. Delete invalid Depts from SP – deletes any invalid Departments from the SharePoint list. See Part 5 above.
8. Truncate SP_Depts – Truncate the SP_Department table in preparation for reload of corrected data.
9. Retrieve Updated SP_Depts – Populate SP_Department table from the updated SharePoint list. See Part 2 above.
10. Update Dept Group Codes – Updates the DIM_Department table with the Department_Group_ID.
UPDATE DIM_Department
SET Department_Group_ID = SP.Department_Group_ID
FROM SP_Department AS SP INNER JOIN
DIM_Department as DP on DP.Department_Code = SP.Department_Code
This may seem like a lot of work, but it actually doesn’t take that much time once you get the hang of it. Of course if you have more complex lists or require a lot of lists to manage this kind of data, you will want to look into other tools. This is a good solution to the occasional one-off with a fairly simple data requirement, where the client doesn’t have another tool available to handle it.
Update April 24, 2012
If you are populating date fields, see this post. http://thedataqueenblog.azurewebsites.net/2012/04/populating-date-fields-in-sharepoint-using-ssis/
Update May 2, 2012
SQL Server 2012: All components that are installed on the local machine now automatically appear in the new SSIS Toolbox. When you install additional components, right-click inside the toolbox and then click Refresh Toolbox to add the components. The SharePoint List Destination and the SharePoint List Source appeared in the “Common” folder in the SSIS Toolbox when I refreshed the toolbox.
Hey Martina – thanks for your help! I needed to find out how to delete records in a sharepoint list and you (and Bing) had the answer!
Thanks Martina, I was given this task to do and was struggling until I found your very informative post.
Had the package done in a tick. Great Stuff.
Thanks for you post. Glad it helped! Martina
I use Microsoft SQL Server 2012 with SharePoint Server 2010. SQL 2012 has replaced BIDS with Server Data Tools. I’ve installed the lastest version os SharePointListAdpater I can find online but when i select add new items in toolbox the SSIS Data Flow and Control Items tabs aren’t displayed.
Please does anyone know how to accomplish this same task using SQL server 2012 with server data tools? Thank you
All components that are installed on the local machine now automatically appear in the new SSIS Toolbox. When you install additional components, right-click inside the toolbox and then click Refresh Toolbox to add the components. The SharePoint List Destination and the SharePoint List Source appeared in the “Common” folder in the SSIS Toolbox when I refreshed the toolbox.
How do you install additional components…That is the part I’m missing?
Never mind… I have found the answer to my question…I have to click on the Data Flow tab in Visual Studios 2010 to get the options under Common in the SSIS toolbox.
Cool, glad you found it.
Martina
I was using SSIS to import the data from my SQL Server to SP2010, I create a column in my SQL called SPID, bascailly this column will contain NULL when it’s a new record, but the problem now is this value will always be NULL as SSIS wouldn’t update the SharePoint ID back to my SQL, any idea how do I accomplish this? Thanks in advanced.
Hi,
Thanks for your question. You will need to use two different data flows, one for new records and one for updates. For more details on updating SharePoint lists and using SSIS to maintain data integrity in your SharePoint lists, you might find this article helpful – Maintaining Data Integrity of a SharePoint list with SSIS. If you are using InfoPath forms, you might rather use the combination of the SharePoint list and the InfoPath form to manage the data integrity. Check out this blog post for best practices on this subject. It talks about cascading lists, but the principles apply to single lists as well – Best Practices for Cascading SharePoint lists
Cheers,
Martina
Hi Kathy,The script task is silmpe and I included it below. It sets the sheetname and also builds the column list. In the SQL Task I set the SQLSourceType to Variable and the SourceVariable then becomes User::SheetTable. I can’t add a screenshot of the whole thing here, but I start with the script task, then the SQL task, then the data flow I showed in the original post.Public Sub Main() Add your code here Dim strDate As String = FormatDateTime(Now, DateFormat.ShortDate).ToString strDate = Replace(strDate, / , _ ) Dts.Variables( SheetName ).Value = DBSize_ strDate Dts.Variables( SheetTable ).Value = CREATE TABLE `DBSize_ strDate ` (`ServerName` NVARCHAR(50),`DBName` NVARCHAR(50), `Name` NVARCHAR(50), `LastRunDate` DateTime, `PriorRunDate` DateTime, `LastTotalSizeMB` Decimal(8,2), `PriorTotalSizeMB` Decimal(8,2),`TotalSizeDiff` Decimal(8,2),`LastUsedSpaceMB` Decimal(8,2), `PriorUsedSpaceMB` Decimal(8,2), `UsedSpaceDiff` Decimal(8,2), `LastFreeSpaceMB` Decimal(8,2), `PriorFreeSpaceMB` Decimal(8,2), `FreeSpaceDiff` Decimal(8,2)) Dts.Variables( SheetTable ).Value = Replace(Dts.Variables( SheetTable ).Value, Default , Dts.Variables( SheetName ).Value) Dts.TaskResult = ScriptResults.SuccessEnd Sub
Excellent work Martina, keep it up!
Made my life so much easier
Hi Martina…I am porting a package from SQL2005 to SQL2012 and have had good results except for one area…I am runing a
DataFlow(SP List Source—>RecordSet Destination)—>
(Foreach Loop Container—–Execute SQL Task—>DataFlow(SP List Source—>Script—>SP List Destination))
and trying to update a SP List…the item ID is in @User::ListItemID…
in SQL2005 I use a CAML query of:
0
and it worked fine…in 2012 I am not getting the Item ID passed into the SharePoint List Source and therefore not updating any Items…any siggestions?
lol…never mind …I got it…need to use an Expression:
“” + (DT_WSTR, 5) @[User::ListItemID] + “”
lost the CAML Query in the previous post
“0”
Is there a way to read files from a Share Point site without using the codeplex components?
Can this all be done in a script task? ssis 2008
Thanks
Hi Stever,
Assuming I am reading your question correctly you are asking about reading files, not SharePoint list items. Your approach may depend on what type of files you are reading. This is how I did it to copy MS Office Files from SharePoint to the File System. I believe the DavWWWRoot is specific to MS Office files.
Create a connection manager to the SharePoint folder like this
sharepointserverDavWWWRootmysitemylibrarymyfile.xls for a file, or this
sharepointserverDavWWWRootmysitemylibrary for a folder
Then use a File System Task as usual in your package to perform whatever action you choose (Copy Directory, Copy File, etc) to whatever location you choose.
On the server running SSIS, you may need to open Server Manager, Add Feature – Desktop Experience. This will allow WebDav to work. If you’re still having trouble you may have to install the WebDav components – http://www.microsoft.com/downloads/en/details.aspx?familyid=7554f536-8c28-4598-9b72-ef94e038c891&displaylang=en
I hope this helps you as a starting point.
Cheers,
Martina
Hi Martina – I have a share point Connection Manager (SharePoint Source from Pragmatic Works Task Factory) I can read the list data items – but how do I import the data in the associated infopath files for each of those records?
Thanks
Stever
How to update a lookup type field ?
Hi Luis,
You can’t update the displayed field which has been looked up. You may be able to update the field that the lookup is attached to. For example if you are displaying UserName in a UserGroups list, then the SharePointListID for that User is what is being stored in the UserGroups list lookup field.
IF
it is possible to update that field, you would need to send the data in the exact format that SharePoint is storing it. It likely looks something like 24;#Sarah Robinson. You could try pulling the lookup field into SQL and see how it is being stored, then try to update it.
Please also take a look at this Best Practices post. I avoid LookUp fields as they can cause difficulties, and this talks about other ways to handle it. http://thedataqueenblog.azurewebsites.net/2012/03/best-practices-for-cascading-sharepoint-lists-using-infopath-forms/
Cheers,
Martina
Martina,
Thank you very much for the step by step post. Everything works for me when running in development environment. However, when scheduling the package using SQL Server Agent. It failed on the sharepoint connection manager. Do you know how to fix it? kind of needing it urgently Do I need to install something on the DB server? Thanks a lot in advance!
The following is the Error Message:
DTS:Name=”DelayValidation”>0 CM_SharePoint {C49B478E-04A7-4C9B-A978-7F7AB5″ from node “DTS:ConnectionManager”. End Error Could not load package “package.dtsx” because of error 0xC0010014. Description: The package failed to load due to error 0xC0010014 “One or more error occurred. There should be more specific errors preceding this one that explains the details of the errors. This message is used as a return value from functions that encounter errors.”. This occurs when CPackage::LoadFromXML fails. Source: Started: 6:42:06 PM Finished: 6:42:07 PM Elapsed: 0.249 seconds. The package could not be loaded. The step failed.
Hi samsar,
You’ll want to set up permissions for your SQL Agent Service Account on the SharePoint site you are accessing in your package. It’s not ideal, but it’s either that or set up a Proxy on SQL Server Agent which has permissions to the site, and run the job as the Proxy. I find it easier to maintain just setting up the permissions for the service account. Make sure you set read/write permissions if the package is writing back to your SharePoint lists.
Cheers,
Martina
Be sure you have installed the SharePoint list adapters on your SSIS server as well, if it is a different machine from where you have being working in Visual Studio.
Cheers,
Martina
I am still trying to get the Connection SharePoint List Source to work in Visual Studio. I think it is a permission problem. I setup the Connection Manager with Custom Credentials that have Full Control to the site and the list. In the Advance Editor for SharePoint List Source I select the Connection Manager. Then under Componet Properties I enter the site URL to the site where the list lives example https://xxxxxx.xxxx.xxx/Site/SubSite/ Then I enter the List Name in the SiteListName, refresh and I get “Error at Data Flow Task [SharePoint List Source [1]}: System.ServiceModel.Security.MessageSecutiryException: …..” The custome Credentials should have Full access to the site and List. Any suggestions would be helpful.
Hi Guy,
That does look like a permissions error. Can you successfully sign into SharePoint as the custom user, navigate to the list and add an item to the list?
Cheers,
Martina
I am using this to pull data from a SharePoint 2010 List using SQL Server 2012 Data Tools. My SharePoint List Source is returning the List Definition (I verified using Fiddler to inspect the HTTP traffic) but no records – I have verified there are 2 records in the List that I should expect to see returned. I also do not get any results in the “Execution Results” Tab. I took the step of specifying a View but that did not help.
Any thoughts?
Thanks,
– Ian
Hi Ian, sorry not sure where the disconnect lies. I have never had an issue returning records. The tools version shouldn’t be a problem. You’re certain the view is not filtering out records, even by user?
Cheers,
Martina
Hi Martina,
I am trying to migrate data from SQL(2008) table to SharePoint(2010) List.But I was little bit confused with the SharePoint Credentials.Could you please help me with this.And also My SQL table is having 6 Columns But My List Should have 10 Columns.So I mapped these 6 Columns by making the other columns in the list Ignored.But Can’t figure out the error.Could you Please Help me Out.
Hi Lalitha,
For Insert into a SharePoint list, be sure that Batch Type is set to Modification, and that you DO NOT map the ID column. SharePoint will create an ID for each record inserted. If you are still having an error with the empty columns in SharePoint, then perhaps they List setting require them to have data. For example the Title column normally requires data and it must be filled in.
If you were able to get that far, I’m not sure what issue you are having the the credentials. If you are developing with a login which also allows you to seamlessly log in to SharePoint then you shouldn’t require credentials for SharePoint.
Cheers,
Martina
Hi Martina,
I love reading your detailed steps for SharePoint config. Would you mind me asking a frustrated question?
I was trying to upgrade a SSIS package from 2008 to 2012. Everything works except the SharePoint list Source, which showed no color. BTW, I have installed SharePoint list adaptor in the SSDT2012, and I can configue another SharePoint list source from scratch. However, I can neither EDIT the bad existing SharePoint list Source nor DELETE it. Have you met this kind of problem before? I am lookig forward to your reply.
Thanks a lot,
Larry
Hi Larry,
I ran into problem upgrading SharePoint list source/destinations in SSIS as well. I ended up recreating them from scratch. Sorry. 🙁
Martina
Thanks for taking the time to write all this up with the screenshots, it’s much better than my post on SharePoint List adapter, you’re making me want to re-write mine.
Cheers,
-TD
Thanks for the feedback Tony! Cheers, Martina
Hi Martina,
Informative post you have. Very detailed.
I have a question though when I try deleting from a sharepoint list and reinserting again the ID field on the SharePoint List doesn’t seem to reset to 1. How do I do this?
Thanks
The ID is controlled by SharePoint and can’t be reset to 1.
There is something wrong in your ROW_NUMBER. You need to group by something in order to have the row_number increase, in this example it is grouped by LAYOUT_CODE as indicated in the PARTITION BY portion of the statement.
SELECT
ROW_NUMBER() OVER(PARTITION BY Layout_Code ORDER BY Product_ID) AS Row
,[Layout_Code]
,[Product_ID]
,[Variant_Code]
FROM[DIM_Product]
Cheers,
Martina
Hi Martina ,
The Post is excellent . In my case I have a bulk data in SQL 2012 which is actually having Document of various format also . Now I need to move this to SharePoint 2013 Document library .
Could I use SSIS package as given . Any steps I need to change .What data type of document should I take at SQL DB end .
Actually the big picture of this requirement is I need to move documents from sales force to a SQL db VIA intermediate tool and then from SQL db to SharePoint 2013 .
Your suggestion will be really helpful for part of moving documents in SQL table to Sharepoint 2013 .
Thanks
Hi Karry, This answer is probably far too late, but I’ll answer it now in case it helps. Sounds like the intermediate tool may get in the way. If you can bring the file down to the file system from Salesforce, it can be dragged and dropped into SharePoint through OneDrive for Business (essentially a synchronized doc library). In fact, if the SalesForce docs could be synced to the file system, the destination folder could be the library itself, removing a lot of moving parts. I hope that helps.
Martina
Hi!
I’m a newbie in using SharePoint Lists Source and Destination in SSIS, so I hope you can help me with this error:
Error at Data Flow Task [SharePoint List Source [1]]:
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unhandled SharePoint Exception —> System.ServiceModel.FaultException: Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.
I’ve read the same error somewhere, and it says it is a permissions error. I’ve checked that I have ‘Full control, Limited Access’ permission in the target SharePoint List.
I’m wondering if that error really is a permission error and do I have to have a ‘Full Control’ permission, instead of ‘Full Control, Limited Access.’ The error message was really vague. Hope you can help ASAP.
Thanks 🙂
Turns out, it was a SiteName mistake. The list name in the web portal is different from the actual name of the list. Thanks 🙂
I am using SQL 2012, with the 2012 adpator (installed on VM that i am building the package on where the server also resides). I’ve built my package in Visual Studio 2010, when i execute the package in studio, everything works fine. When I try and schedule the dtsx package, i get the following error below:
“This occurs when CPackage::LoadFromXML fails”.
Any thoughts around this? I’ve tried multiple things:
create a proxy to make sure the sql agent can pull the data out of sharepoint (has permissions)
force it to run in 32 bit (someone in browsing google suggested this)
formatting of dates; i excluded dates to test
Ultimately, the above error is what I keep getting. Thoughts? It’s driving me nuts, since I have a lot of lists that I need to automate and I am hoping i don’t have to kick the jobs off manually everytime.
I am using sharepoint 2003.
Hi Steven,
I don’t know this error in particular but searched a bit. Sounds like it might be a version difference between your development environment and your deployment environment. Is that possible? Can you log in to the SQL Server itself and successfully run the package in Visual Studio from there? You might take a look at this thread for any clues – http://www.sqlservercentral.com/Forums/Topic737169-148-1.aspx. Sorry I have not seen this error in particular. I always develop the package on the same server where it will be executed, in order to catch any issues up front.
Cheers,
Martina
I found the issue – SSIS was not installed on the VM I was using, which has since resolved my problems. Great article, life saver!
Great post! Thanks for the detailed description, appreciate it.
I have 10 million records, and want to load that from Oracle to Sharepoint 2013 list.
Will this component work for this scenario or there is some limitation on number of records?
You don’t want to load more than 5,000 records into a SharePoint list. It won’t perform. Why do you want to expose that many records in that way? Perhaps there is a better solution.
Hi Martina,
I am a little new to SSIS, but have been working with Sharepoint for some time now. I have followed your instructions and downloaded the lastest version of the SharePoint adapter to use with Visual Studio 2012. After installing the adapter, I restarted Visual Studios and tried to locate the controller in the Tool box, even under the Data Flow tab, but I couldn’t find it. Is there anything else that I need to do after installing and before trying to locate the tool in SSIS? Is there a specific location I need to consider when installing this adapter, Server, locate PC? I am also using SP Foundation, is it compatible?
Thanks for your time.
Hi, Did you see the update for 2012 at the end of the post? Have you tried right clicking inside the Toolbox pane to refresh? The SharePoint List adapters should appear in the Common folder.
SharePoint Listi destination works correctly if imposed a fixed url site,. how can I parameterize url to migrate the package in ambient operating?
I am hoping someone can help with a problem we are having.
We have installed the Sharepoint List Adapters, and the ‘SharePointListAdapters.dll’ file appears in the correct folder, however when we go to ‘Choose Toolbox Items’, they are not in the list of SSIS Data Flow Items.
Anyone else had this problem?
Hi Martina – I am using multiple sharepoint lists to enter data into SQL. I have deployed packages to MSDB server and running the packages from that server only. I have designed the package to run parallel. Many times it gives me time out error. There was a time it was not running at all then i made it sequential and now it is running fine but still some times it gives me timeout error. Can you please help on this?
Thanks, Mahesh.
Hi Mahesh, The SharePoint list connection can timeout more often than other connections. It really is a function of the connectivity from the SSIS server to the SharePoint list. You’ll have to look at the time of day, how busy the server is, server resources, etc.
Using SSIS 2008R2 or 2012, I have to connect to SharePoint 2007 and copy any documents in 2 specific folders that have a modified date of today, and place the copies in a shared folder on the network. Will the SharePoint List Source help me with that task. I am not for sure the difference between SharePoint list and documents in a folder in SharePoint. I have no experience with SharePoint.
No, the list adapters will not help you move documents in a library to the file system. This post should help you out. http://thedataqueenblog.azurewebsites.net/2013/02/moving-sharepoint-documents-to-the-file-system/
Hi Martina, can this work with SharePoint 2013 site’s lists as well?
Hi,
I am retrieving data from a list having few lookups and I am getting below mentioned error.
I tried to create a view on the list with deselecting unwanted lookup columns but still same issue . Any suggestions as I have reduced the batch size and used the CAML query as well
Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size) —> System.ServiceModel.FaultException: Exception of type ‘Microsoft.SharePoint.SoapServer.SoapServerException’ was thrown.
Any suggestions
I haven’t encountered this error. I suggest you make a test list with only a few items in it and see if you can get that working.
What if Office 365 Sharepoint Online?
You’ll need to use the OData source connector with SharePoint Online
Has anyone managed to get this working with Visual Studio 2013? The sharepoint data source doesn’t appear under common.
I’ve got it working in VS 2012 though.
Hi can you kindly guide me how ? i am facing issues in visual studio 2012
Hi,
While loading SPS 2013 list to a SQL table, i am getting the below error. Any idea, what it could be? – Thank you!
Error 1 Microsoft.SqlServer.Dts.Runtime.DtsRuntimeException: The package failed to load due to error 0xC001001C “The object “%1” references ID “%2”, but no object in the package has this ID. If the object “%1” was added to the package by a manual edit ensure that the referenced object with ID “%2″ is also added to the package.”. This occurs when CPackage::LoadFromXML fails. —> System.Runtime.InteropServices.COMException: The package failed to load due to error 0xC001001C “The object “%1” references ID “%2”, but no object in the package has this ID. If the object “%1” was added to the package by a manual edit ensure that the referenced object with ID “%2″ is also added to the package.”. This occurs when CPackage::LoadFromXML fails. at Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSPackagePersist100.LoadPackageFromXML(Object vSource, Boolean vbSourceIsLocation, IDTSEvents100 pEvents) at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) — End of inner exception stack trace — at Microsoft.SqlServer.Dts.Runtime.Package.LoadFromXML(String packageXml, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.Project.LoadPackage(IProjectStorage storage, Package package, String streamName, IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.Load(IDTSEvents events) at Microsoft.SqlServer.Dts.Runtime.PackageItem.get_Package() at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.IncrementalBuildThroughObj(IOutputWindow outputWindow) at Microsoft.DataTransformationServices.Project.DataTransformationsProjectBuilder.BuildIncremental(IOutputWindow outputWindow) 0 0
All, I have installed SQL Server Data Tools 2015 and then the SharePoint adapters. When I open Visual Studio Administrator, it is not displaying the SSIS Data Flow Items tab, and I can’t find the SharePoint adapters in any of the other tabs, nor are they visible in the Toolbox when I refresh.
My set up is Visual Studio 2015 Shell (Integrated) version 14.0.23107.0 D14REL
SQL Server Data Tools 14.0.61707.300
I am very frustrated with loading the adapters and not having them then available in SSIS
Any help is appreciated.
I am facing an issue with Extracting sharepoint list data through SSIS. i have Sharepoint List Adapter in place where i have given the Configurations as follows in the List Adapter. Image URL: ListAdapter Configurations After this i entered the Site List Name: Technow Reports – Daily Left the Sitelistview as blank. Gave the SiteURl: https://znet.techbigies.com/sites/6160987/GC/ Use Connection Manager is False The CAML Query is as follows:
Rolling Refresh Reports/Refresh Summary Report – Daily
2017-12-04
IT has more than 50 K records in that list. when ever i try to get the files it throws Following error Error: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size). Can you please help with this. i tried lot of combinations of CAML Query but still failing.
It has Site URL as https://znet.techbigies.com/sites/6160987/GC/ ListName as : Technow Reports – Daily 2 – Subfolders: Rolling Refresh Reports/Refresh Summary Report – Daily
When try this same code for other sites it is working fine but files are more it has this issue. And also the SiteListname Technow Reports – Daily is generated in URL of file as Technow Reports Daily i.e. it has two spaces before the daily.
I am facing an issue with Extracting sharepoint list data through SSIS. i have Sharepoint List Adapter in place where i have given the Configurations as follows in the List Adapter. Image URL: https://i.stack.imgur.com/HZCgs.png.
After this i entered the Site List Name: Technow Reports – Daily
Sitelistview as blank.
Gave the SiteURl: https://znet.techbigies.com/sites/6160987/GC/
Use Connection Manager is False
The CAML Query is as follows:
Rolling Refresh Reports/Refresh Summary Report – Daily
2017-12-04
IT has more than 50 K records in that list. when ever i try to get the files it throws Following error Error: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unspecified SharePoint Error. A possible reason might be you are trying to retrieve too many items at a time (Batch size). Can you please help with this. i tried lot of combinations of CAML Query but still failing.
It has Site URL as https://znet.techbigies.com/sites/6160987/GC/
ListName as : Technow Reports – Daily
Subfolders – 2: Rolling Refresh Reports/Refresh Summary Report – Daily
When try this same code for other sites it is working fine but files are more it has this issue. And also for the SiteListname: Technow Reports – Daily is generated in URL of file as Technow Reports Daily i.e. it has two spaces before the daily.
Hello,
I’m trying to connecting to Sharepoint 2010 from SSDT for VS2015 but cannot see the sharepoint list adapters. I did install the SharepointListAdapter from CodePlex. Any ideas???
We have 1200 sites that have a similar list “Comment Log.” I would like to use SSIS to retrieve all the comments. Is there a way to accomplish this? I have been able to do it from a single list, but how would I do it for all the sites in the site collection?
Thank you for the article. Any help would be much appreciated.
Please correct if i am wrong. I have been using this since 2015. It has been working great in production environment with SP 2013, SQL 2012, and IDS 2010. I have installed this adapters in app server as well as in the DB server (i am not sure why i installed in app server). Now, i am adding new app server and i am not sure if i have to install the adapter in the new app server as well. I cannot also find the version 1.0.0.0 adapter installer, how do we check the version of the installer, or how do we know what the installer supports? Please help!