Introduction

I've been involved as a systems administrator for several Rock implementations.  Until recently, I didn't have a lot of knowledge of the Rock UI and functions available.  All I was doing was setting up Rock on Azure and converting data, as appropriate, and others that are more knowledgeable about Rock were doing everything else.  Documentation about migrating data is a little sparse from my observation and I had to do a lot of investigating and trying things out to even use Slingshot and the Bulk Import block to get data into Rock.  I will document here what I learned in that process, so if you're just trying to use Slingshot and the Bulk Import block, this might be a good reference for you. I'm also documenting what I learned about getting data out of ACS.   I am also not an experienced ACS user and I didn't find good documentation at ACS on the capabilities of exporting data from ACS.  I will also document what I learned about how to get the data I needed out of ACS into a form that Slingshot can use.

In addition to the general functionality of Slingshot and exporting data from ACS, I also had the challenge of trying to get attendance data from ACS into Rock.  Because of the way ACS stores attendance, Slingshot cannot get it into a form that the Bulk Import block can use.  ACS exports to a Microsoft Access database, so I wrote VBA code to manipulate and convert the attendance, group and groupmember data from ACS into a format that the Bulk Import block can pull into Rock.  I put a user interface within Access that will guide you through getting that data into a Slingshot file that can be imported into Rock.  I will document that process here and provide access to that database.

Exporting from ACS

Clearview Baptist was using ACS OnDemand.  I know there is a newer version of ACS that has a much more modern user interface, but I don't know anything about the data formats or export capabilities of that version.  First login to ACS and bring up the People Suite.  On the People tab, under Output, click on Searches.  

Click on Advanced Exports and Create New Export.

Slingshot expects a Microsoft Access database, so select the Database Export option.  

The default export only includes Personal data about people, so we will need to move to the Included Data tab and select all the data that is available within ACS.  Not all of it will be used by Slingshot, but check them all unless you know of data that you do not want to migrate to Rock.

We wanted to convert all historical data that we could from ACS into Rock.  In order to do that, I changed the date range included to the entire time period that we had data in ACS.  At a minimum, click on the CB Gifts button and change the posting period to that date range for which you would like to convert data.  That may be all that is necessary for the data that we are converting, but, to be safe, I changed the date range for all financial areas.  

ACS will only export one year's worth of attendance data and the default is the current year.  Assuming that you want to convert all attendance data up to the current year, you can leave the defaults in the Class Rolls area.  If not, you can change the effective date to the end of the last year you want to convert.  There will be more information about how to get all our attendance data later.

Go through all the Options buttons and select all the data available for each option.   None of the optional data is already selected by default.  Things like Individual Notes and Family Notes can be imported into Rock, but you'll need to make sure they are included in the export.

On the Format tab, make sure the File Format is Microsoft Access.

Give the export a name that will indicate this is the full export as we will be creating smaller exports for every other year for which we want attendance data.  The other exports will be named ClassRollsxxxx.mdb where xxxx is the 4 digit year of the attendance data that is in it.  Since this full export contains the data for the current year (or the year of the effective date you selected), you could name this one ClassRolls2020.mdb, as an example for the year 2020.  It will make it easier later if you use a name formatted like this.  Run your export and a database will be created in your My Documents folder on the ACS server.  We will copy those to our local computer later after the other exports are completed.

After you have created this export, you can use the Copy to New Name button on the Advanced Export Menu to create another export that has all the same options selected.  For each of the years for which you would like attendance data, name the export ClassRollsxxxx and unselect everything except for Class Rolls.  It doesn't hurt anything for each of them to have all the data, but that will make each Access database file much larger than is necessary.  For each class roll export file, uncheck the Use System Date and provide an effective date that is the last day of the year for the year xxxx in ClassRollsxxxx.

When we're done, you should have one Access database that has all the data in it and a series of other databases that only have Class Roll data in them for each year. In my case, I had 19 Access databases named ClassRolls2002.mdb through ClassRolls2020.mdb.

