SATURDAY, MAY 19, 2012

Scheduled File Import via Mid Server

Wednesday, 17 November, 2010 09:23 Written by Jacob Andersen
40 Comments

There so many ways to get information from a 3rd-party system into Service-now and back. It can be done via web services, JDBC, email, file transfer, etc. Let’s say a customer wants to send a CSV file to the Service-now instance, they have a couple of choices: attach the file manually to a data source or place the file on an FTP(s) server. Oddly, there is no option to simply have the mid server send the file directly to the Service-now instance to be processed by an import set. This is a very common complaint/recommendation that I hear from customers.

Placing a file on an FTP server is typically a good idea, especially when that file will be used by other vendors and you want a common location for these CSV files to be retrieved. This is not always an easy thing to do, however, when there is no public-facing FTP(s) server available and you simply need to send a CSV of users, for example, to Service-now.

Introducing, the Scheduled File Importer.

The Scheduled File Importer gives the mid server the ability to send a file that exists on the same machine (or on a mapped drive) directly to a Service-now import set table that has been created previously in your instance.

When this update set is installed, a new section in the Mid Server Application will appear called File Importer.  This section contains three modules, each of which I’ll cover briefly:  Scheduled File Import, Scheduled File Transform, and File Import History.

.

.

Prerequisites:

  • The Mid Server plugin needs to be installed.
  • The CSV file should be added to a data source via an attachment and then imported.  This will create an import set table into which you may import your data.
  • A transform map should be defined for the import set table in order to map the file being sent from the mid server into a target record.

Scheduled File Import

This module allows an admin to schedule the import of a CSV file from a mid server  As shown below, it only has a few fields to define, each of which are pretty self-explanatory.

The only thing to note from these fields is that the import set table that is specified in this record should already exist in the system.

When a scheduled file import record is created, it will run on a scheduled interval that is defined by the record.  When the data is imported, it is added to the import set table in a Pending state (not yet transformed).  Because of this, you have the freedom to load many different files into a particular import set table before a transformation occurs.

Scheduled File Transform

This module is merely a script that can be configured to run on a defined interval.  It looks for all import set tables that are defined in any scheduled file import record and transforms them all.

A transform map must be defined for the import set table in order to process any of the pending records.

File Import History

Whenever a scheduled file import is run, there will be two corresponding entries in the ecc_queue.  The first entry is the outgoing communication to the MID server. In this case, it sends the mid server the command to fetch a file, gives it the file location, and tells it what import set to send the data to.  The second entry contains the raw results of the command.

This module filters the ECC queue for entries that pertain to this solution.  These entries can be quite valuable when debugging efforts are required.

At this time, this solution only works with CSV files.
This entry was posted on Wednesday, November 17th, 2010 at 9:23 am and is filed under Integration. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Comments

Posted On
Dec 02, 2010
Posted By
Paul Rice

Is it possible to use the MID server to do an import of user records from an LDAP server?

Reply
Posted On
Dec 02, 2010
Posted By
admin

Not directly. If you created a CSV extract of your LDAP info and put it on a MID server then you could import that file using this technique.

Reply
Posted On
Jan 25, 2011
Posted By
AdminPro

This is a great addition and we are trying to use it in our environment. We have followed all of the above steps,but ran to an issue. We have three schedules setup which each has their own import-set table and transform map. When the schedules run, only one of them gets to transform and the other two never get to that stage. We have checked to make sure everything is setup as it should be and can’t find any errors or warning in the log either. Is there anything you can think of that can help us resolving this?!

Reply
Posted On
Jan 27, 2011
Posted By
AdminPro

How large of a file this function can handle?! What is the average load time per minute?! Or in another word, how many records should be loaded in one minute (approximately)?!

Reply
Posted On
Jan 28, 2011
Posted By
Jacob Andersen

AdminPro,

Are you still having problems with all of the transforms running? If so, I’d be happy to look into it. Please send me your instance name and I will take a look at what is going on.

Regarding your second question, I don’t believe that we have run any benchmarks on this. Is the reason you’re asking because you’re seeing performance issues or are you just curious?

Thanks.

Reply
Posted On
Feb 28, 2011
Posted By
Benjamin

We had been using this solution for close to a month now, however, recently the file stopped getting imported up to our server. The File Import History still shows that the data is being pushed up, but it never gets imported into our defined table. Any suggestions on how to troubleshoot this?

Reply
Posted On
Mar 01, 2011
Posted By
Mark Stanger

