Help!! Error with sharepoint list source using expressions

Dec 15, 2011 at 6:25 PM

Hi

I am using SSIS in VS 2008. The Sharepoint List Source Component works beautifully.

Now I need to use different url's and I am attempting to assign the SiteURL via an Expression.

Now each time the SiteURL changes (i am using script task for it), i get this error

[SSIS.Pipeline] Error: "component "SharePoint List Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".

Can anyone help me?

Thanks a lot!!

Coordinator
Dec 21, 2011 at 4:44 PM

The site list you're trying to change to, does it have the same columns?  This is a bit more tricky however.... (I ran into the same thing in the past).

When you first create a new shiny sharepoint list, the columns are named similar to their IDs.  Then, over time, column names and things change.  When you export to excel and import to a 'new sharepoint' list, the column names LOOK the same, but their internal IDs will not match the original list, which had the original column names.

The way around this is when you import to create your 'second' copy of a list, also recreate the first with the same file so that both lists will be created the same way, and will have the same column IDs.

Or, if you are only using a small subset of columns, remove the extraneous (high risk of change) columns from the adapter so that it doesn't even try to match them up at all. 

Dec 21, 2011 at 5:30 PM

I have several sites: http:mysite/x/ where I have several lists: list1, list2, list3

x can be 00, 01, 02,... and so on

list1, list2 and list3 are similar, with the same columns for all the sub-sites

I wanted to have an adapter for each list, and the value for the siteurl can change (http:mysite/01/, http:mysite/02/, http:mysite/03/.... http:mysite/100/), so I wanted to use a variable for it...

Coordinator
Dec 22, 2011 at 2:08 PM

I understand your scenario, but the columns need to have the same key for them.  And you need to remove any excessive columns in the External Columns Tree node of the advanced dialog, not just hte output columns.  If the key is not the same though (described previously), the columns will not be 'found' and it will error.

Dec 22, 2011 at 2:25 PM

I already tried it yesterday... I removed all the columns in the external columns and I only left one, the "id" column, but I got the "component "SharePoint List Source" (1)" failed validation and returned validation status "VS_NEEDSNEWMETADATA" error...

:-(

Thanks!

Feb 24, 2012 at 6:53 PM

This is a very frustrating problem while working in Dev environment and attempting to use the solution against prod and your sharepoint list has a large nbr of columns and you have already gone through the pain of removing them from external/output column list for the sharepoint source while designing!!!!!!

Recreating production sharepoint lists are not always feasible.....I ended up doing the same thing all over again!!! I wish there was a more elegant workaround/solution to this!! :-( :-(

Coordinator
Apr 18, 2012 at 2:35 AM

I'm sorry for the frustration Santanu. THe problem from my end is that I can't map to the display Name of the column as it is not unique at all.  The only way to uniquely identify a column is through that goofy internal ID. 

All SSIS knows is the IDs for matching against... I'm surprised removing all of the columns did not work though, that is a scenario i used before and seemed to remember it working.  THat is the working solution - so if it is not working like that, then file an issue for me to address.

Nov 17, 2013 at 5:29 PM
Hi,
Please help me on how to pass the multiple URL's to the Sharepoint list source. I have refered the following link but the steps are not given clearly.

1) I have all the subsite url's in SQL table.
2) I am retrieving it using Execute SQL task and storing the URL in variable.
3) Created data flow task inside the for each loop container.
3) Assigning that variable as expression to Sharepoint List Source.SiteURL not working.
4) I have tested by entering the Site URL in variable value, then it is working fine.
5) but passing dynamic URL to the Sharrepoint list source is not working.
6) FYI... The site list name and metadata is same for all URL.

Please help.....
Jul 1, 2014 at 8:23 AM
Hi RajVee,
Were you able to solve this issue?

I have a similar problem. link to thread In my scenario, I am trying to consolidate information from more than hundreds of Tasks lists into a single SQL Table. The objective is to simplify reporting. All the Tasks lists have the same content type (same field names) and are located across various sub-sites under the same site collection. The package works fine with the List and Site URL configured at design time. But any attempt to parameterize using variables or parameters leads to VS_NEEDSNEWMETADATA error. I like the idea of being able to pull information from SharePoint lists. But, not being able to parmeterize is a serious short coming. I will have to go back to writing custom code.

Regards,
Saurabh