I had a client request to export some data to Access 2010 for their downstream use. I have done this in the past and thought it a no-brainer; however, there is always a little trick–hence the post.
Because I had already installed Office 2010, I assumed the Microsoft Access Database Engine for 2010 would get me what I needed. Because I was using a new machine, I had to install the 2007 Office System Driver: Data Connectivity Components. After I did that, I could see the Microsoft Office 12.0 Access Database Engine OLE DB Provider. I put in the location of the access db, and I was off and running.
A server upgrade prompted me to migrate some SSIS packages. These packages were going from SQL 2008R2 to SQL 2008R2, so there was no change there; however, the new server had a different version of the SharePoint List Adapters, which allow you to easily write to and get data from SharePoint Lists. When I opened the package on the new server, the list adapter was not recognized and I got the following error.
“Error 1 Validation error. %Job Name% The component metadata for “component “SharePoint List Destination” (529)” could not be upgraded to the newer version of the component. The PerformUpgrade method failed. %Job Name% 0 0″
The ‘version’ of the adapter was same; however, I was using an updated release as shown here by the release date for the adapter. Upgrading was straightforward, once I found this post on codeplex. My experience was going to the package in Windows Explorer and opening the file in Textpad. Now I guess I should lament here that I didn’t do it in PowerShell, but one thing at a time here! I then replaced the PublicTokenID as mentioned in the post. I saved the text file and had to re-open the SSIS project. There is a newer version of the file itself and one would need to update the Version and the PublicTokenID if upgrading to the latest version.
The last step was to add a SharePoint Credential to the List Adapter as this is a new feature. I right clicked in my connection manager and choose new connection. I was then able to choose SPCRED and enter the appropriate information. My package was converted to the new server and worked well.