SQL Agent SPCRED Issue

Mar 29, 2011 at 9:35 PM

I have a somewhat frustrating scenario with deploying a package I created that uses a SharePoint List Source. The package executes fine from the Execute Package Utility on the Integration Services server and on my local machine. But for some reason it throws an error when I try to execute the package using a SQL Agent job. I've configured the SPCRED connection to use the credentials of the executing process and I've run the SQL Agent Job using the SQL Server Agent Account and a proxy account that uses my credentials - both of those accounts have been granted access to the SharePoint site the package accesses. Here is the error I receive:

Date        3/29/2011 2:33:22 PM
Log        Job History (Sharepoint Test Import)

Step ID        1
Server        DEV-DW02
Job Name        Sharepoint Test Import
Step Name        Run package
Duration        00:00:01
Sql Severity        0
Sql Message ID        0
Operator Emailed       
Operator Net sent       
Operator Paged       
Retries Attempted        0

Message
Executed as user: GHS\DEV-DW02-SQLSA. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.1600.1 for 64-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  2:33:22 PM  Error: 2011-03-29 14:33:23.17     Code: 0xC0014005     Source:       Description: The connection type "SPCRED" specified for connection manager "SharePoint" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.  End Error  Error: 2011-03-29 14:33:23.17     Code: 0xC0010018     Source:       Description: Error loading value "<DTS:ConnectionManager xmlns:DTS="www.microsoft.com/SqlServer/Dts">  <DTS:Property DTS:Name="DelayValidation">0</DTS:Property>  <DTS:Property DTS:Name="ObjectName">SharePoint</DTS:Property>  <DTS:Property DTS:Name="DTSID">{4F0C5230-29BA-48E2-9DA9-84710C893" from node "DTS:ConnectionManager".  End Error  Could not load package "\BI_DW\Stage\Sharepoint_Print_Production_Cost_List" because of error 0xC0010014.  Description: 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.  Source:   Started:  2:33:22 PM  Finished: 2:33:23 PM  Elapsed:  0.484 seconds.  The package could not be loaded.  The step failed.

 

Any help is much appreciated!

Apr 25, 2011 at 2:48 PM

I am having this same exacty issue.  Funny thing is, when I ran into this problem on a separate server, all I had to do was restart the SSIS service and that fixed the issue.  On the server I'm working with now, the problem persists.  Please help!

Aug 3, 2011 at 7:52 AM

I was on an old server testing SQL Server 2005 with the adapters and actually ran into the same thing today!  I restarte the SSIS service (per posts about related items like this) and it did clear it up for me as well.

Oct 5, 2011 at 11:46 PM

I am having the same issue, Windows server 2003 R2, SQL 2005 - any new ideas?

 

Thanks

 

Bob

Oct 6, 2011 at 3:38 PM

I gave up trying to make it work with the SSIS plugin – I created a workaround where I created an SSRS report to access the Sharepoint list directly (I don’t think that functionality was available prior to SQL 2008 R2 unfortunately) and then setup a subscription to dump it to a flat file to be consumed by the SSIS data flow. Sorry I can’t be of more help.

Nathan Suiter | Business Intelligence Analyst/Developer | Onlife Health Inc.

9020 Overlook Blvd., Suite 300, Brentwood, TN 37027

615.620.2277 | nsuiter@onlifehealth.com

From: rpress [email removed]
Sent: Wednesday, October 05, 2011 5:47 PM
To: Nathan Suiter
Subject: Re: SQL Agent SPCRED Issue [SQLSrvIntegrationSrv:251698]

From: rpress

I am having the same issue, Windows server 2003 R2, SQL 2005 - any new ideas?

Thanks

Bob

Oct 6, 2011 at 4:27 PM

Has this issue happened to anyone using SQL 2008? All of the reports I see are with SQL 2005.

Oct 6, 2011 at 5:35 PM
Edited Oct 6, 2011 at 5:42 PM

I've created an issue for this and will work on a beta release that responds to this. I could use some help in validating the beta for rollout to all.

I've added a beta version. Do to the way SSIS validates the connection manager from the properties, and that this is more of a one-off solution for those that are experiencing issues, the instructions are not quite as straightforward as I'd like:

To turn connection manager support off:

- In each Source/Destination component, in properties pane from designer (NOT popup), set "UseConnectionManager' to false. 

- Remove all Sharepoint Connection managers from project

To turn the connection manager support back on:

- In each Source/Destination component, in properties pane from designer (NOT popup), set "UseConnectionManager' to true.

- Close and Reopen the package to see the connection manager tab.

 ++ In addition, i took care of a annoyance I added by using the friendly names -- duplicates. Now duplicate friendly names will no longer be true duplicates, that is included in this release as well. 

Oct 17, 2011 at 2:29 PM
kevinidzi wrote:

Has this issue happened to anyone using SQL 2008? All of the reports I see are with SQL 2005.


Had this issue today with SQL 2008. It was because the SharePoint List Adapter was not installed on the server where i had deployed the package.

After installing it the issue was resolved. 

Jun 10, 2013 at 9:04 PM
I'm having this issue with 2008 R2. I tried removing the connection manager, but that throws another error. "The exception was: Could not load file or assembly 'SharePointListAdapters, Version=1.0.0.0, Culture=neutral, PublicKeyToken=f4b3011e1ece9d47' or one of its dependencies. The system cannot find the file specified."

I tried reinstalling the adapters, restarted all the services, etc.. Any ideas?