Isn't It Great When We Integrate?

OREN BOURNE: Welcome, everyone. My name is Oren Bourne. I'm the CAD and PLM administrator at Greenpoint Technologies. Up here with me today is Ravi Dharmalingam, a solution architect for Autodesk.

Just a quick show of hands, how many people here are currently using PLM-- or Fusion Lifecycle, excuse me? OK. Jitterbit Harmony? OK? Anyone here comfortable with the C-Sharp and SQL Server? OK, cool.

So this class is going to be very dense. It's a very technically-oriented class. It's going to be a rapid fire, kind of coding type class. Are you guys OK with that? Does that sound just right up your alley?

If you aren't terribly comfortable with that, let me make you feel more comfortable by telling you a story. So you with me? It's kind of a parable.

So once upon a time-- I think all good stories start with once upon a time. Once upon a time, there were a bunch of people walking through the wilderness. And they encountered a river, a raging, raging torrent, just screaming down through the riverbank.

They had to cross the river. One person finally was brave enough to take a step. He stepped forward, kerplunk, right into the river. He got washed downstream. They got him out. This isn't a bad story. Nobody died or anything like that.

So then finally one person said, hmm, look at the river, and said, I know what to do. And they walked across the water all the way to the other side. Everyone was amazed. My goodness! What did you do? How did you walk across the water?

The person simply said, I didn't walk on the water, I simply stepped on the rocks. I know where they are. So in this class, I'm going to show you where the rocks are for this type of integration.

So as the title says, isn't great when we integrate? We're talking about real-time systems integration in-- [CLEARS THROAT] excuse me. It's a real-time systems integration with Fusion Lifecycle with an on-premise SQL Server database and console application using Jitterbit harmony. So at the end of this class, you should be able to use JavaScript to send a JSON packet to Jitterbit Harmony API, Use Jitterbit Studio to receive and transform data, insert data into an on-premise SQL Server database, and pass arguments to a C-Sharp application.

So before we get going, I am obliged to-- whoops, that's the wrong one-- I'm obliged to give you a disclaimer. Greenpoint Technologies is not associated with Autodesk. We're actually part of Zodiac corporation.

And as such, Greenpoint Technologies, are Zodiac Aerospace company, is providing this information for educational purposes only. The presentation of the following information is to facilitate the free exchange of ideas amongst industry peers. This class and its content should not be viewed as an endorsement of any product or company.

With that said, let me tell you what Greenpoint Technologies does do. We do this. We take Boeing wide-body jets and make them incredibly beautiful. We turn them essentially into flying palaces.

This is an example of the work that we do. As you can see, it's pretty nice. Who here would not want to fly in this jet? I'm pretty sure everybody would, pretty nice stuff.

Currently, the 787s are the Boeing VIP aircraft of choice. And this is another example of the work that we do. This is a design concept done by our design department. This one actually won a few awards. Our client base is very wealthy individuals, heads of state, royalty, sometimes all three in one.

But we also do commercial work. We recently did an airplane for a company called Crystal AirCruises. They are a luxury cruise line company that wanted to take their experience into the sky. So we outfitted a 777 for them. As you can see, a pretty nice airplane-- a wet bar, seating area. Here's the lavs, the dining experience.

Here's another view from forward looking aft. it's a first-class aircraft. It seats up to 88 people. All seats are first class. Here's what you can experience if you fly in this jet.

So why am I showing this to you? Well, it's cool stuff. I like showing off what our company does and what I'm involved in.

But at the same time, I want to show you that the products that people make, and that we make in particular, are very, very intensive. They have many, many hours involved, of activity involved. A typical design like this will have anywhere from 100,000 engineering hours alone just to design it.

So we typically have anywhere from two to four projects going on a year simultaneously. [CLEARS THROAT] Excuse me. So that represents a lot of data. We use Fusion Lifecycle to manage the engineering process.

But we're vertically integrated. So we have many, many systems to manage the manufacturing process, the certification process. We have an ERP system. We use SharePoint. And the list goes on.

Those systems worked well enough on their own independently. But they are more effective when we can integrate those systems. So we have data from 100,000 engineering hours of work that we try to integrate with other systems.

So, how do we do it? We have in architecture that we follow where we have Fusion Lifecycle in the cloud. Whoops! Actually, I got out of sync of it. Got out of sync just a little bit.

