As a member of the Professional Services team here at Sugar, one of the services that is often requested of us is migrating a client’s data from their existing CRM into Sugar. In the past, our team would write custom migration scripts that read their data, performed some basic cleansing, transformed it into Sugar format, and then imported it into the database. This was a laborious task given the disparate data formats out there and varying levels of data quality–a task which none of us looked forward to doing.
Recently, however, some of us began experimenting with a tool called Open Studio, which is provided by one of Sugar’s technology partners, Talend. Open Studio is an open source data integration suite licensed under GPLv2 and provides a variety of tools for bridging the gap between disparate applications.
Using Talend Open Studio 2.4, the following are the steps that I followed to setup a migration of Note records from a Salesforce.com CSV file, including copying and renaming the associated attachments. Theser steps will work for Sugar Community Edition, Sugar Professional Edition and Sugar Enterprise Edition.
Note that the copy and rename step isn’t necessary for migrating SFDC Note data since their file attachments are already uniquely named with the record id (as it is in Sugar). However this step highlights some of the additional functionality provided by Talend and is a useful exercise as other CRM systems don’t save their attachments with a corresponding GUID.
Now open Talend Open Studio and follow these steps:
- Setup a tFileInputDelimited object that points to the SFDC Note.csv file
- Create a tFileList object that points to the directory that contains your attachment files (setting up the proper filters if more than the attachments exists within the directory)
- Create a tFileProperties object
- Right click the tFileList object and select Row->Iterate, and drag the line over to the tFileProperties object
- Click on the Component tab for the tFileProperties object and click in the File textbox. Press ctrl+space to bring up the contextual selection menu, and select the CURRENT_FILEPATH property for the tFileList object.
- Create a tMap object
- Right click the Notes tFileInputDelimited object and select Row->Main, and drag the line over to the tMap object
- Right click the tFileProperties object and select Row->Lookup, and drag the line over to the tMap object.
- Create a tFileCopy object
- Create a database output object (i.e. tMysqlOutput)
- Right click the tMap object and select Row->*New Output* (main), and drag the line over to the tFileCopy object. Give this output object a unique name (i.e. file_copy).
- Right click the tMap object and select Row->*New Output* (main), and drag the line over to the database output object (tMysqlOutput). Give this output object a unique name (i.e. notes)
- Double click the tMap object to bring up the map editor.
- On the left hand side of the map drag the column that contains the file name of the attachment down to the basename column on the tFileProperties schema. Select Inner Join.
- Drag the columns from the Notes schema on the left hand side over to the corresponding notes schema on the Right hand side. Make sure that your schema types are setup properly to avoid runtime errors. (i.e. date fields are set to Date, Boolean fields are set to Boolean, etc)
- Create a new column called new_file_name on the file_copy schema.
- Drag the column containing the id from the left hand side notes schema over to the new_file_name column on the file_copy schema
- Click OK to close the map editor.
- Click the Component tab for the tFileCopy object.
- Enter the CURRENT_FILEPATH property for the tFileList object into the File Name text box as you did for the tFileProperties object (step 5).
- Select your destination directory (for Sugar attachments, this is cache/uploads)
- Select the Rename checkbox and enter the new_file_name property for the file_copy object into the Destination filename textbox (for the above scenario it would be file_copy.new_file_name)
- Click the Run tab.
- Select the Statistics checkbox if you want to see where in the migration the job currently is, plus how fast the data is being migrated over.
- Click Run.
This should get you going to handle a basic migration of Notes data with copy and rename of the attachments. Hopefully, you will be able to take this and extend it to handle migration of data from any source into Sugar.