Using Talend to Upload Data to SugarCRM: Part 2 Creating projects and uploading data into SugarCRM
Feb 27, 2013 -
When you open Talend for the first time, you will see the usual license agreements to click through. As always, affirmative answers will move the process along to the point where you can start using the software. Once you’ve clicked through the agreements, you will be brought to the Welcome Screen.
You will see a variation of this screen each time you open Talend. After you create a project, you will always get the screen in Figure 5.
We are going to create a new project called ‘SugarCRM Import’. Enter the name in the ‘Create a New Project’ box and click ‘Create…’ and then ‘Finish’
Once the Project is created the window will change slightly, showing you a list of projects. We only have one project for now, but as new projects are created they can be opened from here.
Select the ‘SugarCRMImport’ project and click ‘Open’.
Connect to TalendForge?
Don’t worry, this isn’t a pay screen. This is asking if you want to connect to TalendForge, the online Talend community. If this sounds like something you might be interested in using, feel free to sign up. It’s not required though, so if you want to skip it click the ‘Skip’ button.
Generation Engine Initialization
This will happen each time you open Talend and can take a few minutes to complete. You can start working with your project while this process is completed, so feel free to let it run in the background.
Unless you turn it off, whenever you open Talend you will be at the Splash Screen. Since we are creating a project, lets click ‘Start Now!’ at the bottom of the middle column.
Creating a Job Design
Talend uses jobs to define the tasks of a given data import. Jobs can be stand-alone items or can be tucked away in a folder with other similar jobs. To keep things organized, I generally create folders for each Sugar module I plan to work with and then create a job in that folder for the given task. In Figure 8, I have created a Contacts folder with an UploadContacts job. I also have a standalone job, ExportUsers, that isn’t in any folder.
To create a job (or folder), simply right-click on ‘Job Designs’ and select the object you wish to create.
When creating a job, you will be presented with a number of options for describing what the job will do. The only field that is required is the Name field. Since spaces are not allowed in the Name field, I stick to mixed case names to make them readable. Talend also offers the ability to mark a job with a version number and a status (development, testing or production), but these are not required.
After you create a job, you will be presented with an empty slate. This space will contain the various connectors to represent the source data, the field mapping and the data destination. To begin creating our import job, we will first create a connector to SQL Server.
Connections to Data Sources can take almost any form. Since we are covering importing data to SugarCRM from SQL Server, our work will begin in ‘Metadata’.
As with ‘Jobs Designs’, connections defined in Metadata can be stand-alone items or can be tucked away in folders to better organize the connections. As a personal preference, I almost never put my connections in Metadata in folders, but that’s only because I am rarely working with more than one or two. Your mileage may vary, so use what works best for you.
Under ‘Metadata’, you can add a connection to a SQL Server Data Source by right-clicking on ‘Db Connections’ and selecting ‘Create Connection’.
‘Database Connections’ (like jobs) have a number of properties to describe the name of the connection and what it will be used for. Only the name is required.
The second and final step in creating the connection is defining the data engine connection. In Figure 13, I am selecting Microsoft SQL Server and then defining all of my connection parameters.
Tip: Notice that my connection parameters include instance=sql2012 in the ‘Additional parameters’ box. Since my SQL Server has two instances, I have to specify the instance in additional parameters, not in the server name as one would expect with ODBC or other Windows applications.
Once all the settings have been entered, click ‘Check’ to confirm they are accurate. If successful, click ‘Finish’.
After the database connector has been added, we need to select the tables or views that contain our data. This is done by right-clicking on the connection and selecting ‘Retrieve Schema’.
Retrieving the schema tells Talend about the fields and data types that are contained in the table or view. You can reduce the number of items that will be returned by selecting types (Table, view, Synonym) and by specifying a name filter.
While creating the table or view (in the example provided in Figure 17) is beyond the scope of this post, you will notice that Talend has detected that some of the fields in my view are BIT (True or False) which means I won’t have to do this later when it comes time to map the data to SugarCRM.
After you’ve retrieved the schema, you can drag and drop it into the workspace. Talend will then ask you what type of component to create. Since this is our data source (this is where data is coming FROM), we will select tMSSqlInput.
Next, we need to tell Talend where this data will be GOING. For this, we will switch to the palette and select our SugarCRM Output Connector. As with our schema connection, we can drag and drop this into the workspace.
So now we know where the data is coming from and where it’s going, it’s time to tell Talend how we plan to map the data between data sources. For that, we need a tMAP. You can also find that in the palette list and drag it into the workspace.
Before continuing, you will need to configure it for your SugarCRM system. To do this, simply click on the SugarCRM connector and select the component tab. The key items here are:
· SugarCRM WebService URL: This can be either local, on-premise or on-demand systems. As a general rule of thumb, the URL will be determined by the path you use to get to SugarCRM from your web browser (https://yourcompany.sugarondemand.com or https://sugarcrm.yourcompany.com) followed by /soap.php. So, using the examples above your URL should be entered into Talend as follows (including the quotes):
· Username and Password
· Module: The module where you will be importing data.
· Action: For imports, this will be set to ‘insert’. You can also use Talend for updates (this may be covered in a later post).
With all the pieces in place, it's time to connect them together. Start with the data source (on the left) and move to the right. To connect objects together, right-click and hold on the object and drag the line to the object to the right. When you are connecting tMap to SugarCRM, you will be asked for an Output name. Any descriptive name will do. Talend will then ask you if you want to retrieve the schema from the target component. Click ‘Yes’ to finish up.
Are you tired yet? Don’t worry – we’re almost done.
We are now down to the last two steps, mapping the fields from the source to the destination, and then kicking off the import.
To start mapping, double-click on your tMap component. This will open the mapping window.
Talend can map one of one of two ways: manually, dragging the source field(s) to the destination field(s), or it can automatically map the fields based on the field names. I am super lazy when it comes to mapping, so when I am creating my source views I always make an effort to name my fields to the matching field in SugarCRM. So, when it comes to mapping, I can just click the ‘Auto map!’ button and then check the list to make sure everything was mapped successfully.
You will notice in the example that not all of my fields are mapped, and that’s actually OK. This view contains some extra information (for example, the ID I want to use for the contact, is listed as a custom field). If you don’t see a field listed, but you know it’s in the module (the Contacts module contains an ID column), I can simply drag it over to the end of the list of fields in the right column. I can also do this with custom made fields, which won’t be listed at all.
As a rule, I generally place all of my custom fields at the end of my views to make this easier to do as they show as grouped together. Once you’ve got your mapping in place, go ahead and click ‘OK’. Talend will ask if you want to propagate the changes, so go ahead and say ‘Yes’.
OK – we are finally ready to send some data to SugarCRM. Click on the ‘Run’ tab in your designer and click the ‘Run’ button. The amount to time to upload your data will vary depending on a number of factors, like the amount of data you are importing and the speed of your Internet connection.
So, we have successfully created a project and imported data from our data source to SugarCRM. Talend provides quite a bit of flexibility in uploading data to Sugar. While we only reviewed the process for Contacts, the same workflow (with different data) can be applied to Accounts, Calls, Tasks, Meetings and even E-mail Messages.
If you have found this article helpful, and would like to read more articles in this series, please let us know! We welcome any feedback you might have so we can ensure that you’re data input process goes as smoothly as possible.