none
Read Sharepoint List Adapter scheduled through Agent Job

    Question

  • Hi,

    There is a post, exact question like I have, but the solutions did not fix my issue. please advise. thanks in advance.

    ----------------------------------------------

    post

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/af25182d-776a-4c7e-bc4c-2313672feb24/sharepoint-list-adapter-scheduled-through-agent-job?forum=sqlintegrationservices

    ----------------------------------------------

    I created a package that reads from a Sharepoint List using the apapters on Codeplex. IfI run this package directly, it ran fine without problem. When I try to run the package through SQLAgent I get the following error:

     Source:      

    Description: The connection type "SPCRED" specified for connection manager "SharePoint Credential" 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

    Has anyone else worked through and resolved this error?


    GWANG

    Sunday, October 06, 2013 4:47 AM

Answers

  • Thank you.  The error message was very helpful.  For one thing it is different from the one you had above, which suggests that the component is installed, but having an issue.

    You are going to make sure that the account that is running the SSIS package SQL Agent Job Step has permission to access your SharePoint server.  You need to create a SQL Server Agent Proxy that uses an account that does have access on your SharePoint server. http://support.microsoft.com/kb/918760


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 10:26 PM
  • I assume that the account that is running the package (by default it is the SQL Server Agent Service Account) must have read permission on that list.  I have not used that component.

    However, you can create a proxy account that can run the SSIS package step. http://support.microsoft.com/kb/918760 You could create an Active Directory account that can run your package and can access the SharePoint list.  Then you create a Credential in the SQL Server master database (this is outlined in the article as well as the read me posts associated with this list).  The Credential object stores the user name from AD and the password for that account.  Next you create a SQL Agent proxy for that Credential.  And then you edit the SSIS job step and change the RunAs setting to use the proxy you just created.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, October 07, 2013 10:08 PM

