What is this B.C.P. process everyone’s talking about?

13 minute read time.

Hello readers,

Today we are going to be looking at a relatively old, yet new, process. It’s a bit complicated to explain (going to try my best), but its super easy to use. We are going to be looking at the “Import from .dmp files, with Bulk Copy utility and Export Data Pump” which is also more commonly known as BCP process. This really easy utility to use and will drastically decrease the time it takes to export and import data sets for Sage X3. The utility itself is currently built into the Management Console, which was not always the case.

We are not going to be looking at how to manually run the utilities (as they are part of MS SQL Server) but we are going to look at the “Import from .dmp files, with Bulk Copy utility and Export Data Pump” function that you can find in later versions of the Management Console (AKA SAFE Console). If you have the Management console its most likely already there. If you are still using the SAFE console (which is an old version of the newer Management Console) then theres a chance, it’s not there. The Console version I am using in this blog is CFG.2.50.1.37. If you have a different version than this, it should be ok still.

Anyways, if you have a machine to follow along, great, if not that’s ok. You can just read this or later I will be posting a video on our YouTube channel on how to use it also. I will post the video in the chat as soon as I can.

Let’s get started…

So first off, you are going to need a data set to export. You will also need access to the Management Console and the associated user that goes with the setup/configuration. Once you have that, you can start.

In my demonstration, I am using version 11 patch 20 (just to show older versions work). You know you have the correct login, when you log into the Management Console, and you have a Data-Application configuration with stuff in it like my screenshot. If you don’t see anything, you have the wrong user account.

Next you want to click on the Folders tab from the top bar to load the folder (endpoint) data listings

Select the folder you want to export (in my case its SEED) and click the Export button from menu bar.

Now the important part. On the Export folder popup, you will see an option labeled: “Export data to native dump files. This function uses Bulk Copy utility on MS SQL Server, and Export Data Pump on Oracle”. You want to check that sucker. That’s the money maker button!!

You can leave the rest of it as default. I like to have a fresh SVG folder each time. Then slap that OK button to start the process of exporting.

Now, if you left the defaults, you may get a few popups after hitting ok. Like this one:

I said yes.

Or this one:

Which I also said OK to (because I want it to use it)

After all of that warning and saying YES and OK to stuff, it should kick off the export process

And hopefully it finishes really fast. For me it was so fast I had to double check the logs to make sure it was actually doing stuff.

I ran the export earlier and here is an example of what the trace log would typically look like.

14/12/2021 01:30:47 : INFO - ---------------------------------------------------------------------

14/12/2021 01:30:47 : INFO - Folder configuration

14/12/2021 01:30:47 : INFO - ---------------------------------------------------------------------

14/12/2021 01:30:47 : INFO - Starting configuration from Runtime X3ERPV12RUN x3erpv12sqlvm 14/12/2021 01:30:47

14/12/2021 01:30:47 : INFO - Starting creating the SVG directory

14/12/2021 01:30:47 : INFO - Creating the folder directories D:\Sage\X3ERPV12\Folders\SEED\SVG - x3erpv12sqlvm

14/12/2021 01:30:48 : INFO - End of creation of the backup directories

14/12/2021 01:30:48 : INFO - Starting generating the folder table list

14/12/2021 01:30:48 : INFO - Launching the generation script of the folder table list - D:\Sage\X3ERPV12\Folders\SEED

14/12/2021 01:30:49 : INFO - Recovering the folder table list

14/12/2021 01:30:49 : INFO - Launching the generation script of the folder view list - D:\Sage\X3ERPV12\Folders\SEED

14/12/2021 01:30:50 : INFO - Recovering the view table list

14/12/2021 01:30:50 : INFO - 1942 tables found

14/12/2021 01:30:50 : INFO - End of generation of the table list (00:00:01.6929852)

14/12/2021 01:30:50 : INFO - Starting exporting the folder tables

14/12/2021 01:30:50 : INFO - Extracting the folder tables - SEED

14/12/2021 01:30:50 : INFO - Starting exporting the folder SEED tables to D:\Sage\X3ERPV12\Folders\SEED\SVG - Windows

14/12/2021 01:30:53 : INFO - Script 'D:\Sage\X3ERPV12\Folders\SEED\SVG\_valfilbat.bat' generated, host x3erpv12sqlvm, 1659 table(s) - 283 view(s)

14/12/2021 01:30:53 : INFO - Executing '_valfilbat.bat' on x3erpv12sqlvm to extract tables data - (LibAdm.ExtractTablesWin32)

14/12/2021 01:31:06 : INFO - End of extraction of the tables - Windows

14/12/2021 01:31:06 : INFO - End of extraction of the tables (00:00:16.1890856)

14/12/2021 01:31:06 : INFO - Starting extracting the sequences

14/12/2021 01:31:06 : INFO - Extracting the sequences - SEED

14/12/2021 01:31:06 : INFO - Starting extracting the sequences - Windows / SqlServer - x3erpv12sqlvm

14/12/2021 01:31:06 : INFO - Sequence file stream generated, 1659 tables - (LibAdm.ExtractSequenceWin32Sql)

14/12/2021 01:32:32 : INFO - End of extraction of the sequences - SQL Server (00:01:26.1073871)

14/12/2021 01:32:32 : INFO - End of extraction of the tables

14/12/2021 01:32:32 : INFO - Starting generating the srf files

14/12/2021 01:32:32 : INFO - Generating the folder srf files - D:\Sage\X3ERPV12\Folders\SEED\SVG

14/12/2021 01:33:23 : INFO - End of generation of the srf files (00:00:50.7342099)

14/12/2021 01:33:23 : INFO - Starting exporting the database

14/12/2021 01:33:23 : INFO - Exporting the database - SEED (Native dump mode)

14/12/2021 01:33:23 : INFO - Starting exporting the database - SQL Server

14/12/2021 01:33:23 : INFO - Bcp path built from key 'database.software.sqlodbctools', runtime X3ERPV12RUN: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools

14/12/2021 01:36:54 : INFO - End of database export - SQL Server (00:03:30.8768724)

14/12/2021 01:36:54 : INFO - End of configuration (00:06:07.2475031)

14/12/2021 01:36:54 : INFO - ---------------------------------------------------------------------

14/12/2021 01:36:54 : INFO - End of configuration

14/12/2021 01:36:54 : INFO - ---------------------------------------------------------------------

If you take the time to figure out how long it actually took (6 minutes and 7 seconds to be exact) you can see how fast it really was.

Here is another trace of running the export the original way:

14/12/2021 01:38:51 : INFO - ---------------------------------------------------------------------

14/12/2021 01:38:51 : INFO - Folder configuration

14/12/2021 01:38:51 : INFO - ---------------------------------------------------------------------

14/12/2021 01:38:51 : INFO - Starting configuration from Runtime X3ERPV12RUN x3erpv12sqlvm 14/12/2021 01:38:51

14/12/2021 01:38:51 : INFO - Starting creating the SVG directory

14/12/2021 01:38:53 : INFO - Delete directory 'D:\Sage\X3ERPV12\Folders\SEED\SVG'

14/12/2021 01:38:53 : INFO - End of creation of the backup directories

14/12/2021 01:38:53 : INFO - Starting generating the folder table list

14/12/2021 01:38:53 : INFO - Launching the generation script of the folder table list - D:\Sage\X3ERPV12\Folders\SEED

14/12/2021 01:38:53 : INFO - Recovering the folder table list

14/12/2021 01:38:54 : INFO - Launching the generation script of the folder view list - D:\Sage\X3ERPV12\Folders\SEED

14/12/2021 01:38:54 : INFO - Recovering the view table list

14/12/2021 01:38:54 : INFO - 1942 tables found

