transfering data from sharepoint to SQL

Question

transfering data from sharepoint to SQL

nazaninahmady_sh

Default port

Points: 1465
More actions
November 19, 2008 at 12:20 am

#127733

how i can use SSIS for Transfering data from sharepoint(excell) to SQL Server 2005?
thanks

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

COZYROC One Orange Chip Points: 28499 More actions · Answer 1

Check SharePoint List Source and Destination adapters. They are for SQL 2008, but you can easily port them back to SQL 2005. You have the source code included.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

COZYROC One Orange Chip Points: 28499 More actions · Answer 2

I just realized you need Excel file processing. Yes you can still use SSIS, but you have to first download the Excel file locally from SPS and then process using the standard Excel file and source component. Check this script sample, how to download using HTTP client.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

nazaninahmady_sh Default port Points: 1465 More actions · Answer 3

thank you very much for your kind,

i will try to write my question defenitly,

i have a site with sharepoint(2003), the users enter data to excel form,as documents library, in it, i want to have these data in a database wich is created withSQL server 2005 for reporting and ....., i dont know is there any solution for transfering , and if yes, what is that?

i have heard that there ia a one big table for every row of every list in whole of sharepoint, is it true? where i it?

thank you

COZYROC One Orange Chip Points: 28499 More actions · Answer 4

I don't know too much about SPS. It is probably a good idea to ask that kind of question in SharePoint-related website. This forum is concentrated on SSIS.

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

notes4we SSCommitted Points: 1565 More actions · Answer 5

I have not worked with Sharepoint 2003, but I have extensively worked with Sharepoint 2007.

What I did is I used OLE DB source with some properties change in it which made it work with Sharepoint 2007 fine.

- In the connection managers area, right click and select New OLE DB connection.

- Click on New and in the Provider, Select Native OLE DB\Microsoft Office 12.0 Access Database Engine OLE DB Provider.

- On the left, click on All and in the Advanced column in the Extended Properties field, type Excel 12.0

- Named ConnectionString column, where there is File Name field, locate the Excel File that we wish to use. On the right most side, there is an ellipsis icon, that will allow us to select the file.

- Simply right click and cut that file path and paste it under the Source column, in the Data Source field.

- On the left most bottom corner, click on Test Connection. If everything is good, then the Test Connection succeeded message will prompt.

- Go back to the OLE DB source and select the connection that you wish to use.

- This way you can go ahead and use Sharepoint 2007 for building the package.

You can try this and see if it works for you too.

Good Luck.