[Music] in order to subscribe to my channel please click here or click here please share comment and like my videos and channel hey guys this is god of welcome to SAS word service now today's session is about import sets in ServiceNow if you are working as a developer or administrator you might have got so many requirements for migration of data from another systems into ServiceNow like knowledge management where your organization was using legacy knowledge management platform then they wanted to migrate to ServiceNow system all you want to upload some data in different tables in ServiceNow like users request and incident ServiceNow has an application called system import sets which helps admins to import data to ServiceNow which is aligned with the fields of ServiceNow table let's start with key terms we have in import sets the first term we have is import set table import set table is a table that acts as a staging location for all the records imported from a data source before data is transformed in to ServiceNow fields on these tables are generated automatically based on imported data and should not be modified manually so it's kind of a staging table a pre table before the target table so when you import some data it keeps that data into first table that is the staging table and that's called import set table then we have data source data source is a racket that defines where to get the data to import a data source may point to a file a JDBC compatible database or an LDAP organizational unit next term we have is transformation the conversion of data from an import set table to another table according to the rules defined in a transform map so when you have a staging table and you have and did a target table the kind of composition they have so that they can transform the data whatever logic you put in to transform map but what is transform Maps the next term we have is transform map transform map is a set of field maps that define the relationships between fields in an import set and fields on a target table such as incident during transformation data is copied from import set table to the destination table based on the transform map you mention or create the last term we have is foreign racket insert a foreign racket insert occurs where an import makes a change to a table that is not the target table for that import that means I will give you a quick example you have incident table and you are going to push some data into incident table but on incident table you also have some reference fields like collar it might happen that collar is not available in your system but with the help of import you are able to create a collar and that means a user directly in the users table so you're not even hitting users table as part of this migration this import but you are creating so that's that kind of insert is called foreign record insert what are import sets import sets allow administrators to import data from various data sources and then map that data into ServiceNow tables the import sets table act as a staging area for records imported from a data source so imports is a feature of functionality of service now so that you can import data from different external system or different data sources and imports a table is a specific staging table it's kind of a table which is basically created pre transformation so you are creating the table and whatever data you have from external system is being put over there and then ask for your logic as for the transform map so that you map the correct Fleitz video destination table and then you import the data into the right table or the destination table you have then we have why we need import sets we need import sets to import some data into ServiceNow so let's see we have this incident table and you can see the ServiceNow system and you you have some data externally maybe that data is in your external system maybe it's like a file or or any in any other data sources we have if that data is being imported or we have to import that data into ServiceNow then we need this import set functionality supported file formats now to import the data in ServiceNow it's also really important that you understand what are different file formats are supported by ServiceNow so the file which are supported file format is CSV Excel XML and then JSON so these are the four file formats which are supported by ServiceNow to import the data into ServiceNow then we have data sources now what are the different data sources you can utilize to import data into ServiceNow first is file and we already talked about the supported formats then we have JDBC that means ServiceNow also allows you to create JDBC connection then you can import the data with the help of JDBC as well and then we have LDAP so if you have LDAP in your organization idly every organization does have this LDAP sin that case you can use LDAP as well mostly if I talk about why LDAP mostly organizations they use the import users and groups that's what they do then indirectly import into service now transform Maps so as of now we learnt about what exactly service now can support what are the different data sources files you can import into service now now let's learn about transform mass maps which plays really important role in import or whatever data you import into service now this really I think plays an important role transform map is basically a track of field maps that means you are mapping the fields between your destination table and the import set table that means the data or the fields which you are getting from external system or external file then you map that fields so that all the fields which you have external in external data you can map them and then that field or that data of that field will be pushed into the same field in ServiceNow whatever table you have it will automatically post if you will create these transform Maps let me just explain a little bit more in detail so you have this ServiceNow target table you can see we have incident table user table group table location table and I have an external system as well so you can see I have external data it can be in XLS that means excel sheet XML or maybe via JDBC connection so this is external data source I have now this data is basically imported into ServiceNow so first it gets imported into import sets table as you can see but once it gets into import set table that data goes into import set table then it also gets trance formed that means whatever data you have externally first it will then transform and then after that transformation it goes into ServiceNow target table so once you have done the mapping that means you have a field one field2 field3 an incident table you have caller may be examples short description or maybe description in transform map you have to map because ServiceNow doesn't know which which feel is for a short description which feel is for a description which feel is for caller so in that case you have to map maybe field to is for caller maybe field one is for short description in the external system or external file or whatever data source you are putting so ServiceNow is not aware of that so that's the reason they have given this functionality of transform map so that you can map the fields and that data will be transformed into ServiceNow destination table accordingly import sets application so as I mentioned in order to control in order to manage these imports ServiceNow has this application that is in system import sets let's take a look in my personal developer instance this is my personal developer instance in order to go to import sets you have to type in application navigator system import sets so this is the application I have where you have all these modules I will talk about these modules quickly so we have load data now here whatever data you have you can load it right from here maybe with the help of file so you have to create a new table or if you have an existing table you have already created the table then you can put it select this option you also have source of import whether you want to upload this data with the help of file or you already have a data source and you will find some existing data source as well then we have create transform map now as you know once you upload the data into that staging table that is an import set table then you have to transform that data so that it can map the fields that's something you will create right here and then we have run after mapping the fields so if you are done you have created the table you have mapped the fields and then you have to run that transform that means data will be transformed into your destination table then we have one administration tab that is data sources this is what I was talking about records where it mentions different sources you have we can import the data now out of the box you will also see some sample data sources as you can see some example LDAP groups CSV FTP ad as your Google example okhta example so that means if you want to import data from these data sources as well you can do that it is just showing you one example for different different data sources you have as you can see right here in choosing I think we won one more we have that is oh I DC so you can see we have file JDBC LDAP I think IDC was added later but but majorly I think if I talk about right now as I can see in my Madrid instance I still have Oh IDC so you can see you can say the overall data sources we have four I think if I quickly search for the or oh I D see I have to check what exactly this this Oh IDC because I think majorly we use file JDBC and LDAP then we have transform maps now what would transform Maps you create it it shows you in this table so in this module if you click that all the transformed maps you create you can see here that means all the mapping of the fields if I click just one of the transform map if I go at the bottom I will show you what exactly it shows so you can see we have these field maps so in user table we have lot of fields but we are just mapping these fields then we have schedule imports now you can also can use the imports that means service now if we have for example LDAP or any sources it will also schedule that means every day for example in LDAP now you want to import user data every day maybe in your organization now that is that will be scheduled imports because you can't just stick to the one time data right because you want every data every user data into your ServiceNow system so example LDAP a lab group and a user have to be like schedule you have to create this schedule so that is something you can do in ServiceNow as well because you already have that data source or service now we'll check that data source and again we'll import the data from from that particular system then we have some advanced option over here under advanced you will see we have import sets now whatever data is being imported into ServiceNow it becomes like a racket for that particular instance that one time racket basically which is being imported into ServiceNow that becomes a kind of import set racket then we have concurrent update sets now concurrent update sets that means this is basically used for any large sets of data you have but you have to remember that this kind of import sets also impact the performance of the system so because concurrent is is not I would say you have to monitor as well so this is only for if you have large sets of data then only you can use this concurrent concurrent import sets then you have concurrent import sets job now with the help of these jobs you can you can just run concurrent update sets import sets as well then we have progress now any any import which is running right now or has completed that is something you can see in this particular table then we have transform history now whatever data is being transformed you have got it got any errors any kind of any ignore update we will talk about it a little bit detail later then we have transform errors so while importing while transforming the data did we get any kind of error that is what you will see right here and then we have these important so this will show you the data which was imported that is something you can see here it will show you the logs so like you can see clean up clean import sets seven days old what exactly this is cleanup so that is you will find the next column next section we have that is import sets table here we have cleanup now this cleanup is something about that which data you have to clean because you you might have lot of import data now why would you keep data in your import table because we will keep on importing the data into your staging table that is your import table that you have to delete them for for for maybe after a particular time so that is what you need this cleanup so you can schedule the cleanup you can also run it accordingly and you can also schedule it if you want you can see it says you want to change the frequency that is what you can do directly from here let me show you the practical scenarios to import data into ServiceNow the first scenario we have is how to import an excel sheet file into an incident table so one of your customer is looking to import some data maybe 100 records into your incident table now I'm not going to import hundred incidents I will just show you an example that how you can import that and the requirement is that customer wants to insert new incidents how we would do that let's take a look this is my personal developer instant I will go to my incident table first so I would go here and maybe I will just keep one record here and I will try to export this in Excel because we want to import in excel format for now I have imported this one I will just open this sheet so you can see this data I will make it a little bit large so that you can see okay so you can see we have number opens short description collar priority state category assignment group assigned to updated updated by external now as of now these are the columns of your incident table but what happens when I'm not going to put that one in that case I'm not going to use any number I'm not going to use opened I will I even I will I won't keep this short description what I will do I will try to change this maybe I will make it title and here I will maybe keep maybe I will also delete this priority I can do now this has to be maybe I will put severity I don't know for now maybe because I'm just putting the data from external system and then I have title severity state here I can maybe I will put status and then I have category maybe I will remove this for now assignment group it has to be existing basically I will still remove this for now I will just keep title civility status or I will delete this one as well so we are just keeping these three columns for now maybe I will remove this one as well or maybe we will just keep description I think that is better for now so that you understand description and this is I will do this is a title one this is description one similarly I will copy this data here so that you understand so as of now we will just insert two racquets so this is title - and this is description - and I'm going to save this so I have saved this particular excel sheet now I will go back to my instance here now how exactly I can import this data so what I will do I will go to so but before that let's let's check incident table we will go here open you can see we have the latest one this one if I do update it so we have this one for now so what I will do I will go to system import sets so I'm going to system import sets and then I will go to load data here I will create a table because as of now I don't have any table for incident so I will click on create table and I will type a name and that labor name because it will automatically add I am PB that will be import set table so maybe I will type incident you can see it says you underscore incident source of import I will put for now file and then I have this choose a file so I will click on choose file it will go to my system directory and I will be able to select that file so this is the file which we just edited file is selected and I will click on now submit once you will click on submit it will say process - that means it has two rackets inserts - but now what what exactly you have to do as of now you have just imported the data into import set table but now you have to transform it you have to create the transform map so for that you will click on create transform map here you have to give the name so maybe I will incident and then I have you you might find other options so as of now I'm just I'm not doing anything I will just quickly give target table and target table is incident that is what we want our data over there do you want to enforce mandatory fields we will just keep it for no for now copy Mt no we don't have any empty field for now create new racket or on empty college fields I mean any field which is collies now what exactly this school is is that is something we will learn a little bit later so I will just click on first auto matching fields so what you will do you will click on auto map or matching fields but this won't be able to do that now you can see it says fields map create it but it only Maps this you description because it found that particular word description that's reason it is able to map it but that is not we have to do because we have one more column so he will go to mapping assist right now here now this is your source incident this is your target that means the data you have in your source table so here you have to find title you will click here title you'll pull the title from here so that is short description because we want to map it with short description I will pull short description here you can see I have description - description map title with description map as an example it will also show you the data right here see we have this is the description 1 and in short description you will definitely see where exactly this data would be but so it is showing you that fields of your target table and your source table you have that is your import set table if you will click here map fields it will show you that what fields are going to be mapped so that you can click here and then you can see now after mapping the fields you can click on save once you click on save your transform map will be created now as of now we are not doing any kind of scripting because you also have like you can see transform script we will learn little we will learn about it a little bit later as well once you are done with this then you have to click on transform because you have to now transform the data so in that case but before that you also need to make sure that you you have some some coalesce fields enabled is it really needed so you will see description no so short description description can't be coalesced now coalesce is something is like unique that means for example number or in user stable email address now these are the fields are unique it shouldn't be same that means even if you importing the data from external system you have to make sure that they are not no duplicates so in that case you have to keep those fields as colace so that your data is not getting duplicated so in that case I will click on transform once you will click on transform this will now transform the data into your target table you can see it is telling you this is the import sets run which we have and this is the map which we will run so this will available Maps it will show you all the transform Maps but it is already picking up the map which we have already created right now so now I will click on transform now this is transforming the data now that means it is transforming the data into your service now incident table that means your task is done off import so this is the import sets which was created so if I go to my module here and if you will see this imports s you if you remember this was not there earlier but now you have it so if I click here you will see the data is processed at the bottom you will see the logs that how exactly that run running happened so in that case you can see total records we have to what exactly it did did it update did it did it is skipped did we have any kind of errors so the answer is no it was able to insert two rackets and what those rows are here you can see we had two different rows for two columns here we have row zero row one inserted inserted and these are the target records which were automatically created in ServiceNow so how to check this just go to your incident table that was your target table click on open if you remember we had these two this is title 2 this is title 1 and I can also pull description in my column so I have a description right here maybe just after a short description I will click on OK so you will see we have this this is title 2 this is a description 2 that means you are able to import data successfully with the help of EXO's sheet the next scenario we have is how to import an XML file into users table so as of now you saw that we imported excel file but now we will import XML file into one of the user table we have in ServiceNow before that I will show you that XML file which I just created to show you this demo so that XML file we have is this one so you can see I have this user data and I have three different users in this XML file so let's say your client is providing this data it might be in hundreds or maybe in thousands but I am just showing you this demo so these are just three records for now so you have three different records and the fields we have the information we have is first name and last name for three different records so in that case what we will do will go through our instance and we will go to load data that's how we imported the data into ServiceNow for excel as well so let's start with create so first we will create a table maybe we will create table as user maybe we will say user import data so I'm creating the new table now Here I am selecting the source and I will upload the file our click here I have this file I will flip that and now I will click on submit and here I will click on submit oh so now you can see the error it says only files that can be uploaded a file in CSV or excel excel file in dot XLS or dot XLS X formats that means you cannot upload any other format directly into ServiceNow import table now how exactly we will import that data then the data which we have an XML format but you know we learnt about it that service now does support XML as well even JSON so how we will import this data then now this way what exactly you can do you have to use data sources so we learnt about data sources different data sources we have now one of the source we had file one so what you will do you will click on data sources here you will create a new data source so I'm going to click here and I will give it a name maybe it will be SAS demo XML I will give it a name may be SAS where it's a label so I will do SAS xml demo name you can see it automatically selected here we will select file but what format now format we have to put XML then we have this file retrieval method that how this file will be retrieved now this file can be with different ways like FTP stdp SCP SFTP but as of now because we don't have organizations infrastructure here so that I can I can select these examples so I will select as of now attachment which is a default one now for attachment when you select attachment it has to have an attachment then so in that case I will attach the same file which I have so I will select this XML demo so now you can see it will not throw any kind of error so it has attached that file now this data source is created but you still need to do one more thing because you have selected XML so it is adding one more information one more field to you so that you can put that information that is XPath now in order to learn about XPath if you're not aware of it please go on google and try to search for it and you will understand but I will still tell you what exactly ESPA XPath is now XPath is something the nodes the nodes which you have in your XML data so for example I will go to my XML data again now you can see we have nodes like user data then we have user now it might happen that you might have a large XML file but it might happen that you don't need every rack head or every node information so in that case whatever node information you need you can just select it how to select it I will show as part of this example so we have this user data under that we have this node user so I will go back to my instance here I will do slash and I will mention user data our first node and then I will go to our second node that is user that's it you are done do we need to expand No do we need to zip no nothing I will just click on save you have done you have created this data source now how we will upload it then in our user table so in that case I will go again to load data if you remember it it threw an error but now we will create that table again so its user import and now I will do source of import should be selected data source because file it was not allowing to do that so we will select data source it will automatically give you all this data source we have but we have to select this one SAS demo XML and now I will click on submit when I will click on submit you can see it is able to process it it also found three records you can see it inserted three records in this staging table in the import set table now we have to create the transform map now so I will click on transform map create transform map I will give it a name so that is user import transform here I have target table no target table would be sis underscore user I should be able to get that so I have this underscore user table run business rules no I don't yes if I I have to go you can you can do that after transforming the data if you want to run any business rule you can also do that create new record on empty Coley's fields if you have empty the data is not there so you still want to create new record let's do that or maybe let's not check this we will see whether we are able to create data or not it says auto map matching fields I don't think it would be able to automatic system I will click here now it will show you the tables table fields you can if you remember we have this first name last name now apart from that these are all existing that means automatically created fields into your imports a table so you already have these columns into your import set table so you will select this one I will select first name so from here I do have first name in my user racket so I will go - first name yes I do have I will pull it here then I will select last name and from here I will select now last name as well and last name is lecture so I have mapped the fields you can see here all the map fields as well like how exactly they will be mapped but I can see Fred Ludi is already there in your system let's see if if it does something so in that case I will click on save so it's saved both first-name lastname I don't want to put any kind of coalesce because why you would put for first-name and lastname because first and last name cannot be unique names like everybody can have different times name I mean even same names as well so you don't have to select any kind of Collies here now you have to transform this data but before that we will directly go into our system so I will go to says user that list because I will keep it open and I will see if I am able to see my new data I will go here and maybe I will just select created one I already have created I will sort it from like this Z to a and then I will go here and I will click on transform now so ideally it should create three different users into your user table so I would click on transform now it will ask me to run the transform which was just created this one user import transform and I will click on run transform it's done you can do one thing even check it so before checking the results you can directly see here as well and both sets one yes it did insert three records so before checking it you can still check here first whether your data was successfully transformed or not and then you can go here you had three rows these were the target records and if I go here let's see let's refresh it and if i refresh it us so user ID are blank but you have these records Gaurav through party John will shred Ludi how about some scripting I am sure that you will be interested to see some scripting as well so let's say we have these user ID but it is not pushing any user ID automatically now there are different ways you can do that maybe you can write a business rule over here so that every time you write a business rule every time that business rules will run it will automatically concatenate the names and then create the user ID if you want like first name last name but what we will do we will try to make it better directly into your transform because I want to show you that you can also add a scripting into your transform how how can we do that so in that case let's go here let's go to your transform map so what we will do will go to sis system input we're right here it will go to our transform Maps and the map which we just created that was user import transform now this was the transform which we created in order to do is scripting I have to check this box run a script when you will check this box you can run your script directly during the transformation now if you also want different scripting depends on the requirement you have but then you have this option as well that is called transform scripts and I will just quickly show you I will not create it right now I will quickly show you what exactly this transform sky will do you can see you can create these transform script on start that means when transform start on reject on foreign insert there any external data in any other table is being inserted on complete when transform is completed if you want to do something any new choices created on before that means before transform or after transform so it's totally depends the kind of requirement you have and then you can run these transform scripts but as of now I will directly run this script directly on the transfer so I will go here I will click on run a script and here I will write the script so if you remember I have source so in source I have first name so what I will do I will do target dot which one we have user underscore name that's one that is the user ID if you remember this one this is user name so what I will do whenever a new record will be inserted it will automatically push the user ID as well so I don't have to do that manually or maybe by a business tool as well so in that case what I can do you can just try get dot username equal to and then you can put source no you want to create develop that particular user ID but the logic you want to put first name plus last dot nas name so for that we will do it source dot first name plus here I will put a string that is dot and then I will do plus I will do source dot last name that's it you are done your scripting is done no business rules are required here so in that case I will save it now I will create again these three rackets and you will see the difference what I need to do I will click on load data again now this time I don't have to create a new table because table is already created so I will just select the table which I had that is user input here I will select the data source which I have same one I don't have to date change the data I will click on submit done data is loaded into the import set table and even I don't have to create the transform map the reason behind it because that was already created so in that case I can just click on run transform it will automatically show you which transform you have to run and it will also select that one so sometimes it can show you right here but it as of now it has that available maps here and then it is already selecting this one I will just click on transform once you will click on transform it's done success I will click here you will see yes the target record it inserted one racket what it didn't inserted these two there should be a reason and guys I think this is really I would say good opportunity because we are getting errors when you get see the errors you'll be able to understand you might get these errors in your import as well so why we got this error so it completed with errors so one was inserted and - not with errors what you can do you can just go here and just click on here and I think it's not showing the error directly right here I will select this one open okay but you can see the error I think it's not showing in that particular view but you can directly click on the error it says error during insert of sis user afraid Ludi why I will show you why so as of now but it only inserted this one so they should be some some reason that why you your this particular record was able to create but why not these two so I will do one thing I will go to my users table again I will refresh it you can see here it says undefined dot undefined that means it was not able to recognize the source data that's the reason so the field which we just created over there it was not able to recognize so and the reason why other two records were not created because you user ID cannot be same because it's colace it's ideally already on the system level so that's the reason it rejected other two rackets so what we will do let's see our script there should be some issue so we'll go back to our script here we have this target dot username in culture first name plus last name if I go bottom you will see the name is you underscore that's our source field so in that case and that is the main reason your script is not working so what it will do you will do you and the score and just save this and I'm sure now this will definitely create something so we will go back to our load data existing table we will select existing one I will keep the same data source and now I will click on submit when I will click on submit it will pick three rackets inserts I will click on run transform when I will click on run transform it will select the existing transform map I have and then I will click on transform it's done let's see if I'm getting any error it still says completed with errors but it this time I got two inserts but one error and I go here and the part is it is able to insert god of true party and john bill but not this one fred Ludi one let's let's go to here and refresh it when I'm refreshing you can see it is able to push god of doctor party and also John bill but the third one was not created that it was Fred Darla D that racket was not created it was created earlier but when we were when we tried to con connect Concannon Konkan ated the first name and last name in user ID then it didn't work why because that user ID is already there let me search it yes that's the reason so you can see that user ID was already available and ServiceNow won't allow you and use a table automatically directly from the system table it already has that business rule that you can't insert a duplicate user ID so in that case that's the reason your third record was not created and that's how you can do scripting and transform map as well so whatever data external data you have whenever you do any kind of migration or any kind of important service now you have to just remember that how exactly you will align that data into your target table maybe with the help of a scripting may be directly mapping whatever because sometimes it might happen that external data might not have similar kind of data which you have in ServiceNow and you fought you will find this kind of situation every time you do a bigger migration so you have to be be precautious with these data migrations so you have to make sure that whatever scripting you have written it is transforming the data accordingly as for the existing fields you have as for the format you have into your tables in ServiceNow so that's how you import data into ServiceNow in excel format or if you have X XML format with the direct uploading feature or if you have any XML file or how exactly you can utilize data source as well I hope you like my video so thanks for watching and have a great day