14/12/2021 01:38:54 : INFO - End of generation of the table list (00:00:01.6859769)

14/12/2021 01:38:54 : INFO - Starting exporting the folder tables

14/12/2021 01:38:54 : INFO - Extracting the folder tables - SEED

14/12/2021 01:38:54 : INFO - Starting exporting the folder SEED tables to D:\Sage\X3ERPV12\Folders\SEED\SVG - Windows

14/12/2021 01:38:59 : INFO - Script 'D:\Sage\X3ERPV12\Folders\SEED\SVG\_valfilbat.bat' generated, host x3erpv12sqlvm, 1659 table(s) - 283 view(s)

14/12/2021 01:38:59 : INFO - Executing '_valfilbat.bat' on x3erpv12sqlvm to extract tables data - (LibAdm.ExtractTablesWin32)

14/12/2021 01:56:59 : INFO - End of extraction of the tables - Windows

14/12/2021 01:56:59 : INFO - End of extraction of the tables (00:18:04.8210271)

14/12/2021 01:56:59 : INFO - Starting extracting the sequences

14/12/2021 01:56:59 : INFO - Extracting the sequences - SEED

14/12/2021 01:56:59 : INFO - Starting extracting the sequences - Windows / SqlServer - x3erpv12sqlvm

14/12/2021 01:57:01 : INFO - Sequence file stream generated, 1659 tables - (LibAdm.ExtractSequenceWin32Sql)

14/12/2021 01:58:29 : INFO - End of extraction of the sequences - SQL Server (00:01:29.6959131)

14/12/2021 01:58:29 : INFO - End of extraction of the tables

14/12/2021 01:58:29 : INFO - Starting generating the srf files

14/12/2021 01:58:29 : INFO - Generating the folder srf files - D:\Sage\X3ERPV12\Folders\SEED\SVG

14/12/2021 01:58:49 : INFO - End of generation of the srf files (00:00:20.0141674)

14/12/2021 01:58:49 : INFO - End of configuration (00:19:57.6989357)

14/12/2021 01:58:49 : INFO - ---------------------------------------------------------------------

14/12/2021 01:58:49 : INFO - End of configuration

14/12/2021 01:58:49 : INFO - ---------------------------------------------------------------------

This one took 19 minutes and 58 seconds which is about 3 times as long as the BCP function is. If you use the Data extract tools in the development function it takes even longer.

With that said, the Folder Import process is pretty much the same thing. You check the same button on the import popup, and it will run through the same utility tools.

You want to go to the same Management Console \ Folders tab that we previously started at, but this time you want to click on the Import menu button

I went ahead and setup a TEST folder to import (just for this blog) to show what the option is. It’s really just an export of the SEED folder that I renamed TEST.

A Very Important Note: When importing with this method, the exported folder must have used the same Bulk Copy for the import Bulk Copy to work. You cannot use the function on a folder that was exported with the original/tradition export and vise versa.

On the Import a folder popup you want to check the “Import from .dmp files, with Bulk Copy utility and Export Data Pump” checkbox before running the import. Then all you have to do is click OK button at the bottom and watch the magic happen.

The import process will start as soon as you click OK.

When it’s finished you can click ok to the popup for complication and if all went according to plan, you should see the newly imported folder show up on the listing

Here is what the trace log looks like

16/12/2021 02:26:06 : INFO - ---------------------------------------------------------------------

16/12/2021 02:26:06 : INFO - Sage Application setup 16/12/2021 02:26:06

16/12/2021 02:26:06 : INFO - ---------------------------------------------------------------------

16/12/2021 02:26:06 : INFO - Starting folder import

16/12/2021 02:26:06 : INFO -

16/12/2021 02:26:06 : INFO - *--- Sage Folder Import ---*

16/12/2021 02:26:06 : INFO - Reading solution.xml on x3erpv11vm...

16/12/2021 02:26:06 : INFO - Information from solution.xml retrieved.