So what are some of the ways in which you might want to integrate your systems? There are many different ways that you can follow to integrate the systems. The way that we're looking at today is a real-time systems integration. So when something happens inside of Fusion Lifecycle on an item-by-item basis, you're going to send that data to an on-premise system, a SQL Server or a console application, and do something.

So what might that-- whoops-- what might that be? You might want to send data on-premise to manipulate at a PDF file. You might want to send email.

Maybe you don't like the email server that Fusion Lifecycle has. You can transmit the data internally and send it from your internal SMTP server. You might want to interact with a web service. You might have a web service that you want to post data to.

Or you might want to put it into a SQL database. Once you get that data into a database, you can report against it. If you do it right, you can get a quasi real-time reporting system that when something happens in Fusion, that data is immediately available. And you can report on it immediately. You could also use it as source data. Maybe that data drives other internal applications.

So there's lots of reasons and ways that-- or I should say, there's lots of reasons why you want to move that data internally in real time. I'm sure if you think about it, from the perspective of your company, is you probably have a lot of ideas that you probably already have. That's probably why you're in this class.

So what's the architecture? How do we do that? How do we accomplished this really cool integration?

Well, this is the architecture we follow. I think this will probably work for you. We've been really successful with it. So you have to use Fusion Lifecycle in the cloud sends data to the Jitterbit Harmony API. Who here is familiar with Jitterbit Harmony? OK.

Jitterbit Harmony is a-- without going into a lot of detail, it's essentially a website that sits there. And it listens for traffic. It gets traffic from Fusion Lifecycle. Fusion Lifecycle passes data to the Jitterbit Harmony API. And the Jitterbit Harmony API says, excuse me, I have data.

Then Jitterbit Studio, which is behind your firewall, is constantly pinging the Jitterbit Harmony API. Do you have traffic? Do you have traffic? Do you have traffic?

And finally, it says, yeah, I have traffic. The Jitterbit Studio then says, give me the package of data you just received. I'm going to process that and then do something with it.

This is in the cloud. This is in the cloud. This is on-premise. Make sense?

This part right here is Jitterbit Studio. This your dev environment. This is how you create the code or the recipe for this to follow.

You pull data down. You build the recipe. You post the data up.

When Fusion Lifecycle hits this with some information, the recipe kicks in. And then it passes the information off to Jitterbit. And things happen. That's what we're talking about is this entire process that you see right here. With this process you can, set up a real-time systems integration.

So how do we do that? Four steps. If you do these four things that I'm going to explain-- I just felt like I was selling you steak knives or something. If you do these four things, that you will be able to do an integration like this.

So you get a triggering event. You need something to trigger the script inside of Fusion Lifecycle-- on edit script, on create, workflow transition, or a push-button script. You could even hook it up to a precondition script. I don't recommend it. But there might be a reason you might want to do that. The script will aggregate the Fusion Lifecycle data and send it to your Jitterbit API.

You'll receive the data in the Jitterbit API. And you'll transform it. It might be a simple or complex transformation. It depends upon what your needs are. And then you'll use the data to run external systems. Four things, if you do these four things, you will have a real-time systems integration.

So how do we do that? So I have a lot to cover. And I have, ooh, not a lot of time to cover it in. So, it's going to be dense. And I'm just going to go for it. I'm not going to overwhelm you. But we're going to step right through this very deliberately.

So first thing, can you see that OK? I wasn't sure if it would show up. Can you guys see that well enough?

So in Jitterbit Studio, you're going to create a project. You're going to fill out a form. You're going to choose the environment you want it to go to. If you only have one environment, that's OK. If you have two environments, you just got to pick the right one.

You fill out some information. You choose a new operation. You choose a transformation type. And then you're going to have a blank project.

Give it a name, a simple name, something that's easy to identify. I once made the mistake of giving it a fancy name. I just really confused myself downstream.

So there's your first rock. Don't give it a fancy name. Give it something really simple and easy to use.

Then you're going to go, you're going to save that. And you're going to post it. So at this point right here in the process, we are on a laptop or a computer, something inside of your firewall. You've created this project. And then you push this project out to the Jitterbit Harmony API. It's just a blank project. We're going to fill us in a little bit.

Just to be clear, at the end of the lecture part, I'll give you a demonstration. I'll show you all the steps involved in this.

So we create a project. You push it out to the API. Now we go to the web. We log in we go to API Management, Custom API. And we're going to create a custom API.

There's just a button in the upper right-hand corner. You just say New. I want to make a new API. So this is a URL. You're going to build a URL and establish some functions behind it, really straightforward.

