.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

SSIS and Sharepoint

Posted By:      Posted Date: October 07, 2010    Points: 0   Category :Sql Server

Here are the requirements from client:

1. When user clicks the document listed on sharepoint, it tiggers SSIS process.

2. SSIS generates the document(excel file) and refresh the sharepoint document.

3. Sharepoint document got open to user.

Can this process happen in reality? What kind of tools I need?



View Complete Post

More Related Resource Links

using SharePoint List for SSIS package

I need to use a sharepoint list as data source for SSIS package, it works fine until the sharepoint site implement Kerberos only (IIS 7) authentication, anyone knows how to get around this? thanks.

Migrate DB + files outside DB into SharePoint using SSIS and scripts


Hello, just wanted to ask a question about SSIS and SharePoint, any input will be greatly appreciated.... did search the forum but could not find anything....

So basically we have a SQL Server DB with the metadata ONLY, WITHOUT the related files(doc/ppt/pdf..etc) which should've been in the DB and they are on a separate web server inside folders. Each row in the DB has a key (column) which matches the related folder's name, so for row with ID #112, there's a folder named ID112 on the web server with the relevant files inside. There could be more than 8+ files for one folder.  

Now, I think it is possible but just wanted to double check, would it be possible to use SSIS and some kind of a script to get the metadata inside the SQL DB AND the related files inside those folders, combine & relate them and upload them into SharePoint as a custom list with file attachments? would they display the attachment normally (like the paperclip icon in a sharepoint list...etc) I wonder if anyone has tried this...

Any guidance, thoughts, suggestions for other solutions, inputs will be great!!! Thank you!!



C# newbie stuck - trying to access column data in a SharePoint list in an SSIS script task

Hello, I'm sure this is the simplest question but I can't figure it out, even with Google's help. I am trying to stumble through some C# code in an SSIS script task and I am frustrated that I can't figure out how to do the easiest things.  I eventually want to find data in a column,and then use another list as a lookup to replace that value with another where the existing value matches a value in the lookup list.  So, the data in my (multiple choice) column might be "apples; bananas" and in another list I have a row that contains two columns, the first holding the value "Apples" and the second containing "Red Delicious" and my original column should read: "Red Delicious; bananas." But, alas, I can't even figure out how to see the data that is in a column. Here is my code: /*<br/> Microsoft SQL Server Integration Services Script Task<br/> Write scripts using Microsoft Visual C# 2008.<br/> The ScriptMain is the entry point class of the script.<br/> */<br/> <br/> using System;<br/> using System.Data;<br/> using Microsoft.SharePoint;<br/> using Microsoft.SqlServer.Dts.Runtime;<br/> using System.Windows.Forms;<br/> using Microsoft.SharePoint.Utilities;<br/> <br/> namespace ST_08becda4c05c49cd9f30ea76110076cd.csproj<br/> {<br/> [

Use SSIS to read files in SharePoint library

Hi,I'm trying to use SSIS to read data from an Excel file in a SharePoint document library and I am having some problems with it.First I tried to use the Excel file as a source file in an Excel Source task which worked well in BIDS but not on the SQL server. ("CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER")Next I tried to read data from an Excel file on the SQL server file system which worked well on the SQL server.Next I tried to use a File System Task to copy the Excel file to the SQL server file system. This also worked well in BIDS but not on the SQL server. ("The file name \\sharepoint\mylibrary\myfile.xls specified in the connection was not valid.")I have also tried to let the SQL server agent run on a domain account that is set up as a reader on the SharePoint document library without success.Where am I going wrong?

SSIS SharePoint List Adapter - how to query Users & Groups?


I am using SSIS SharePoint List Adapter to load data in and out of my SharePoint Lists from within SSIS, and most of it is working fine.  However, I need to perform a lookup against the Users & Groups collection in SharePoint so I can retrieve the UserID and set the value when writing back to SharePoint.  Any help would be tremendously appreciated!


Field shows as when using SSIS and sharepoint adapter


Hello, I am setting up an SSIS usingthe Sharepoint Source adapter. I am replacing and existing process that imports data from Sharepoint into Excel directly, but this is manual.  The sharepoint source adapter seems to truncate column names or provides different column names then what is visible on the user Sharepoin site - they all do not match my manual import into Excel and there are additional fields.  I'd assume that what it is returning is the actual Sharepoint Database column name.  Anyway, I need to map my fields to my existing process in SQL.  The only way I know how to do this and identify what columns are being returned, I am attempting to import the data into Excel using SSIS through the Excel Destination.  I'm having an issue with a few fileds....I have a field/column called QAReviews which is importing into Excel with all the fields for each record showing <Lon Text> instead of the value the Sharepoint site actually contains.  In my manual Excel import process, this field shows the correct data.  By the way, on sharepoint this is a choice field. Can someone tell me why I'm getting something different through SSIS then what I get from using the manual import in Excel.  I also exported the data from Sharepoint to Access and again I get the correct data. Please advise.



Import multiple xml files(sharepoint webservices out put) to sql database using ssis



I am importing xml file in to sql server database i was able to do this task for single xml file in to sql database using ssis( i am using dataflow task, xml source, data conversion, oledb destination creating a ssis package).

Now i am having 3 xml files, need to store in different tables in sql server database can someone explain how i can import multiple xml files in to different tables in sql.can we create a xsd for each xml file while importing.

How to update Sharepoint List associated to a workflow with SSIS ?


Hi all,

I've started a new SSIS project which needs to sync daily a SharePoint list into a SQL 2008 database.

Every day the SP list is updated with new suppliers. It is associated to a validation workflow in order to get a clean list.

My SSIS project is looking good so far, when I run it I'm able to get approved records from the SP list into my SQL table.

But I need to go further. I need to tell to the SP list that the datatset I have at the end of my SSIS flow has been synced.

I need to do that in order to have these records only once otherwise at the next run I'll get duplicates.

So my idea was to go for a boolean field in the SP list and update this field and the end of my data flow.

I'm deriving the colum in order to update that in my flow. I set all the records to IsSync == True

But when I run the flow and I check the SP List...only the version field in the SP list has changed and nothing else!

Any help would be appreciated !



Update a Sharepoint List in SSIS (Web Service)?


Good day,

I'm trying to update a Sharepoint List withh SSIS (Web Service)?
I tried using Web Services with vbscript/javascript but struggled for days.

I'm working on a Remote sever diff to the Sharepoint & SQL server (3 diff serv)

But thought if i could get a package to update the list (Sharepoint document library) and then execute the package using vbscipt/javascript through a command prompt.

Firstly i would like to get the package to work before i try & execute the package remotely.

Where can i also find the ssis sharepoint list adapters installer package to install.

Please Assist!

Changing the graphics on the home page of a SharePoint site

When you create a site in Microsoft Windows SharePoint Services by using the default Team Site template, the home page includes two default graphics. A Windows SharePoint Services graphic appears in the body of the page, and a picture of a house in a circle appears near the site title. You can change these graphics to customize your site. Changing the smaller graphic requires a Windows SharePoint Services compatible Web page editor

Displaying SharePoint lists in Web Parts

When you create a built-in or custom list in Microsoft Windows SharePoint Services, Windows SharePoint Services creates a Web Part, a basic building block of a SharePoint site, to display that list. These Web Parts are called List View Web Parts. Windows SharePoint Services stores List View Web Parts in the site collection Web Part gallery. (The site collection Web Part gallery is the most central gallery of Web Parts for a work group. By default, the gallery name is the name of the site, such as "Our Team Site Web Part Gallery." )

Take a tour of your SharePoint site

What is a SharePoint site for?
A SharePoint site is a Web site that provides a central storage and collaboration space for documents, information, and ideas. A SharePoint site is a tool for collaboration, just like a telephone is a tool for communication, or a meeting is a tool for decision making. A SharePoint site helps groups of people (whether work teams or social groups) share information and work together. For example, a SharePoint site can help you:

Coordinate projects, calendars, and schedules.
Discuss ideas and review documents or proposals.
Share information and keep in touch with other people.

Six easy ways to customize your site using Web Parts in SharePoint

Becoming familiar with Web Part customization techniques gives you a range of page-customization choices. For example, you can customize your site's home page so that it displays the specific information that your team needs. You can rearrange the list items on the home page, add lists, and even change the images on the home page. As you become more familiar with Web Part Page customization techniques, you can build interactive Web Part Pages that makes the data in lists more dynamic and useful, and you can add new Web Parts that provide access to external data and Web services.

Working with SharePoint document libraries

Document libraries are collections of files that you can share with team members on a Web based on Microsoft Windows SharePoint Services. For example, you can create a library of common documents for a project, and team members can use their Web browsers to find the files, read them, and make comments. Users with Microsoft Office 2003 can check out and edit the files as if they resided on a local or network drive.

By default, your team Web site comes with a built-in document library named Shared Documents, which is listed on the Quick Launch bar as well as on the Documents and Lists page.

Out of the Depths of SharePoint's XML Hell

I described how a new open source tool called sporm significantly simplifies unit testing SharePoint. Making SharePoint unit testable is my absolute favorite feature of sporm because SharePoint is notoriously hard to unit test. But sporm provides other benefits as well and its ability to pull us out of the depths of verbose loosely typed XML hell and into LINQ excellence is next on my list of favorite features. So in this post I'll describe the pre-sporm technique of querying with CAML, how to query data using sporm, and finally how sporm supports SharePoint's unique architecture of allowing multiple content types per list and what that means to you.

Unit Testing SharePoint - Past, Present, and Sporm

As I described in SharePoint: The Wild West of Software Development there is a serious problem when you develop for SharePoint: ensuring quality through unit testing is really, really hard. And that's where a new open source tool just released today called sporm (SharePoint Object Relational Mapper) comes in. While sporm provides many benefits besides simplified unit testing I wanted to focus on this topic first, because sporm's approach, which models the entity framework in the way it supports POCO's, is a unique feature not available with other SharePoint tools like LINQ to SharePoint.

SharePoint Tutorial - Web Parts

A page in SharePoint is comprised of web parts the exist within zones. Web parts are available for just about everything. For example in the page below there are four web part highlighted by different colors. The purple web part displays a short list of the new announcements stored in the annoucements library. The pink web part displays events from a calendar within the site. The green web part displays an image from a location the user specifies. The gray web part displays a list of links from the links list on the site.
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend