[sims] Script to add student email addresses in to SIMS.net

[kennysarmy]

We are changing our VLE from Moodle to Firefly.

 

Moodle used pupil and staff email addresses from Active Directory whereas Firefly will be pulling data direct from SIMS.

 

We therefore need to add in to every student their school email address.

 

Is there a script to do this?

 

The email address required to be entered for each student would be their admissions number (minus the leading zeros) plus @schoolname.sch.uk

 

eg. [email protected]

[RLR]

There must be a way to do it as we have a piece of software called Salamander that does this for us. Not sure how though so not much help!

[BKGarry]

There was a SIMS Bulk Import Tool kicking around the Forum somewhere.

 

With that you can export your user details from SIMS to a spreadsheet, use the concatenate formula to pull them together and then import it back into SIMS into the email address field.

 

I think the project closed down a while ago, but you may find a copy somewhere (i'm afraid I do not, as I use SalamanderSoft's AD Sync for this, and other things)

[ThomL]

You need to make a csv containing all the data you wish to import to SIMS - I'm not certain which SIMS patch it is that you need to run but I know I've done it before for the same reason - switching to firefly email data needed to be populated in SIMS.

 

I'd get the correct sims patch + csv template, then export students from SIMS to get admission numbers - quick formula in excel then save matching SIMS patch csv format and run the patch. sorted!

[howartp]

Patch 23212 was the April17 version of the patch; it's re-released as a new patch with a new SIMS release.

[kennysarmy]

I've now got patch 20343 on SOLUS

 

and found these instructions:

 

https://faq.scomis.org/wp-content/uploads/2017/12/BulkImport.pdf

[howartp]

Those instructions are within the patch if you export it.

[kennysarmy]

 

Anyone any ideas?

[kennysarmy]

It's fine if I don't point at the script...

 

[kennysarmy]

Needed to extract the ZIP file

[kennysarmy]

 

Sigh.

 

There is no mention of requiring exclusive use, so I gave it a run with a single student in the CSV....

[jthompson]

You possibly need to dbupgrade 'as admin'.

[howartp]

I do all my DBUpgrades as the SA user which you showed in your earlier screenshot before you unzipped it.

 

Peter

[kennysarmy]

Thanks, I'll try again later this evening when everyone is off the network.

[kennysarmy]

Success, running using the SA credentials and when everyone was out of SIMS added all the student emails.

 

I then just needed to remove any rogue parent addresses that had got in the system.

[kennysarmy]

For anyone interested:

 

Patch 20343 has been replace with Patch 23212

[matt40k]

Did you have to run the patch as SA or could you use sysman or another SIMS user?

[ThomL]

Just ran patch 20343 this morning, used SA - worked perfectly.

[matt40k]

Do you know if HAVE to use SA however?

[kennysarmy]

Do you know if HAVE to use SA however?

 

I had a permissions error when I tried with my username - not conclusive I know - but I've most SIMS.net rights and it failed!

[kennysarmy]

Having issues with this, trying to bulk update all our new intake which are now on SIMS.

 

 

Is this suggesting there is a replacement patch for my database?

[Atomic_Zombie]

Sounds like it, most likely there is a new version for SIMS Summer.

[kennysarmy]

Sounds like it, most likely there is a new version for SIMS Summer.

 

You're right, I've been sent the new patch (with no instructions) so I've used the ones I've used previously which are here:

https://faq.scomis.org/wp-content/uploads/2017/12/BulkImport.pdf

 

It's now attempting to update the email addresses but fails with the following:

 

 

 

 

Your database has been restored correctly and can be used normally.

 

The error reported was

Bulk load: An unexpected end of file was encountered in the data file.

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

 

The SQL Batch which caused the error was :

 

 

-----------------------------------------------------------------------------------------

 

-- Main entry point

set nocount on

 

declare @Match_list table(person_id int)

 

declare @imported bit,

@upn_ni_tt varchar(200),

@email_phone varchar(200),

@student_import_E bit,

@Staff_import_E bit,