You're going get a form, a very simple form. You choose the environment. That's the environment that you just posted your project to.

Give it a name. This name is whatever you want it to be. This name right here will become part of your URL. So you can give it something meaningful if it helps you. Or you could just give it a bunch of random characters. It doesn't really matter.

When you set up this part right here, your operations, you want to choose the post type. Post is simple enough. That's all that you need.

You could get fancy if you wanted to get into the gets, or whatever else you want to get into, you can do that. But if you stick the post, it will most definitely work. So there's your second rock. Make sure that you do it this way.

The project, that's the name of the project that we just created a moment ago, that same with the operation, that simple name that I was emphasizing that you choose that we just chose a moment ago. So you just walk down the hierarchy until you find that operation that you want to be the start the kernel, the central starting point when this project kicks off.

And no response. This is going to be really simple set up. We don't need to respond. So we don't need to tell Fusion Lifecycle that there's anything that it has to react to. It's not going to get a response. Choose "no response."

I usually clear out all of these. You can have them on if you want to. I clear all the checkboxes out.

But turn on debugging. You're going to need debugging. That will allow you to see the traffic that comes into the Jitterbit Harmony API when the traffic arrives. And give it the time enough you want, and then choose Update.

Once you've done that, it'll present to you a URL. This is the URL that you will pass data to from Fusion Lifecycle. That's it. Now we finally get to Fusion Lifecycle.

So in Fusion Lifecycle, it's kind of an approach that you want to take. And it's going to be scripting. You want to aggregate all of your data that's important to you.

You might have fields in your item details. You might want to grab the milestones array. You might want to look at who knows what, workflow actions, workflow history.

Through your scripting, you need to aggregate all the things that are important to you for this particular item. Remember, this type of integration we're talking about is on an item-by-item basis. So after you use script, that's after you consider the data that you want to collect, you map that over to your script.

So here, I am pulling this field, the item dot item number. That has the real name behind this. This is a user-friendly name. So don't let that confuse you. This name is what the user sees.

Behind that is the actual dedicated name to this. That's the name that you want to be pulling the values from. So item dot item number, I assign it to a variable item number. So this is the JavaScript right here.

You could use this directly in line with other areas that we're going to talk about in a little bit. I don't recommend it. It's not good programming practice. It makes it more difficult for you to manage and extend this code. Assign it to a variable. Do the same thing here, same thing here, same thing here.

Now, as you're doing your scripting, you're going to need to consider special cases. Special cases might be an array. If you're accessing data that is actually an array, what are you going to do with it? Maybe you want to process it, or maybe you don't. But you should make a decision.

Are you accessing data that's a linking pick list? If you're accessing data and linking pick-- so you guys familiar with a linking pick list and what that is? So if you're accessing data that's a linking pick list, you'll have to walk through here, item dot item number dot, dot, and so forth until you get to that data.

So we've talked about arrays, linking pick list-- oh, null values. This data right here-- whoa-- this data right here is currently filled in, but maybe it won't be. If you allow blanks inside of your forms, that's effectively a null value.

If you assign that, say this was blank, item number was blank, for whatever reason. Maybe that's part of your process. And it's OK. If I assign that to this variable, this variable will have a null value. There'll be nothing inside of it. That's OK if that's OK with your process.

However, I recommend that you transform that data, wrap it in an if statement that says, if null, then this equals minus one. The reason being that when you pass that blank data, and you receive it on the other end, and you try to debug, you're going to look at this blank data and go, OK, did the data not get sent over? Or was it actually blank inside of Fusion Lifecycle? If you transform it and send something, negative one, or whatever your null transformation is-- excuse me-- your null transformation is, you know positively, OK, I got the data. It just happens to be blank in Fusion Lifecycle. Does that makes sense? I really recommend that you do that.

Another special case in this particular example, this is from the all users pick list right here. The all users pick list has the name of every single person that you have assigned a license in Fusion Lifecycle. When I assign the value of an array-- so this is an array type. If I assign the value of an array to a variable, it takes all the contents of that array and just turns it into a big string

If you're OK with that, then fine, you're done. But if you're not OK with that, you're going to need to do something special. And I'll give you an example of that when we get to the demo portion.

So arrays are special. Sometimes they're fine. Sometimes you're going to have to iterate through an array and massage the data until you get it the way you want it to be.

