In many instances there is a development team or even a warehouse team that needs to run SSIS packages and you want to store those packages in SQL Server and run them through the SQL Agent Service. You may not; however, want to grant those users/group sysadmin rights to the machine. The following describes my experience in setting this up.
First, you must add the user/group to the DCOM Configuration for “Microsoft SQL Server Integration Services 11.0″ (SQL 2012), which can be found in the Component Services MMC. Right click and choose properties.
You can now add the groups to two security sections–“Launch and Activation Permissions” and “Access Permissions”. It was my experience I had to have them in both groups. I granted full rights in each group.
The final component is to add each user/group to the local group “Distributed COM Users”. It seems like it won’t even authenticate a user for the groups you set earlier unless they are in the local group. This will allow users to connect the Integration Services Instance.
At this point you should restart the Integration Service services.
If you haven’t already, you can go ahead and create your Catalog Database. We won’t go over those steps here–perhaps in another post.
The last step is to allow them to deploy and manage the packages on the servers. I add the group to the SSISDB and grant the role of ssis_admin. Now your teams should be able to function completely within the SSIS parameters and not have permissions to drop database objects or modify security settings.
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.