SMP Connector Solution - Import from a Sharepoint list

[Migration User]

Calling all OLEDB/Sharepoint experts with an understanding of SMP Connector Solution...

I'm trying to import a list (it contains a table of all the software managed by the Licensing Compliance team), into a bespoke resource type/dataclasses that will store the data within SMP 7 for reporting purposes. As with all connectors, the solution involves a Data Source definition and an Import Rule.

Warning: Don't try to recreate this on your system unless you have support from a Symantec engineer.

Data Source

I have defined an OLEDB Data Source definition, with a directly entered connection string (below), and associated import query.

Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=http://sp-sites/xx/;List={3DC2C988-C73C-4GGG-ADF9-C3575FE3814E}

When I click the View Import Data button on the data source I can see the data.

Import Rule

When I define the import rule to use the data source and map the fields from the Sharepoint list to the target resource type, the definition can be made (implying that the import rule designer screen can get the list of fields that need to be mapped), but when I run it, either as a test or non-test run, it shows a dialogue with 1%, which never moves.

When I stop the job, it eventually appears to cancel after a bit of delay, then I can close, but I can't then rerun the job (it says it is already running). The cancelled import task leaves incorrect status entries in one of the task tables, and I need to get our Symantec Engineer to assist with overcoming these errors.

Other information

• Known defect with UNC paths: We have also experiences similar symptoms with a data source that uses a UNC path format to a file on network connected storage. If the UNC path is not mapped as a drive, or defined as a network location on the SMP server, the import will hang in the same way. Trouble is I can't solve the issue the same way with Sharepoint.
• Sharepoint 2007: The sharepoint site I'm trying to read is currently running sharepoint 2007.
• We are running SMP 7.1, MP1 on the instance where this is failing.
• No messages are written to the logs indicating any errors related to this activity.

Can you help? Why can I see the data via the button, but the import rule fails?

[Triinu Gross]

How big is the amount of data that you are trying to import - i.e. how many rows approximately?

Also, have you tried to export data from Sharepoint into CSV or MS SQL, and then to import from that data source?

[Migration User]

With thanks to John Doyle, the underlying issue now appears to be solved.

It appears that the Altiris Service Account could not connect to the Sharepoint source with IMEX=2 in the connection string, which demands Read/Write/Update/Delete privileges. By changing this to IMEX=1, it only  requires Read privileges (which it has) and happily connects and transfers the data.

We still have outstanding issues as to why Altiris didn't handle this connectivity issue gracefully and give us some clue as to what it needed, this aspect still needs to be investigated by Symantec backline support, but the reason why it probably worked with data preview and not when the rule was run (scheduled), is because my personal logon had the required read/write/update/delete privileges against the sharepoint source, but the Altiris Service account only had read access.

[Migration User]

Hi Triinu,

Sorry I didn't acknowledge your post, I must have missed the e-mail telling me that you'd commented.

My interim solution (workaround) was to export the sharepoint list to an MS-Access table.

From Sharepoint you can generate an MS-Access Query that connects to sharepoint to retrieve data. I encapsulated that query within a Macro/Module in the Access Database. I configured the Macro/Module to automatically run when the database was opened, materialising the sharepoint data within a table in that Access Database. Then the Access database shuts itself down again.

I scheduled the MS-Access database to open via a scheduled task (running on a windows 7 workstation).

This gave me an OleDB data source (the Access Database) that I could connect to from Altiris and retrieve the data from the table within it.

This worked reliably for the two months or so that it took us to find the reason for Altiris locking up when trying to connect directly to Sharepoint.

[Triinu Gross]

I'm glad you've got your problem solved!