So once you've done your coding inside of Fusion Lifecycle, you're then ready to call your Jitterbit Harmony API. I recommend-- let me read this up here. Build the request and then send it. OK, great Bullets

I recommend-- this is another rock for you-- I recommend that you set this up as a function call. Don't put it in line in your script. That's not good coding practice. And it'll be difficult for you to troubleshoot and to extend as you start using the script over and over again. Put it into a library script, and then reference that library script into your action script or whatever it is that you're using. That's the better way to do it.

So you establish the arguments that you're going to receive. Then in this method, I'm going to choose a variable and assign it the Jitterbit Harmony API. That's the API that we just made a moment ago.

Just assign it to this as a variable. Don't write it in line inside of this guy right here. It just makes it more difficult. And it's tough to maintain. You might even want to consider putting this variable into its own separate library that's a library of all of the variables, the global variables that use for your tenant. But that's up to you.

In this case right here, I have in an if statement. This is purely optional. You don't have to do this. I do this because this if statement allows me to control the behavior of this right here when I'm debugging it.

It's just a switch right here. That's just a variable. I assign it either true or false. And I wrap it in an if statement. That allows me to turn this script on and off when I'm testing and debugging. Because when I'm testing the script, I might be testing this stuff over here, all of these variables here, into a print line.

I don't want to be jamming my API with a bunch of data, data, jamming, jamming, jamming while I'm testing the script. So I just set a little switch right here, set it to true or false. And that activates or deactivates this guy right here. Of course, you want to make sure you set it the right way when you go to production. That's purely optional.

Then right here-- I'm getting ready to get to the core of this. I create a variable that's going to handle my request. You can assign any name you want.

There's nothing special about that variable name there at all. That's just what we use. I don't know why we use that variable. But that's what we use at Greenpoint.

Then this is the standard JavaScript constructor, XML, HTTP request. If you're using JavaScript, this constructor is already there available for you. You simply call that, and you're going to create your XML, HTTP request object.

Then we're going to fill in the different properties, so the open clause. Remember when we are creating the API, we chose the post option. It has to align here. This has to be post. This has to align with the way you configured your API.

Here's the URL. That just this guy right up here. The API URL, just write it into here.

Then we have a true clause. True tells this package-- or I should say, true tells JavaScript that we are sending this asynchronously, meaning that we're not going to get a response back. We're not going to wait for a response. So you remember when we set up that API, we chose the no response type? This true says, yes, I am not going to expect a response.

So this part right-- oh, sorry, the next part, the set request header, you just fill it out that way. That's all there is to say about that. So these three parts here, the creation of the object, the dot open-- these four parts, no, these three parts, excuse me-- the dot open and the dot set request header, you just do it that way. You just follow that pattern every single time. And it'll work out.

Now the next part, the dot send, that's where you put in your special sauce, your secret recipe for how you want your data constructed. So with the dot send clause, I have a method called JSON dot stringify. That's another built-in JavaScript method that takes whatever is in the body of this and turns it into a JSON structured data. That's it.

Then you fill it out. I'm going to give it a title, give a value. I'm going to give it a name. I should say, give it a value, give it a name, give it a value, and so on and so forth. Now here I am at an array type again. And then I assign approvers here.

If this is an array type, even if you explicitly declare it as an array type variable in your scripting, and you pass it as an array, when you put it in the stringify, it will be turned into a big chunk of text. That's what stringify means. It's going to string all of these values into text.

Even if these are numbers, even if these are integers, these are Booleans, or whatever it is, when it hits JSON dot stringify, it is text. That's it. There's no other data type that will be sent. And then as the script runs, it sends it. Does this point make sense? Is it clear? OK, excellent.

So at that point we're done with Fusion. And now we go to back to Jitterbit Harmony. So remember, we're in the cloud now. We're on a website.

We're going to go to Menu, API Analytics, the Debug Logs. Remember when we constructed that API, we activated the debug logs? The debug logs allow us to actually see that traffic that gets passed in. That is the JSON body that will be passed in.

It's really short here in my example. When you guys do this-- excuse me-- when you do this, it may be huge. That's OK.

We have some JSON packages that we send there are a really big. And that's totally fine. The system can handle it. So if you want to send a lot of data, It is OK to do that.

But I want to copy that. I want to go to the debug logs, and I'm going to highlight that, and I'm going to copy it. And I'm going to save it to a text-- oops. I keep doing that. I'm not used to this clicker thing.