I would start by taking a look at the data in your import set table to see if the information is making it that far. If it is, then the scheduled file importer is probably working correctly and you’ll need to take a look at your transform map. If it isn’t, then you’ll want to take a look at the importer config and make sure that the MID server is up and operating correctly.

Reply
Posted On
Mar 03, 2011
Posted By
Mike Wallick

I’d like to also say that this is a great feature to add to an instance, but I do have some questions. Here’s my understanding of the process:

1). I’ve defined a single file import record, which is pulling a CSV file with ~49,000 records (people).

2). Manually executing the file import runs very fast, usually in just a few minutes.

3). In the import set table, all the records are loaded in a pending state, with no row numbers or import set relationship. This is the piece I’m a little confused with (why isn’t there an import set created at this point?)

4). When I manually execute the scheduled transform script, it appears that an import set is then created, and each record in the import set table gets a row number and import set reference.

At this point, everything does work, but it is horribly slow. The scheduled transform has been running for about 25 minutes now, and has just barely processed 4000 records. What is the expected performance for loading records? This seems rather slow (even for a sandbox instance), and I am seeing repeated warnings in the system log about “Table handling an extremely large result set.”

Any insight would be greatly appreciated.

Thanks.

Mike

Reply
Posted On
Mar 15, 2011
Posted By
Philippe SIEK

Hi,

About transform map on large dataset, it appear that execution time increases exponentially. For a 12000 record transformation, we had about 37h of transformation.

What we have made to avoid this is to disable “Run business rules” in transform maps, now it take only 10 minutes. But, be carefull, we will able to do this just because we had no important business rules associated with our record type “on insert”.

Regards,

Philippe

Reply
Posted On
Mar 03, 2011
Posted By
Benjamin

Hi Mark,

It looks like the table never gets populated after the scheduled file import runs. The messages I get is that it can access the file and it processes the file, but nothing is being inserted in the custom table.

We confirmed the MID server is up. I am able to manually import the file to the same table and that works fine. It just doesn’t work through the scheduled file import. Let me clarify, you can watch and see the schedule file import run, just nothing gets inserted into the table.

Also could you clarify as to what you mean when you say “importer config”. Are you just saying our mid server configuration?

Thanks for your help.

Reply
Posted On
Mar 08, 2011
Posted By
Jacob Andersen

Benjamin,

I have uploaded the latest version of the update set to replace the version that you have. It fixes some performance issues that earlier scripts had. Can I have you try this out in your demo instance and let me know if it resolves your issues?

Thanks!

Reply
Posted On
Mar 08, 2011
Posted By
Benjamin

Is there something I should do to upgrade the version?

I downloaded the copy I did just install it and commit it but data is still not being imported into the temp table for me when I execute the scheduled file import.

Reply
Posted On
Mar 16, 2011
Posted By
Benjamin

We just changed our MID server to run on Linux because the integration with Netcool worked. We tested the file upload and we get the same response. The file never gets imported into the table.

Any suggestions on what I can do to troubleshoot? Here is the information from the File Import History:

SNFilePoster Log:

Initializing SNFilePoster

Running SNFilePoster execute

Arriving to postToImportSet function

Upload URL: ‘https://primedev.service-now.com/nav_to.dosys_import.do?sysparm_import_set_tablename=u_itim_update’

Response status code: 302

Response body:

Reply
Posted On
Mar 16, 2011
Posted By
Jacob Andersen

Looking at your upload URL, it says to go to nav_to.dosys_import.do, which is incorrect. It should probably say

&lsquo ;https://primedev.service-now.com/sys_import.do?sysparm_import_set_tablename=u_itim_update‘

Reply
Posted On
Mar 17, 2011
Posted By
Benjamin

Thanks, Jacob. That is what our problem was. Looks like the mid file import uses the glide.servlet.uri and we had modified it to get it to work with a redirect. That is where the nav_to.do was getting added.

Reply
Posted On
Aug 09, 2011
Posted By
John

I have just tried this and have a problem being reported in the mid server log. Can you advise what is causing this ?

08/09/11 14:45:27 (241) Probe: JavascriptProbe:SNFilePoster Worker starting: JavascriptProbe
08/09/11 14:45:27 (382) Probe: JavascriptProbe:SNFilePoster Slow compile (141ms) of script: probe:SNFilePoster
08/09/11 14:45:27 (522) Probe: JavascriptProbe:SNFilePoster WARNING *** WARNING *** org.mozilla.javascript.EcmaError: “SNFilePoster” is not defined.
Caused by error in JavaScript probe ‘SNFilePoster’ at line 1

==> 1: var req = new SNFilePoster();req.getLog()

