SOLVED

flow to sync SPO list with SQL table

%3CLINGO-SUB%20id%3D%22lingo-sub-394780%22%20slang%3D%22en-US%22%3Eflow%20to%20sync%20SPO%20list%20with%20SQL%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-394780%22%20slang%3D%22en-US%22%3E%3CP%3Eso%20I'm%20developing%20in%20SPO%20and%20got%20into%20an%20issue%20with%20%26gt%3B5k%20list%20items.%20Fixed%20that%20with%20creative%20filtered%20views%2C%20then%20another%20issue%20sprang%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20list%20in%20question%20now%20has%207500%20or%20so%20items%20in%20it%2C%20and%20we%20need%20to%20report%20on%20the%20data%2C%20and%20therefore%20need%20to%20have%20a%20complete%20data%20set.%20So%20using%20the%20creative%20filtered%20views%20(quarterly%20dated)%20I%20exported%20all%20the%20data%20by%20opening%20each%20view%20in%20turn%20and%20exporting%20to%20excel%2C%20then%20cut%20and%20pasted%20from%20all%20to%20first%20sans%20table%20headings%20and%20viola%20I%20got%20a%20complete%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20now%20attempting%20to%20make%20this%20a%20little%20more%20robust%20so%20thinking%20of%20building%20a%20Flow%20to%20sync%20modified%20or%20new%20items%20to%20a%20SQL%20table.%3C%2FP%3E%3CP%3EUsing%20the%20on%20premises%20data%20connector%20I%20was%20able%20to%20insert%20row%20and%20update%20row%20just%20fine%20to%20SQL.%20To%20determine%20if%20the%20flow%20run%20was%20inserting%20or%20updating%20I%20put%20a%20column%20in%20the%20list%20with%20a%20number%2C%20and%20tested%20if%20that%20value%20was%20equal%20to%200%2C%20if%20so%20I%20inserted%2C%20else%20updated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20with%20this%20approach%20is%20that%20somewhere%20in%20the%20flow%20I%20it%20was%20necessary%20to%20update%20the%20list%20to%20increment%20the%20count%2C%20and%20if%20you%20are%20following%20this%20logic%20you%20may%20have%20just%20figured%20out%20that%20this%20creates%20a%20recursive%20loop.%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20switched%20to%20using%20a%20query%20or%20stored%20proc%20in%20sql%2C%20and%20got%20an%20error%20saying%20the%20data%20connector%20didn't%20permit%20use%20of%20a%20query.%20%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20I%20could%20do%20this%20with%20Layer2%20(have%20used%20that%20in%20the%20past)%2C%20also%20had%20good%20luck%20running%20codeplex%20SSIS%20SharePoint%20list%20source%20and%20destination%20connector%20for%20something%20similar.%20In%20my%20current%20contract%20I%20need%20to%20not%20purchase%203rd%20party%20s%2Fw%2C%20and%20have%20not%20been%20given%20MSDN%20(VS)%20to%20play%20with.%3C%2FP%3E%3CP%3EAnybody%20got%20any%20suggestions%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-394780%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Flow%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESharePoint%20Online%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-400285%22%20slang%3D%22en-US%22%3ERe%3A%20flow%20to%20sync%20SPO%20list%20with%20SQL%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-400285%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F278105%22%20target%3D%22_blank%22%3E%40NeverHere%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Etook%20me%20a%20while%20to%20finger%20out%20the%20connections%2C%20but%20eventually%20stumbled%20into%20connecting%20powerbi%20to%20the%20spo%20list.%20Way%20cool!%20great%20suggestion!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-399481%22%20slang%3D%22en-US%22%3ERe%3A%20flow%20to%20sync%20SPO%20list%20with%20SQL%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-399481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F278105%22%20target%3D%22_blank%22%3E%40NeverHere%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20experienced%20powerbi%2C%20but%20usually%20doing%20data%20modeling.%3C%2FP%3E%3CBLOCKQUOTE%3E%3CHR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F278105%22%20target%3D%22_blank%22%3E%40NeverHere%3C%2FA%3E%26nbsp%3Bwrote%3A%3CBR%20%2F%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F268631%22%20target%3D%22_blank%22%3E%40RobinOrgHood%3C%2FA%3E%26nbsp%3BDo%20you%20need%20to%20push%20to%20SQL%20to%20report%3F%20Could%20you%20just%20connect%20PowerBI%20to%20the%20SharePoint%20list%20and%20report%20that%20way%3F%3C%2FP%3E%3CHR%20%2F%3E%3C%2FBLOCKQUOTE%3E%3CP%3E%3CBR%20%2F%3EI%20have%20never%20%22connected%20it%20to%20SharePoint%22%2C%20but%20since%20you%20are%20the%20second%20today%20to%20suggest%20this%2C%20it%20sure%20is%20on%20my%20list%20of%20things%20to%20explore.%20Any%20more%20hints%20on%20steps%20to%20take%20would%20be%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-397025%22%20slang%3D%22en-US%22%3ERe%3A%20flow%20to%20sync%20SPO%20list%20with%20SQL%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-397025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F268631%22%20target%3D%22_blank%22%3E%40RobinOrgHood%3C%2FA%3E%26nbsp%3BDo%20you%20need%20to%20push%20to%20SQL%20to%20report%3F%20Could%20you%20just%20connect%20PowerBI%20to%20the%20SharePoint%20list%20and%20report%20that%20way%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

so I'm developing in SPO and got into an issue with >5k list items. Fixed that with creative filtered views, then another issue sprang up.

 

The list in question now has 7500 or so items in it, and we need to report on the data, and therefore need to have a complete data set. So using the creative filtered views (quarterly dated) I exported all the data by opening each view in turn and exporting to excel, then cut and pasted from all to first sans table headings and viola I got a complete list.

 

I'm now attempting to make this a little more robust so thinking of building a Flow to sync modified or new items to a SQL table.

Using the on premises data connector I was able to insert row and update row just fine to SQL. To determine if the flow run was inserting or updating I put a column in the list with a number, and tested if that value was equal to 0, if so I inserted, else updated.

 

The problem with this approach is that somewhere in the flow I it was necessary to update the list to increment the count, and if you are following this logic you may have just figured out that this creates a recursive loop. 

Then I switched to using a query or stored proc in sql, and got an error saying the data connector didn't permit use of a query.  

 

I think I could do this with Layer2 (have used that in the past), also had good luck running codeplex SSIS SharePoint list source and destination connector for something similar. In my current contract I need to not purchase 3rd party s/w, and have not been given MSDN (VS) to play with.

Anybody got any suggestions?

 

Thanks in advance!

 

3 Replies
best response confirmed by RobinOrgHood (Occasional Contributor)
Solution

@RobinOrgHood Do you need to push to SQL to report? Could you just connect PowerBI to the SharePoint list and report that way?

@NeverHere 

I have experienced powerbi, but usually doing data modeling.


@NeverHere wrote:

@RobinOrgHood Do you need to push to SQL to report? Could you just connect PowerBI to the SharePoint list and report that way?



I have never "connected it to SharePoint", but since you are the second today to suggest this, it sure is on my list of things to explore. Any more hints on steps to take would be appreciated!

@NeverHere 

took me a while to finger out the connections, but eventually stumbled into connecting powerbi to the spo list. Way cool! great suggestion!