Jitterbit Best Practices

At my current position, we handle a lot of data shuffling using the Jitterbit integration tool. It’s a small product by a small company, but they’ve been working on it for a while and it has some interesting features. It seems primarily geared towards the semi-technical user – maybe an IT manager who needs to feed some data into SalesForce from an on-premises HR system, or the like, and doesn’t have time to figure out a large, comprehensive solution like Cast Iron, Talend, or Informatica. Jitterbit aims to be a turnkey solution.

But that isn’t how we wind up using it; our requirements generally quickly push us into the “advanced scenarios” when migrating data. As such, these best practices focus around advanced or complex Jitterbit jobs. If an out-of-the-box Jitterbit Connect wizard gets you exactly what you need, please ignore all the advice here. If you find yourself having to do a lot of work “by hand” in Jitterbit, performing tons of complex transformations or manipulations of data, then you may want to read on.

Scripts

Without scripts Jitterbit would be almost unusably inflexible. I may be being harsh, but we really do wind up using this functionality quite a lot to get it to handle all our requirements.

Our most common uses for scripts are as follows:

  1. Run operations with more intelligent error-handling and logging.
  2. Execute a simple, targeted database update or query.
  3. Perform some basic, reusable data manipulation (essentially a function).
  4. Compiling logged information for a detailed summary email.

As with SOLID principals in OOP, follow the SRP and keep the scope of a script small. I try to think of them as functions, even though though they don’t really allow parameters or return values. To overcome that limitation, I use a good naming scheme like Translate $variableOne into $variableTwo. Similarly, a query could be Query SomeDatabase for $returnValue from $uniqueKey and an update Update SomeDatabase $uniqueKey FieldName from $valueVariable. Obviously you’ll want variable names that are more descriptive.

Sources and Targets – No Touch Deploys

If you have development, test, and production environments for JitterBit as well as your source and target integration systems, I highly recommend moving the connection information for those systems into predefined global variables section in the Jitterbit.conf file (accessible via the Jitterbit client admin console). You can then change the values for the server, database, user, etc. specified by [jitterbit.variable{default value}] notation, which will read the values from the configuration file at runtime – and use the default you specify at design time).

If all connection and environment specific information can be moved into the configuration file, you can export and import your Jitterbit package from development to test to production without making any changes – practically touch free. It’s not quite continuous deployment, but better than manual tweaks at each release stage along the way. You can also create a small verification script that loads the values of all the global variables so you can confirm that they’re being set correctly.

Transformations

Keep complex logic out of the transformation mapping and the condition. Move it into a script where it can be tested in isolation (more on that below). A simple null check, Trim(), or other simple manipulation can stay in the mapping, but once you need two lines of code to do whatever it is your doing, you should probably think about a script and a test.

The condition in a transformation can be used to trigger a per-record set of logic if you need it, even if you don’t need to actually decide if the record will be included. Just add a “true;” at the end of the condition after your per-record logic. I do recommend pulling complex code out of the condition as well, just like in the transformation mappings.

Tests

Tests for Scripts

It is possible to create almost-automated unit tests for scripts in Jitterbit. You create a test script for each work script you have – be it a query, transformation, or update script. If you’ve kept things fairly single-responsibility, it should be an easy matter to setup the required environment, run the test, and check the results. It can be handy to note that if you add a variable to a line of Jitterbit script as an expression on its own, Jitterbit will helpfully add its value to the test screen output.

You could take this a step further and create a “JBUnit” script that would call each test script and raise an error when a value wasn’t as expected. All of this would have to maintained manually, unfortunately, as there is no way to detect which scripts are meant for testing. The Pit of Success is nowhere to be found in Jitterbit Land.

Tests for Transformations

You can test transformations using test sources and targets. While this has limits, since the source and target data could change and can’t be verified automatically from within Jitterbit, it’s definitively good practice to verify transformations are happening as expected before you point at a more realistic, hard to rollback target. For example, we have a process that takes data from a source system and puts it into an XML file where another system immediately picks it up. In order to run a test here, I created a second test operation that exactly mirrors the standard operation, except it uses a different file share target and doesn’t update the job last run time (which is in its own script so its easy to omit). This way I can generate as many test outputs as I like without causing any changes in the actual target test system.

Security

As something handy to know, here are the steps to allow read-only console access to a project, including queue, schedule, logs, and history:

  1. Open the Jitterbit client and login to the server as an account with admin privileges.
  2. Download the project whose permissions will be set and open it (or start new project).
  3. Actions > Set Permissions should open the permissions for the project (also can right-click on the Project: Name field under the Design tab).
  4. In the Permissions panel, click the Anonymous user, and set Allow for Read. Click Apply.
  5. Click the groups you want to allow read/execute and set Allow for Read and Execute. Click Apply.
  6. Check the Apply these permissions recursively box, and click Apply again.
  7. Deploy the project.
  8. Verify the changes have worked by logging in with a non-admin account in the Developers group and loading the Project Console, System Operation Log, System Operation History, or Scheduler Console.

Multiple Schedules