All replies

  • Are you running SSDT or BIDS on the server that you are running the SQL Agent job from?  So, remote into the same Server that is hosting the SQL Server agent instance that is running the your package and failing.  Can you connect to the SharePoint list connection?  If not, then most likely you need to install the SharePoint components on that server.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 11:36 AM
  • thanks Russ,

    Say I remote to the server, double click on the SSIS package "abc.dtsx" (read data from SharePopint List)

    It worked fine.

    But If I run it via SQL agent job, then getting error. I was googling aorund, no tmuch info. thanks!


    GWANG

    Sunday, October 06, 2013 8:02 PM
  • Here is a computer basic. You are sitting at your computer and you browse using explorer to find the Package.dtsx on another computer, say the computer on which you have installed SQL Server and on which you are eventually going to run your package from SQL Server agent.  The computer that runs that package is your computer not the SQL Server Agent computer.  You have available all of the components that you installed on your computer.

    When you run a SQL Server Agent job on that computer, the computer on which the Package.dtsx file is located, the computer that runs is the computer on which you installed the SQL Server Agent job.  The components that are available are the ones that you installed on the SQL Server Agent computer.  If you haven't installed the SharePoint list component on the SQL Server Agent computer, then SQL Server Agent will give you exactly the error above.

    If you don't understand this, my suggestion is that you find a way to export the SharePoint list to a csv file and import the list using the csv file.  You can't use components unless you understand this basic principle.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 8:33 PM
  • By the way, when you say, you remote into the server, do you mean that you use remote desktop to remote into the machine?  If so, then I take back my point.  Then you have a different problem.  And I'm afraid that I don't know what would cause the issue.  You might try setting the Use 32 bit flag. http://sqlblog.com/blogs/john_paul_cook/archive/2010/03/24/running-32-bit-ssis-in-a-64-bit-environment.aspx 

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 8:36 PM
  • Thanks Russ,  Yes I used remote desktop to remote into server. If I double clicked running this abc.dtsx, it ran fine, but throwing errors as below if I also remote in used SQL agent job.

    --------------

    Source: Data Flow Task SharePoint List Source [1]     Description: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unhandled SharePoint Exception ---> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.

    thanks again for your input.


    GWANG

    Sunday, October 06, 2013 10:03 PM
  • Thank you.  The error message was very helpful.  For one thing it is different from the one you had above, which suggests that the component is installed, but having an issue.

    You are going to make sure that the account that is running the SSIS package SQL Agent Job Step has permission to access your SharePoint server.  You need to create a SQL Server Agent Proxy that uses an account that does have access on your SharePoint server. http://support.microsoft.com/kb/918760


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Sunday, October 06, 2013 10:26 PM
  • Thanks for your input Russ,  I need to talk to our security group.

    So even I have the account setup in connection manager to read SharePoint list on SharePoint server,

    I need to make sure that the account that is running the SSIS package SQL Agent Job Step has permission to access your SharePoint server, correct?


    GWANG

    Monday, October 07, 2013 12:23 AM
  • Correct.  What you would normally do is create a proxy account http://support.microsoft.com/kb/918760 that has perhaps only enough permission to read that SharePoint list.  Note this involves creating a Credential, storing the user name and password for the user that will be running your SSIS Agent Job.  Then you create a Proxy in SQL Agent that uses that Credential.

    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, October 07, 2013 12:55 AM
  • Hi Russ,   sorry to bother.

    "to make sure that the account that is running the SSIS package SQL Agent Job Step has permission to access your SharePoint server"

    does it mean that we just grant the SSIS agent service account has [read] rights to that SharePoint list on SharePint Server?   Or need more rights?

    thanks again.

    below are errors in log

    --------------------------------------------------------------------

    Code: 0xC0047062    

    Source: Data Flow Task SharePoint List Source [1]    

    Description: Microsoft.Samples.SqlServer.SSIS.SharePointUtility.SharePointUnhandledException: Unhandled SharePoint Exception ---> System.ServiceModel.FaultException: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.    Server stack trace:      at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoap.GetListAndView(GetListAndViewRequest request)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.ListsService_ListsSoap_GetListAndView(GetListAndViewRequest request)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListsService.ListsSoapClient.GetListAndView(String listName, String viewName)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointList(String listName, String viewId)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointFields(String listName, String viewId)     --- End of inner exception stack trace ---     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.Adapter.ListsAdapter.GetSharePointFields(String listName, String viewId)     at Microsoft.Samples.SqlServer.SSIS.SharePointUtility.ListServiceUtility.GetFields(Uri sharepointUri, NetworkCredential credentials, String listName, String viewName)     at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.GetAccessibleSharePointColumns(String sharepointUrl, String listName, String viewName)     at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.ValidateSharePointColumns()     at Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListSource.Validate()     at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostValidate(IDTSManagedComponentWrapper100 wrapper)  End Error 

    Error: 2013-10-07 17:45:46.09     Co

    de: 0xC0047017    

    Source: Data Flow Task SSIS.Pipeline    

    Description: component "SharePoint List Source" (1) failed validation and returned error code 0x80131500.  End Error 

    Error: 2013-10-07 17:45:46.09    

    Code: 0xC004700C    

    Source: Data Flow Task SSIS.Pipeline    

    Description: One or more component failed validation. 

    End Error 

    Error: 2013-10-07 17:45:46.09    

    Code: 0xC0024107    

    Source: Data Flow Task     

    Description: There were errors during task validation.  End Error 

    DTExec: The package execution returned DTSER_FAILURE (1).  Started:  5:45:35 PM  Finished: 5:45:46 PM  Elapsed:  11.029 seconds.  The package execution failed.  The step failed.


    GWANG

    Monday, October 07, 2013 10:01 PM
  • I assume that the account that is running the package (by default it is the SQL Server Agent Service Account) must have read permission on that list.  I have not used that component.

    However, you can create a proxy account that can run the SSIS package step. http://support.microsoft.com/kb/918760 You could create an Active Directory account that can run your package and can access the SharePoint list.  Then you create a Credential in the SQL Server master database (this is outlined in the article as well as the read me posts associated with this list).  The Credential object stores the user name from AD and the password for that account.  Next you create a SQL Agent proxy for that Credential.  And then you edit the SSIS job step and change the RunAs setting to use the proxy you just created.


    Russel Loski, MCT, MCSA SQL Server 2012, 2008, MCITP Business Intelligence Developer and Database Developer 2008 Twitter: @sqlmovers; blog: www.sqlmovers.com

    Monday, October 07, 2013 10:08 PM
  • We currently have other existing SSIS packages which run fine using Run As "SQL Server Agent Service Account" say the Executed account is "ser-abc", can we just grant this account "ser-abc" to have the read permission on that SharePoint list on SharePoint server?

    (we also have setup another service account which have read permission on that SharePoint list on SharePoint server  in conncection manager im SSIS package)

    thanks!

    Tuesday, October 08, 2013 7:35 AM