I'm going to send it-- excuse me, I'm going to save it as a text file. So in the text file, you just give it a name. And if you choose on the filter down below all file types, you can explicitly type in dot J-S-O-N extension onto the end of it. You want to save that in a place that is accessible because you'll be referencing this file in the next step.

Are you guys with me so far? How's the pace? It sounds OK? Everything's OK? You guys are getting your nerd on? You feel-- OK.

So now we go back to Jitterbit Studio. So now you're back on your laptop, essentially, or your development environment. And you're inside your firewall operating Jitterbit Studio. You're going to go back to your project.

So now in your project, you have to start telling this what to do. You're going to define the recipe here. So the first thing you need to do is define your source.

This project is empty. It has no direction. It has no idea of what to do. You're going to tell it, OK, you're going to find a source. The source is actually going to be the API request that you're responding to.

It's really simple. You simply right mouse click. You say "use API request," and you're done. That now means that that JSON body that you saw just a moment ago that we copied and saved will be passed to this guy. And it will use that from that point forward, really straight forward.

There's another rock right there. This is a nice-- thank Robbie for this one. This is a really cool tool that he pointed out to me that makes it really, really simple.

So now that I have stuff, I have data, I need to do something with that data. So now we're going to talk about the transformation. So let me slow down-- let me see how much time do we have. Ooh, OK, we're doing OK on time. So now I need to talk about the transformation.

So Jitterbit-- anyone here familiar with data warehousing at all? OK, familiar, anyone else? So I will assume that most people here are not familiar with data warehousing. In data warehousing, essentially, you're taking data from one place and putting it into another place.

But you're doing stuff to it in the process. You take the data, which is usually really messy and dirty, and you have to transform it. You have to cast it from an integer to a string, or a string to an integer. You need to process and massage the data so that it conforms to your data warehouse. That's a data transformation. We need to do the same thing here.

Now for a Fusion Lifecycle, it's not as complicated as working with really messy data because you guys own the whole process end to end. So you can make sure the data coming in is good. But we still need to do some transforms. At a minimum, we'll probably need to take all of those integers that got transformed into strings and cast them back as integers.

You remember that step when we were setting the body? Let me go back because it's important. Here we go.

This stage right here, where we are actually turning everything into strings-- I am not used to this thing-- turning it all into strings, if that were an integer, it's now a string, I need to transform it back to an integer. That's what the transformation is for, is being able to set things back the way you want them to be. So the best way to explain this is to simply show you.

So we go to the transformation, I need to define it. So the first thing I'm going to do is double-click on the transformation. I want to set the source. Excuse me, I'm going to create a new transformation. This is the wizard now. When you double-click on that transformation, and you start to define it, you just get a wizard.

So I create a new transformation, I'm just plowing through the wizard. And I set it up this way. Source is JSON, obviously. That's what we just told it a moment ago.

So you're mirroring that information here. And your target is going to be text. Without any further explanation, that's just what you do. You send it as a text type target. It just works.

And then there's a little radio button at the bottom of the wizard. And you'll see this during the demo, a little radio button that you just say, yeah, I'm going to go ahead and define this as a JSON structure. I don't know why, but you just do that. And it works. You click on Next. Then you need to train Jitterbit in how to understand the data it's about to receive.

So Jitterbit, it doesn't know anything. It doesn't know anything at all other than what you tell it. So all it knows right now that I have this JSON thing, this request that I'm going to receive. You told me it's going to be JSON.

But what is it? Is it one thing of text? Is it 10? Is it 50? Is it 100? What does that data structure look like. You need to teach Jitterbit what that data structure will look like.

So that's what we do next. We're going to find JSON file that we saved. We have a wizard. It's the same wizard. And then it suddenly takes-- it turns into a bunch of numbers. And you just follow those steps.

We're going to navigate out to the JSON file. Remember where we copied the body of the JSON file, and we save that to an accessible location? We go find that file.

And we tell Jitterbit, this file contains the structure of the data you're going to receive. And then you just click Generate because that's what you do. And then it generates what's called an XSD file. That XSD file is essentially that text file converted into something that Jitterbit can understand from that point forward.

It's like an import, if you will. For you guys with CAD backgrounds, it's like importing data from another system and turning it into your native data. That's the analogy here. Again, we're still in the wizard. We're just clicking through this wizard.

I am now going to define the right-hand side of the transformation. So in the transformation, data comes in on the left. That's the JSON body. And I just define the data on the left.