We now need to move all of those files to the local computer.  You can close the People Suite now and return to ACS OnDemand.  Click on the File Manager icon.

I would expect that the OnDemand Drive window will be sitting in the My Documents folder for the account with which you logged in to ACS OnDemand.  This should be the folder that contains all the export files that you created.  On the Client File System, select a folder into which to move all these databases.  I used c:\temp, but use any folder you wish.  Just remember where you put them as you'll need them later.

Creating a Slingshot File

Slingshot was created by Spark Development and is available on GitHub at https://github.com/SparkDevNetwork/Slingshot.  It is interesting to me that source code is the only way Slingshot is provided.  In order to use it, you would have to install Visual Studio and compile the provided Slingshot.ACS solution into an executable.  Considering that most people doing this data migration work may not have a need to have Visual Studio and may not know how to compile, it seems an extra hurdle to jump over to migrate your data.  There are other migration tools available from partners and third parties. I have had some success using some of them, but Slingshot is provided by the Rock Core team and, as such, should be maintained to remain compatible with future versions of Rock.  It appeared that it would be the best solution for converting ACS data. I have compiled the Slingshot.ACS solution and zipped the executable and other needed files so that you can download from this link: Slingshot.ACS.zip  Create a Slingshot folder somewhere (I just put it under c:\) and unzip this file into this folder.  Double click on the Slingshot.ACS.exe file to run the Slingshot application that will pull data from full data export from ACS to create a .Slingshot file that can be imported into Rock.

I'm selecting ClassRolls2020.mdb since this is the file from ACS that has the full export of all data.  You may have named it something else when you created it.  Just make sure you're selecting the one that has the full set of exported data.  Press Open and on the next screen, change the Import Records Modified Since date to the first date for which you have data.  Change the Email Type to Export to the ACS email record that you would like to show up for the individual in Rock.  Though you may have multiple email addresses on file in ACS, you can only bring one email address into Rock in this process.

 Press the Create Download Package button and Slingshot will create an acs-export.slingshot file in the same folder in which the Slingshot.ACS executable resides.  You can look at the contents of this file with any zip application.  You can open it from within your zip application or change the file application defaults so that double-clicking on a .slingshot file will automatically open it with your zip application.  This is just if you want to look at it to see what is in it.  The .slingshot file contains multiple comma separated value (CSV) files that were created from the data in the ACS export database.

Converting ACS Attendance Records

All of the attendance records from ACS are placed in the ATClass table in the export database.  This is not a normalized table, if you know anything about proper normalization of database tables.  Each row in the table is for one specific person's records within a specific class (or group in Rock terminology).  There are twelve columns in this row, one for each month of the year.  Each of those columns is a text or string field with up to 5 characters in it.  Each position in that text field represents a week of the month and contains an A for absent, a P for present and a space for unknown.  This is indeed a strange way to store this type of data and is why each exported file can contain only one year's worth of attendance data.  I created some code in an Access database that will read through all the attendance data and convert it into a form that the Slingshot Bulk Import tool, created by the Rock core team, can use to import it into group attendance records in Rock.  Download my Access database  executable at ACSAttendance.mde.  You should be able to run this file whether or not you have Microsoft Access installed on your computer.  If you have any trouble running it, please let me know.  The first screen asks you to select the "People File."  This needs to be the database that has the full export for the entire date range from ACS.  All that is really needed out of this file is the People table since it contains all the people that were ever recorded in ACS.  You would not want to use one of the smaller export files.

After selecting the database file, press Import Table.  This will import the People table from the selected database into the ACSAttendance database.  On the next screen, select the folder that contains all the individual ClassRollsxxxx.mdb files and provide the first year and the last year for which you have files.  For me, this was 2002 to 2020.  If you did not name the full export database with the ClassRollsxxxx.mdb name, you may wish to rename it now so that the process can pull the ATClass table from each of the individual databases.  For me, ClassRolls2020.mdb is the full export as well as having the attendance data for the last year in ACS, which was 2020.  

