Using Access and SSIS to get data out of Sharepoint

  • Thread starter Thread starter rkutz
  • Start date Start date
R

rkutz

I'm having a problem getting data out of Sharepoint into a SQL Server
database using SSIS. Let me explain what I've done and the problem that I'm
encountering.

There is a sharepoint list on the server with data that I need to extract on
a nightly basis and update tables in SQL Server 2005. To do so, I've created
an access database with linked tables and am querying the linked tables in
the access database which passes the request on to sharepoint and returns the
data. So far so good, it's a bit slow but it works, at least in the BI
design studio. The problem comes when I'm trying to run the package on a
server. I have some limitations on my server access and am running the
package using dtexec under an alternate user name using a 3rd party job
scheduler. When the pakage runs, it just hangs and never completes. From
what I can tell by creating logs on the server, the last status in the log is:

ExternalRequest_pre: The object is ready to make the following external
request: 'ICommandPrepare::Prepare'


I had a user who does have direct access to the server run the package
manually, and it prompted him to authenticate to the sharepoint site even
though I've granted permissions to his account and the account that runs the
package through the job scheduler, so I believe the problem through the job
scheduler is that the package hanging because it needs to authenticate to the
sharepoint site.

So, does this sound like it may be the problem, and if so, how do I get
around it so that the package will automatically authenticate to the
sharepoint site without needing to prompt a user to enter credentials?
 
rkutz said:
I'm having a problem getting data out of Sharepoint into a SQL Server
database using SSIS. Let me explain what I've done and the problem that I'm
encountering.

There is a sharepoint list on the server with data that I need to extract on
a nightly basis and update tables in SQL Server 2005. To do so, I've created
an access database with linked tables and am querying the linked tables in
the access database which passes the request on to sharepoint and returns the
data. So far so good, it's a bit slow but it works, at least in the BI
design studio. The problem comes when I'm trying to run the package on a
server. I have some limitations on my server access and am running the
package using dtexec under an alternate user name using a 3rd party job
scheduler. When the pakage runs, it just hangs and never completes. From
what I can tell by creating logs on the server, the last status in the log is:

ExternalRequest_pre: The object is ready to make the following external
request: 'ICommandPrepare::Prepare'


I had a user who does have direct access to the server run the package
manually, and it prompted him to authenticate to the sharepoint site even
though I've granted permissions to his account and the account that runs the
package through the job scheduler, so I believe the problem through the job
scheduler is that the package hanging because it needs to authenticate to the
sharepoint site.

So, does this sound like it may be the problem, and if so, how do I get
around it so that the package will automatically authenticate to the
sharepoint site without needing to prompt a user to enter credentials?
Click to expand...

Not a direct answer but I have to ask how does Access fit in the
equation, and if you're using Access, why not just have Access do the
hetergeneous operation anyway? Not saying you shouldn't use SSIS, but
I'm probably missing how Access fits in this picture.

If this is a SSIS issue, may I suggest going to the proper forum (e.g.
microsoft.public.sql-server.dts seems closest)?


To the question at the hand, yes, when you run a SSIS package, it's
handled by an agent. You need to impersonate the user, if I understand
the parlance. (Disclaimer: I've never actually done this, so take this
with a grain of salt)

Try googling "SSIS Impersonate User" and see what you get.
 
Back
Top