Now, I'm going to define the data on the right. I'm going from some data structure somewhere, and I'm going to another data structure somewhere else. So it's the right-hand side of the transformation.

I'm going to go ahead and create new from a dropdown. Oh yes, before we go into this, let me explain something. Data structures, you have a choice to make here. Either you're going to structure your data as a simple type data structure or a complex type data structure.

Now, this is a Jitterbit construct. This is a Jitterbit idea. But, of course, we have to work within the concept of Jitterbit. A simple type data structure essentially is flat data. It is just text, text, text, text, text, text, text, simple.

Complex data structure says, I have text. I'm going to get an array. I'm going to have some more text. I want to have another array. That's a complex data structure.

So, the takeaway from here is simple means it's flat data. Structured or complex data means that you have an array that you're passing. There's another rock for you. That's the one that you need to be aware of.

How am I going to structure this data? I going to show you both. I'm going to show you the simple data structure, then I'll show you the complex data structure.

So for a simple data structure, you simply choose "Create New File," give it a name. And this simple text document is selected for you by default. That makes it really easy. You don't have to select anything. Then you just choose "Create Manually." You just got to click "Create Manually." I keep pushing the laser button when I mean to advance forward.

Then you're going to get a little editor, and you just say New. These are the field names. Remember the field names, the variables that we saw in Fusion Lifecycle? Basically you're just going to put-- I should say the names that we saw in the JSON body that we constructed, you basically put the names here, item number, whatever, data, whatever it is, it's all flat data.

We're not going to do anything more with it. We're not going to-- it's just flat data. And you're done. You'll get a flat data structure.

But let's say that we have a complex data structure, which we will in the demo. We're going to have complex data structure. We have an array that passing. We want to respect the fact that it is an array. We're going to do a complex target.

So in that same wizard, we would have done this instead. We would choose "Create New," "Complex Text Document," not that big of a deal. Click "Create Manually." We get a very similar looking editor, but it is different with one key difference right here. This occurrence right here, this is the magic. I'll speak about that in just a moment.

In a similar process, we're going to choose "New." We're going to give it the name of the data structure. This is not the data values that we're going to receive. This is the name of a structure. We're just giving a name to a structure.

We're going to establish the segment root. There's some nuance here which segment roots and the segment apparent. Just choose underscore root for now. As you guys get more comfortable with this process, you can get fancy. But just choose underscore root, and it will work.

Here I set to only once. That means that data gets transform only one single time. In Jitterbit world, that means it's flat data. It's simple data. Then I go and I detail out-- oh, sorry-- and then it gives me another-- sorry-- I click on "Fields," and then it gives me the editor for, OK, what is your flat data structure-- flat data, flat data, flat data, flat data.

Then I move on to the complex data. So I give it a name. Again, so click on "New," give it a name approvers. Again, that's just the name of the data structure itself. I'm just giving this thing I'm about to define a name.

Again, I keep doing that. Choose "Root." Just do it that way because it'll work out well for you. You get fancy later on.

And then here's the secret sauce, Zero or More. That says that I'm going to transform this data zero or more times. That means there's more meaning there is an array there. I'm going to cycle through the elements of the array as I transform the data. That's the magic.

Then I give it a name. In this case, I'm just going to give a one variable. You could put more variables here if you want.

Don't go there right now. We don't want to have multiple data structures. We don't want to have multi-dimensional arrays that we deal with. We're just going to work on a single dimensional array.

And then I choose OK. And I get a data structure that looks like this. Notice that it's a little bit different here, flat data structure only once. Zero or More is my array that I'm going to pass. That's a complex data structure. If you follow this process when you receive arrays, this absolutely will work for you.

How are we doing on time? All right, we're doing OK. We might have to step up the pace just a little bit. We'll see how it goes.

This is where I've been defining-- that whole wizard that we just went through, this is where I define it. This is the left-- I keep doing that. This is the left-hand side. That JSON file that we imported, Jitterbit, when it created the XSD file, it said, oh, I see exactly your data structure. Here's the structure of the data that's arriving-- boom, done for you.

So the whole complex thing has been figured out on its own. You don't have to deal with that. Over here is where I said, OK, I have to manually tell Jitterbit exactly what my structure is going to be like.

So then I map the data. I say item number-- oh-- item number goes to item numbers. Title goes to title. This is why I encourage you to work with really simple names. You don't want to get, item number when it's ready to go one, two, three, four, just really simple. Just keep it really direct. You just want it to be obvious as to what it is you need to do next.