08/09/11 14:45:27 (538) Probe: JavascriptProbe:SNFilePoster Enqueuing: D:\ServiceNow\agent\work\monitors\ECCSender\output\ecc_queue.2aeab3fe24d81000dbfb09307d1280a7.xml
08/09/11 14:45:27 (538) Probe: JavascriptProbe:SNFilePoster Worker completed: JavascriptProbe time: 0:00:00.297
08/09/11 14:45:28 (335) ECCSender.1 Sending ecc_queue.2aeab3fe24d81000dbfb09307d1280a7.xml

Reply
Posted On
Aug 09, 2011
Posted By
Jacob Andersen

Hmm, that does sound strange. Please try downloading the update set again and re-applying. You’re missing something.

Reply
Posted On
Aug 10, 2011
Posted By
John

Ok, downloaded the update set 2.3 and applied all ok. However this has not changed the problem.

08/10/11 11:51:21 (118) Probe: JavascriptProbe:SNFilePoster Worker starting: JavascriptProbe
08/10/11 11:51:21 (571) Probe: JavascriptProbe:SNFilePoster Slow compile (453ms) of script: probe:SNFilePoster
08/10/11 11:51:21 (884) Probe: JavascriptProbe:SNFilePoster WARNING *** WARNING *** org.mozilla.javascript.EcmaError: “SNFilePoster” is not defined.
Caused by error in JavaScript probe ‘SNFilePoster’ at line 1

==> 1: var req = new SNFilePoster();req.getLog()

08/10/11 11:51:21 (993) Probe: JavascriptProbe:SNFilePoster Enqueuing: D:\ServiceNow\agent\work\monitors\ECCSender\output\ecc_queue.177c9cdf245c1000dbfb09307d128041.xml
08/10/11 11:51:22 (290) Probe: JavascriptProbe:SNFilePoster Worker completed: JavascriptProbe time: 0:00:00.781
08/10/11 11:51:23 (009) ECCSender.1 Sending ecc_queue.177c9cdf245c1000dbfb09307d128041.xml

How does applying the update set to the instance affect the mid server ? Is the SNFilePoster a componenet that needs to be applied to the mid server ?

Reply
Posted On
Oct 12, 2011
Posted By
ND

Hi,

I have uploaded the update set as per document without any error. I used below link to upload the update set:

http://www.servicenowguru.com/service-now-general-knowledge/installing-sncguru-update-set/

My intention is to import the data from one Service-Now server to another Service-Now server or any external application.

As per steps mentioned above :-

“When this update set is installed, a new section in the Mid Server Application will appear called File Importer. This section contains three modules, each of which I’ll cover briefly: Scheduled File Import, Scheduled File Transform, and File Import History.”

I am not getting any new section in the MID server section in SN GUI.

Could you please help me out if I am missing something or need to do any thing else.

Regards,
ND

Reply
Posted On
Oct 12, 2011
Posted By
Mark Stanger

Send me your instance name via the contact page and I’ll take a look.

Posted On
Dec 07, 2011
Posted By
Bhavesh

Even I am getting the above error.

Evaluation error: Caused by error in JavaScript probe ‘SNFilePoster’ at line 1

==> 1: var req = new SNFilePoster();req.getLog()

Please help

Reply
Posted On
Dec 08, 2011
Posted By
Jacob Andersen

Please update your SNFilePoster mid server script include with the latest code to make sure that we’re debugging a true problem. You can find the latest code here: http://www.servicenowguru.com/scheduled-file-poster-code/

Please update me with your results. Thanks!

Posted On
Dec 14, 2011
Posted By
Mike Wallick

Actually, I am seeing the same error in the ecc queue coming back from the mid server. Looking at the mid server agent log, there is an error about “SNFilePoster is not defined.”

I’m working on a june2011-05-25-2011__patch3-11-14-2011 instance, mid server version 11-22-2011_1328. I did download the code from http://www.servicenowguru.com/scheduled-file-poster-code/ just to be sure, but that didn’t help.

Reply
Posted On
Dec 14, 2011
Posted By
Bhavesh Jain

Answer for Mike:
There is some change required in the access control written on ecc queue table.
Below are the details:
Add mid_server role to the acl ecc_agent_script_include.* and ecc_agent_script_include for read operation
Go to ‘requires role’ related lists on above ACL’s and assign mid_server role.
This should solve the issue.
Hope this works!!!

Reply
Posted On
Dec 15, 2011
Posted By
Mike Wallick

Unfortunately, those ACLs are already in place. Any other ideas?

