However, one thing seems missing from the out of the box SSIS experience; That is the ability to use SharePoint Lists as data flow sources and destinations.
Some might find this puzzling, given the great integration story found built into the Office suite of products - allowing Excel and Access to connect and retrieve data contained in SharePoint lists.
Fortunately, there's a great community that surrounds SharePoint and provides solutions for common scenarios such as this one.
The SharePoint List Source and Destination sample available on the Microsoft SQL Server Community Samples: Integration Services page on Codeplex provides a solution for SSIS Package creators to use SharePoint as a source as well as a destination in their SSIS data flows.
In use, the sample provides SharePoint Data Flow Source and Destination components which allow the user to the SharePoint site URL and list name to use. Once configured, these Data Flow components can be used in conjunction with any other SSIS Data Flow Components.
You can find these components, available for both SQL Server 2005 and SQL Server 2008, as well as sample instructions at the following locations:
SharePoint List Source and Destination sample
MSDN Article
6 comments:
An update has been released to the List Source and Destination Sample:
1) Support wide lists with lots of data in a column.
2) Support multi-row text columns up to 2 bil chars
3) Added Culture to allow proper formatting of numbers/dates on external systems in different languages
4) Changed validation so that if a column is ADDED to sharepoint, it will not cause a validation error, only if a needed column is removed (helps with lists where people are adjusting them after you sync a process to them)
5) Fixed bug in datatyping where the data was being written using the wrong datatype (R8 Issue)
Also - The SharePointUtility.DLL has been added as a separate download to facilitate using it to access SharePoint Lists API without requiring the entire MSI.
Great adapter!
There is only one question... When I import from SQL datetime column into WSS list (Date and Time) column always receive correct date but "12:00 AM" time in a WSS. Can you advise please?
Thanks,
Vlad
Great adapter!
There is only one question... When I import from SQL datetime column into WSS list (Date and Time) column always receive correct date but "12:00 AM" time in a WSS. Can you advise please?
Thanks,
Vlad
I am having same issue with Date and Time. I am inserting new items into a list, and have a column defined in the list as a Date AND Time column, but when I use the Sharepoint destination in SSIS, and copy from a database_timestamp source column, the Time gets set to 12:00.
Any ideas on how to populate the time portion correctly?
Exactly what I need! However, I'm using VS2005 and it doesn't show on my SSIS Control Flow Items list as an option. Does it not work with VS2005?
Alex,
As shown in the post: "The SharePoint List Source and Destination sample available on the Microsoft SQL Server Community Samples: Integration Services page on Codeplex provides a solution for SSIS Package creators to use SharePoint as a source as well as a destination in their SSIS data flows"
Post a Comment