That's another rock. I made the mistake of getting fancy, trying to be all cool coding guy. And it was dumb. Don't do that. Make it really simple.

So I mapped the drag and drop. That's all that you do. You drag and drop.

Then I need to define my transformation. Remember when I saying that-- excuse me, well, I needed to define some more. So I click on these little blue guys right here, these little blue folders right here. They won't have the f of x at the time that you click on them. They'll just be blank. And I give it a variable name.

This represents on the right-hand side-- well, this will be presented to you by default. This will not be there when you first define it. This represents the value of the JSON body that you receive. Item number string, this represents that value. That holds that data in it. I want to assign that to a variable because it's that variable that I will reference when I start to call my stored procedures or the console application. Assign it a variable.

At this point if you want, this is where you cast this back. This is a string right now. If I wanted this to be an int, I could cast it back to an int or do a conversion to make it an int. That would implicitly make this an int from that point forward.

Also, so I didn't say this earlier, you would want to say that when you're defining this flat data structure or this other one here, the complex structure, you would say whether it's a string, or an int, or a whatnot. Everything has to be in alignment. Does that make sense?

Now, this is the flat data structure. The complex data structures a little bit different. Now, this is getting into an area of how do you want your hamburger cooked? I can't tell you what's right for you, but I can definitely tell you what works. There's another rock that you can step on.

This is an array. But because this is a complex data structure, it's going to cycle over the array. At the very first instance this JSON dollar sign approvers, dollar sign item, is the first element of that array. If I did nothing at all, and I just hit a dollar sign approvers equals this right here, it would cycle through it. And at the very end, this would only hold the value of the very last element of that array.

I needed a way to take that array and turn it back into an array inside of Jitterbit land. So this is a little trick. So when you receive the array, it's going to be over here. It actually will be indexed as number one.

But as you probably know or may know, array start at index zero. I don't know why. That's just how they do it.

So I define dollar sign approvers. At the very first go, dollar sign approvers has no data in it. So the length of no data is zero. And I set this variable equal to zero.

Then I set this approvers array element 0 equals the first element. Then it cycles through. And automatically, internally on its own, it cycles through the next element. So array element 2, approvers now has a one value in it, so its length is one, count equals one, approvers 1 equals the second element, and so on, and so on, and so on.

So I just turn this into an array. This is now an array. Does that makes sense? That's the rock. This is a really good rock to step on.

Finally, we get to the end. We're going to go ahead and do some stuff, some really cool stuff. So I go back to my project, and I delete this target. Let me check the time.

So I delete this target because you don't need it. I'm going to select "Transform," right mouse click, and put a script at the end. It's the script that's actually going to house the code that's going to run the stored procedure in the SQL-- the stored procedure in the console application.

We'll break right there because I know I'm going to run something on a database. I need to establish a database connection. Who here has made a data connection before? OK.

So, a data connection is a way that you tell the system how to connect to the database. I need to establish that upfront. Well, you don't have to, but it's a lot easier if you establish it up front.

So I move away from the project. I go to the left-hand side. I go to targets. Seems weird that we just got rid of a target, but it works out.

Right mouse click, you choose "Target," you're going to choose "Database Type." And you're going to get a little editor here. Here, you just build your connection and information.

Right now, this is configured for a SQL Server database. If you click on the Select button, you'll see the drivers for many different other types of databases. You can choose whatever you need. You just go ahead and fill this out, pretty straightforward stuff.

If you're using SQL Server, here's another rock for you, this is the SA log in, the service account log in. So if you log into SQL Server using this method, you'll need the provision an SA account in order for you to be able to connect to it. It won't use integrated security.

And correct me if I'm wrong on this one, Robbie, when you go to the options, you're able to stipulate a connection string if you prefer do that. And I believe at that time, you can use integrated security. Double check me on that.

What this is leading up to, by the way, is that the Jitterbit agent, so when you get into this a little bit more and you learn more about how the technologies work, this whole process is going to be running under a machine account, your local machine account on the machine that actually is hosting this service. The local machine account needs to be able to access the various areas of your network for this to work. It is probably the case that your local machine account will not because administrators lock that account down. So you may need to change the account that all of these processes run under to a service account that has the ability to traverse your network. Very nerdy stuff for you there, but when you review the video afterwards, just listen to it a few times, and you'll get it.