Importing or Exporting to Access

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.AccessEngineProvider

Upgrading SharePoint List Adapter in SSIS

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.

ListAdapter 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,SharePointListAdapter 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 NewSharePointConnectionable to choose SPCRED and enter the appropriate information.  My package was converted to the new server and worked well.