This one is fairly obvious, but we’ve had to actually use it and it’s handy so I’ll note it. Jitterbit has limitations on schedules – you cannot, for instance, set an operation to run at one time of day Monday through Friday, and a differing time of day Saturday and Sunday. You can, however, have a schedule set to not run on particular days of the week. Using that, you can create a script operation whose whole job is to have the Monday through Friday schedule and runs the actual operation at a given time of day, and a second script operation that is set to go off Saturday and Sunday at the different time. By keeping the “true” orchestration as the one called by both scheduled script operations, you Don’t Repeat Yourself.

Decoupling Targets

I thought of this technique while writing this article. I haven’t yet used it, but if I had a complex enough transformation, I’d consider it. Set all targets to a temp text location instead of mapping to a web service, database, XML file, etc. Then you could easily take the output of any transformation in a solution and dump it through to a CSV file (or XML or whatever) for analysis during testing, and easily switch to a basic text to actual-target pass-through operation for production. This essentially “decouples” a transformation from its target, something I feel Jitterbit is sorely missing. Would it be worth the extra work? Again, for a complex transformation or one that might require differing targets at different points in time, I think so as the “pass-through” transformations wouldn’t add a lot of overhead.

Conclusions

By applying some sound development principals to complex Jitterbit projects, you can get something a little more maintainable and robust.

10 thoughts on “Jitterbit Best Practices

  1. Can you please provide an example on how to set up the configuration file for Dev, QA, and Prod environments? Thanks!

    1. Hey Elay,

      Sure. So in your Jitterbit.config file in your Dev environment (Admin Console > System Console > jittbit.conf tab), find the [PredefinedServerGlobalDataElement] section. In here you can add any variables you like. I prefer to prefix the names with something that will prevent conflicts with jitterbit variables. So for a simple database setup you might have:

      [PredefinedServerGlobalDataElement]
      myCompany.bi.server = 'DEVSERVER25'
      myCompany.bi.database = 'SALES_REPOSITORY_DEV'

      Then in your database Source or Target in your Jitterbit project, instead of using the DEVSERVER25 as the database name, you’d use [myCompany.bi.server]. Note you can optionally use [myCompany.bi.server{DEVSERVER25}] to give it a default value, which can help with testing. Same for the actual database, you’d use [myCompany.bi.database] instead of the explicit name. Then all you need to do is add similar matching QA or settings on your QA Jitterbit server:

      [PredefinedServerGlobalDataElement]
      myCompany.bi.server = 'QASERVER25'
      myCompany.bi.database = 'SALES_REPOSITORY_QA'

      Now when you deploy your Jitterbit package from DEV to QA you won’t have to change any of the database settings, it’ll get them from the config file. This technique works with most “free text” boxes in SalesForce, including SalesForce orginazation settings, email addresses / servers, and more. Hope that makes things clearer.

      1. Thanks Ogro!
        That was so helpful, and able to implement it. Now I want to do the same with Local File folder paths(like /home/test). However, this technique doesn’t seem working for me for file paths. I tried something like the following:

        [PredefinedServerGlobalDataElement]
        myCompany.bi.Folder = ‘/home/test’

        Any help is truly appreciated!

      2. Hey Elay, glad to be of help. We haven’t used Local File sources/targets with the config settings, but we have done network file shares, and sometimes we have issues with slashes. For example, we’ve had to enter some items like so: myCompany.networkFolder = ‘\\\server\\Share’. My recommendation to you would be to play around with escaping the slashes in your entry as well. ‘\/home\/test’, maybe. Also, you could create a new script that only calls that variable name and nothing else, “$myCompany.bi.folder;”. That should allow you to see the setting that the config file is providing to your project after it’s “processed” to make sure it’s coming through as you expect.

  2. This is such a great article, thanks for taking the time to write it up. Would you mind giving an example of how you would use a script to compile logged information into one summary email? I’m struggling a bit with error handling and notification, and this sounds like it could be a great solution. Thanks!

  3. Hi Ogro, Great article. I’m using Jitterbit for a conversion project. Hoping you can answer a couple questions.
    1. Within a transformation formula builder on a field map, i need to have some nested if statements. How do i create statements like “if (a=1 and b=2) or (a=1 and b=3) then C=3 else C = 4” ?

    2. You talked about using Scripts instead of putting complex logic into a transformation. Can you provide an exmaple of this?

    Thank you.

    1. Hey Michael,

      Thanks! “JitterScript” uses an Excel-like syntax for logic. The If(), And(), and Or() are all actually functions that take arguments separated by comments (you can check the docs on it). So your example : If(Or(And(a == 1, b == 1), And(a == 1, b == 3), c = 3, c = 4); That’s off the top of my head, so it may be off a bit.

      Jitterbit doesn’t let you define parameters for scripts, but since all variables are global, you just need to decide which variables will be be inputs and outputs for a script, then use them in the transformation. So your script called “Rewrite $accountName to $accountBrand” might expect the $acccountName to be set in the transformation, then it’d do whatever work is required before setting the $accountBrand output variable.

      Best of luck.

  4. Hello…I am currently working on a Jitterbit project, have used Talend and SSIS before, so this article was a great find. Thanks for sharing.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s