Import csv data from Sharepoint using SQL Job
I need to import csv data from a secured Sharepoint into a SQL table. Running the package in BIDS imports the data successfully... using the path \\\DavWWWRoot\sites\FileName.csv, (built using an expression). Using a proxy account (as myself) running in it as a job step in the SSIS package store, it gives the following error: Description: Connection "TLDFlatFile" failed validation. Code: 0xC001401E The file name property is not valid. The file name is a device or contains invalid characters. I am able to copy/paste the file from the sharepoint at the command line..but doing so in a job step using an OS command gives an "Access is denied" error. Is it possible to import the csv file directly from the secure Sharepoint using SSIS, or to copy/paste it to a location on the destination server to be able to import with SSIS? OS:WinSvr 2008 Enterprise SQL: version 10.50.1600 Visual Studio 2008 Sharepoint 2010 Thank you, Elaine Shafer
October 28th, 2010 3:04pm

Looks like running your package under SQL Server Agent which runs under a different security context is your issue. I would say FTP the file in using a script task or a batch, then process with SSIS.Arthur My Blog
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 4:16pm

Hi Arthur, In the job step, I'm using a proxy account with credentials that has permissions to the Sharepoint location. Thanks! Elaine
October 28th, 2010 4:40pm

And to confirm - you are using the proxy account on a job that uses the CmdExec step type to run DTEXEC? That should be working. Using the SSIS step type won't, in many circumstances.John Welch | | |
Free Windows Admin Tool Kit Click here and download it now
October 28th, 2010 5:48pm

Hopefully not a double post... Thanks John, that does work! It works using myself as a proxy account, but not when I use our non-user domain account, so hopefully it is now a permissions issue.
October 28th, 2010 7:13pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics