Using expressions in sharepoint list and destination

Sep 1, 2010 at 6:53 PM


I am trying to pass a variable to sharepoint URL and sharepoint list name in SharePoint List task, so dynamically I can look at mutiple URL's and load data to sql database. I am configuring this in dataflow task, but I don't think URL is changing dynamically in SharePoint List item. Please advise how I should implement expressions in SharePoint List and Destination tasks.




Dec 3, 2010 at 11:55 AM

I would also like this feature, or a work-around to solve this.

Jan 24, 2011 at 6:04 PM
Edited Jan 24, 2011 at 6:56 PM

I had a need to limit the data returned to items that have a custom attribute (date) within a a range of dates:

            <FieldRef Name='Date_x0020_Picked' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>User:BeginningDate</Value>
            <FieldRef Name='Date_x0020_Picked' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>User:EndingDate</Value>
      <FieldRef Name='Store_x0020_Number' Ascending='False' />

Upon further digging into the documentation for this component I found the following:

Setting Properties by Using Integration Services Expressions

You can set many of the custom properties of the SharePoint List source and destination by using Integration Services expressions. Keep in mind that, in the data flow, you have to create these expressions on the containing Data Flow task, and not on the individual data flow component.

 Figure 14. Using expressions to set the properties of the SharePoint List source and destination.

You can use expressions to set the following properties of the SharePoint List source:

  • SiteUrl
  • SiteListName
  • SiteListViewName
  • CamlQuery

You can use expressions to set the following properties of the SharePoint List destination:

  • SiteUrl
  • SiteListName
  • SiteListViewName

There are several ways to set an expression that offer considerable flexibility to the package developer:

  • You can use a package variable to set an expression. The expression can take its value from a package variable, which might also contain an expression. This expression can construct CAML dynamically, or contain the results of a query sent to the SharePoint site by an Execute SQL task.
  • You can use a package configuration to set an expression. Configurations simplify deployment by extracting values that you may want to change from the package into a separate file or storage location.


Feb 22, 2011 at 4:10 AM

Bingo, you found it :) Nice digging.

Feb 24, 2011 at 5:07 PM

I see no expression property? I'm using 463kb SQL 2005 version? I'm I doing something wrong or is this only in the new beta?

Thanks Much


Feb 24, 2011 at 5:11 PM

Sorry I'm stupid! And did not read!

Mar 2, 2012 at 5:28 PM

I have created an expressions on the containing Data Flow task of a Sharepoint Destination. For SiteListName. I am trying to use the variable to pass multiple lists to be updated. Its not working though. Is this not possible. The package is breaking at the Sharepoint Destination Component. Is this a known issue?

Apr 17, 2012 at 6:29 PM
Edited Apr 17, 2012 at 6:30 PM

Has anyone resolved this? I am trying to take some from SQL server and create a new list weekly. I can create the new list in a script task no problem and set the an output variable to my new list name. However when trying to then add data to this newly created list I cannot. I get the error "SiteListName was not set" unless I explicitly hard code a list name that exists in the site collection. The above statement says you can. But how?

Apr 18, 2012 at 2:18 AM

JShaw, to: "Has anyone resolved this". 

The item above was not an issue, someone found the doc and the expression needed.  If you set the expression, it will use that list. It is loaded dynamically, so as long as your list defintiion is the same, it will work as expected.