Reply
Posted On
Dec 15, 2011
Posted By
Mike Wallick

Ack. Someone had reset all the passwords in the user table, including the account the mid server was using. Odd that I was seeing “SNFilePoster not defined” instead of “authentication failed” in the log.

Reply
Posted On
Dec 15, 2011
Posted By
Bhavesh Jain

Those ACLs are already there but see if the requires role related list has the mid server role.

Reply
Posted On
Jan 17, 2012
Posted By
Scott Ronning

Is there an option to delete the source file (csv) after import?

FYI: The process works perfectly!!! Should be part of the Out of Box product…

Reply
Posted On
Jan 17, 2012
Posted By
Jacob Andersen

You can do this by simply running a post script to do the cleanup, if you wish. To do this, check the “Run Post-script” checkbox and a script field will appear. In that script field, you can enter a command that is specific for the machine where the command will run. In other words, if the mid server is running on a Windows machine, you’ll want to use a DOS command to delete the file. Otherwise, you’ll use a BASH command.

Thanks for the feedback!

Reply
Posted On
Jan 17, 2012
Posted By
Glenn Pinto

Thanks Jacob for the information. I am going to give this a try!!

Reply
Posted On
Feb 06, 2012
Posted By
Ankush Gupta

Hi Jacob,
I have installed this update set in my instance.But our ftp servers requires password and username for loging in,So my question is where i will put those credentials without Breaking the code.

Kind Regards,
Ankush

Reply
Posted On
Feb 06, 2012
Posted By
Mark Stanger

This functionality doesn’t connect to an FTP server. See this line in the post above…
“The Scheduled File Importer gives the mid server the ability to send a file that exists on the same machine (or on a mapped drive).”

You need to install the MID server on the machine that hosts the files (or can access them via a mapped drive). As such, there is no need for any credentials other than what you would use in a regular MID server config.

Reply
Posted On
Feb 21, 2012
Posted By
Robert

Has this been tested on Aspen? I’ve been reliably getting an error with an import that previously worked as configured (85% sure, anyway)… File is not a normal file / status code 200.

Thanks!

Reply
Posted On
Feb 21, 2012
Posted By
Mark Stanger

I’m not aware of anything in Aspen that would break this, though there were some issues with the previous June 2011 release. Please ensure you’re using the latest version of the update set from this site (especially the latest version of the script include).

Reply
Posted On
Feb 21, 2012
Posted By
Robert

Yes, I’m using 2.5.

Reply
Posted On
Feb 21, 2012
Posted By
Jacob Andersen

Per your request, I just installed this on Aspen. It looks like it runs just fine for me on the demo instance running Aspen. I’m not sure what the issue you might be seeing. However, I’d double-check the path you have entered to make sure that it points to a valid file.

Reply
Posted On
May 14, 2012
Posted By
Tomas Larsson

I have tried the update set, but got some errors in the preview, which indicate that there are 7 missing dictionary entries in the u_scheduled_file_import table. The missing entries are: run_dayofmonth, active, run_start, name, run_period, run_dayofweek and run_type. Also, the “Scheduled File Transform” module was not created on commit.

I also tried to apply it to one of the demo-instances and got the same result. Have you seen this before?

/Tomas

Reply
Posted On
May 14, 2012
Posted By
Jacob Andersen

I just tried this on a demo instance and it seems to install the module just fine. Did you make sure to refresh your application list after installing the update set? You’ll need to do that in order to see the new module.

I did notice the preview errors that you mentioned…. my guess is that since my table extends the system scheduled job table, the previewer script does not see the base table’s fields when doing the evaluation. After I commited the udpate set, the list and forms all looked good.

Let me know if you can see the module after a refresh.

Reply
Posted On
May 14, 2012
Posted By
Tomas Larsson

Thanks for your quick response! I located the missing module. It was set to inactive. Is it only activated when the import set table has a corresponding transform?

/Tomas

Reply
Posted On
May 15, 2012
Posted By
Jacob Andersen

No, I think it should be active on the update set’s commit. I’m not sure why it didn’t come that way for you.

Reply

Leave a Reply


Notify me of followup comments via e-mail. You can also subscribe without commenting.

Latest Comments

  • Jim Coyne: I’m not sure exactly what you are looking for, but can you use “window.location” in your...
  • Ian: Might want to check the single quotes around ITIL in the condition line, they gave an error for me until I...
  • Mark Stanger: That’s correct. This returns instance URLs. I don’t have an equivalent currently that...
  • ND: Hi Mark, This is very useful information. I am looking for similar method to find URL of a site created by us. We...