@stud_contact_import_E bit,

@Staff_contact_import_E bit,

@student_import_P bit,

@Staff_import_P bit,

@stud_contact_import_P bit,

@Staff_contact_import_P bit,

@maxDATE smalldatetime,

@main varchar(200),

@primary varchar(100),

@location varchar(100),

@person_type varchar(100),

@surname varchar(200),

@forename varchar(200),

@midname varchar(200),

@notes varchar(6000),

@N int,

@person_id int,

@student_id int,

@Matches int,

@email_id int,

@telephone_id int,

@email_location varchar(100),

@email_main varchar(100),

@email_primary varchar(100),

@email_notes varchar(2000),

@phone_location varchar(100),

@phone_main varchar(100),

@phone_primary varchar(100),

@phone_notes varchar(6000),

@unchanged_emails int,

@unchanged_phones int,

@Staff_id int,

@MASK varchar(3000),

@msg varchar(4000),

@Original_email_phone varchar(100),

@REPlace_email bit,

@REPlace_phone bit,

@prev_max_phone_id int,

@prev_max_email_id int,

@use_for_fees_docs varchar(100), -- what the CSV asks for

@use_for_fees_documents bit, -- what any existing email record has

@MAIN_PHONE_PER_LOCATION bit -- To allow the utility to handle this situation incorrectly the same way that SIMS does (until SIMS

-- is fixed).

 

select @Staff_import_E = 0,

@student_import_E = 0,

@stud_contact_import_E = 0,

@Staff_contact_import_E = 0,

@student_import_P = 0,

@Staff_import_P = 0,

@stud_contact_import_P = 0,

@Staff_contact_import_P = 0,

@maxDATE = '20790606',

@unchanged_emails = 0, -- number of records that were not imported because they exactly match existing data

@unchanged_phones = 0

 

select @prev_max_email_id = max(email_id) from sims.sims_email

select @prev_max_phone_id = max(telephone_id) from sims.sims_telephone

 

if exists(select 1 from sims.db_update_log where description = '')

begin

set @MAIN_PHONE_PER_LOCATION = 1

end

else

begin

set @MAIN_PHONE_PER_LOCATION = 0

end

 

delete sims.tmp_email_phone_import

delete sims.tmp_email_phone_import_log

 

set @imported = 0

 

if (sims.conv_fnb_file_exists('D:\CSV_IMPORT\', 'EMAIL_PHONE.CSV') = 1)

begin

BULK INSERT sims.tmp_email_phone_import

FROM 'D:\CSV_IMPORT\EMAIL_PHONE.CSV'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 1)

 

set @imported = 1

end

else if (sims.conv_fnb_file_exists('C:\CSV_IMPORT\', 'EMAIL_PHONE.CSV') = 1)

begin

BULK INSERT sims.tmp_email_phone_import

FROM 'C:\CSV_IMPORT\EMAIL_PHONE.CSV'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 1)

 

set @imported = 1

end

else if (sims.conv_fnb_file_exists('S:\CSV_IMPORT\', 'EMAIL_PHONE.CSV') = 1)

begin

BULK INSERT sims.tmp_email_phone_import

FROM 'S:\CSV_IMPORT\EMAIL_PHONE.CSV'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 1)

 

set @imported = 1

end

else if (sims.conv_fnb_file_exists('U:\CSV_IMPORT\', 'EMAIL_PHONE.CSV') = 1)

begin

BULK INSERT sims.tmp_email_phone_import

FROM 'U:\CSV_IMPORT\EMAIL_PHONE.CSV'

WITH ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' , FIRSTROW = 1)

 

set @imported = 1

end

[ass17]

When we hosted our firefly we had to create a UDF called FFUsername. Now we are hosted, we still fill this in and my script queries AD and matches the pupils username to the FFUsername and then adds there AD email into SIMS.

 

We couldn’t find a way other than to manually add something in to get us started with some form of automation.

[kennysarmy]

Does anyone have access to any instructions for Patch 23212 they could send over in case there is something different to the instructions I'm following for 20343...