SharePoint Connection Manager Issue

[ezgig...@gmail.com]

Hi,

I have a SharePoint Connection Manager (in the package, not project) that's used in several SharePoint destinations within a Eoreach loop (loops over a Recordset using the Foreach ADO Enumerator). The Server Host of the SharePoint connection is assigned using a variable that changes value with each iteration of the loop. But the connection always uses the first value it gets.

Here are my findings:

• As the default value of the variable is different than the one it gets in the first iteration of the loop I know that the populated value of the variable is used by the connection in the first iteration. So the expression for the connection does work the way it should when it enters the loop the first time. But never changes afterwards.
• By using breakpoints I have confirmed that the value of the variable is actually changing with each iteration, and since there are multiple variables being set in every iteration I can see other sources/transformations functioning properly that use those variables. So the loop and the variable assignments are working fine.
• I have tried setting both the server host and the connection string using expressions. I also tried setting RetainSameConnection to false.
• I have tried SSIS+ versions 1.6 and 1.7. Also both VS 2012 (SQL 2012) and VS 2015 (SQL 2014).

What do you think could be the reason for this? Anything else I can try?

Help or ideas are appreciated.

[Ivan Peev]

Right-click on the SharePoint connection manager and select Properties. Set RetainSameConnection=False .

Try now to execute your package.