Press Import Tables and the application will loop through all the files from the Initial Year to the Final Year, copying the ATClass table into the ACSAttendance database, naming them ATClassxxxx where xxxx is the year's database that it was pulled from.  You may get an Access Security Notice for some or all of your databases as this application tries to open them.  There are ways to prevent this security notice, but it is not worth going through the work required.  Just press Open for each of these files on which a security notice is presented.  

On the next screen, you need to select whether or not you want to remove group (class) membership for people that haven't attended recently.  We want to record all the attendance for everyone over the entire date range for which we have data.  However, you may not wish to have everyone that has ever attended any particular class made a member of that class.  This just depends on how you want to handle group membership in Rock. You could mark everyone that hasn't attended recently as inactive in the class, but maintain them on the roster.  Or, as I chose to do, you may prefer not to put them on the roster if they hadn't attended in the last year.  Either way, we are still recording their attendance in this class, even if they are no longer on the roster.  Press Convert Data.  Depending on how many people for which you have attendance data and how many years of data you have, this process can take quite some time.  There is a progress bar shown so that you can see that it is working and which year it is working on.

On the next screen, select the acs-export.slingshot file that was created by Slingshot earlier and press Update Slingshot.  This will add three comma separated value (CSV) files into the slingshot file in the format that is required for importing into Rock.

It would obviously be much better if all the data conversion that I've done within this Access database would be done within the Slingshot.ACS program.  I contemplated making my changes to the Slingshot.ACS source code to this, but I don't have much experience with C# and the structure of the code was too difficult for me to quickly add this code there.  Someone that knows C# and the Rock code structure could probably modify Slingshot.ACS, moving my code into Slingshot.ACS.  You may download my database with source code at ACSAttendance.mdb.  

Importing into Rock with Bulk Import Block

Finally, we're ready to import the ACS data into Rock.  The core team has created a block called Bulk Import can be used to read a Slingshot file and pull the data into Rock.  Unfortunately, this block is not, by default, set up on any page within Rock, so you'll have to do that if you haven't already.  In Rock, go to Admin Tools, then CMS Configuration and click on Pages.  In order to put the Bulk Import tool on the Power Tools menu (which is where it seems to me it belongs), expand the Internal Homepage folder structure, then Admin Tools and Rock Settings.  

Rather than expand Power Tools, click on it.  At the top of the directory structure, press Add Page and select Add Child to Selected.  This will create a blank page under Power Tools.  Name the page Bulk Import and save it.

Scroll down to the Main Zone section and press Add Block to Zone.  Pull down the Type list and select the Bulk Import block and press Save.

Now we're ready to use Bulk Import to import the Slingshot file we created.  Select Bulk Import from the Power Tools page and press the Upload button to select the acs-export.slingshot file that we created. 

After selecting this file, if you receive an error, it is because the default permissions on the SlingshotFiles upload folder will not allow Rock to write a file into this folder.

You will need to correct the permissions on this folder.  You will need to run Windows Explorer on the computer that houses the Rock Internet Information Server (IIS) and browse to the Slingshot folder.  That folder should be under the inetpub folder on the drive that contains the Rock server files.  On my machine, the path to the folder is c:\inetpub\wwwroot\App_Data.  Right click on the SlingshotFiles folder, click on Properties and the Security tab.  Change the permissions so that the IIS_IUSRS user has Full Control.

Now you should be able to go back to the Bulk Import Tool and upload the acs-export.slingshot file.  When it successfully uploads the file, press the Import button.  It will show you the progress that it is making and then present a results page when it has completed the import.  You've completed the process and should be able to see the results in Rock.  I still had some data cleanup I wanted to do after the data was in Rock.  This is not a problem with the migration process.  Rather, this is an indication of problems in the data as it was stored in ACS.