How to run data import from a SQL stored procedure

Last modified: June 5, 2025

To import data using a SQL stored procedure with dbForge Data Pump for SQL Server, you need to execute its command-line utility. However, SQL Server stored procedures do not have built-in support for running external command line utilities. As a workaround, you can use the xp_cmdshell extended stored procedure to run the dbForge Data Pump command-line utility from within SQL Server.

Important

xp_cmdshell can pose security risks and is often disabled by default. Make sure that proper security measures are in place before you enable and use the extended stored procedure.

This guide provides steps on how to run data import from a stored procedure that uses xp_cmdshell.

Step 1: Create a template to import data

1. First, you need to configure the settings to be saved as a template. To start this process, in Object Explorer in your SQL Server Management Studio (SSMS), right-click a database, point to Data Pump, and click Import Data.

2. Go through all wizard pages and specify the necessary information and settings.

Note

The actual configuration of settings may differ, depending on the format. You will find the full workflow for each format in the corresponding topic:

• Text
• Microsoft Excel (XLS)
• Microsoft Excel 2007+ (XLSX)
• Microsoft Access
• XML
• JSON
• CSV
• ODBC
• DBF
• Google Sheets

3. After you have configured all necessary settings, click Save Template on the needed the wizard’s page.

Specify the name and the location of your template file in the Save As dialog and click Save.

Step 2: Create a batch file

In Notepad or any text editor, modify the script to import data as follows:

• Type a path to the datapump.com file located in the dbForge Data Pump for SQL Server installation folder on your PC.
• If you did not set a security password to create a database when configuring the import template in Import Wizard, add the /password parameter to the script.

"C:\Program Files\Devart\dbForge SQL Tools Professional\dbForge Data Pump for SQL Server\datapump.com" /dataimport /templatefile:<filepath> /password:<yourpassword>

• The arguments should be substituted with your actual data:

  • /templatefile: Full path to the template .dit file you created in the Data Import wizard, for example, C:\SampleFolder\SampleSubfolder\TemplateFile.dit.
  • /password: The password you set for the data import.

Step 3: Enable the xp_cmdshell extended stored procedure

If the xp_cmdshell extended stored procedure is disabled in your SQL Server instance, you can enable it by running the following commands in SSMS:

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;

Step 4: Create a stored procedure

Next, you need to create a stored procedure that uses xp_cmdshell to execute the batch file. Replace C:\SampleFolder\SampleSubfolder\BatchFile.bat in the following query with the actual path to your batch file:

CREATE PROCEDURE dbo.RunDataPumpImport
AS
BEGIN
    EXEC xp_cmdshell 'C:\SampleFolder\SampleSubfolder\BatchFile.bat';
END;

Step 5: Execute the stored procedure

Finally, execute the created stored procedure to run the data import:

EXEC dbo.RunDataPumpImport; 

After executing the stored procedure, retrieve data from the target table to verify that the necessary records have been imported successfully:

For more information about using the command line for data import with dbForge Data Pump, see Importing data from the command line.

For more information about scheduling data imports, see Scheduling data import.