In this example we are going to make a job to import a .txt-file. The example uses a text file with fixed field length. Step 1 – 3 are done in SQL Server Management Studio.

Filenames table

You need to create a table to contain the filenames (path included) of the files that have to be imported/ The fields in the table should contain the following information:

1. File name: Filename and path to the file at the time of the import.
2. Date: Fill this field when the import was successful
3. Type: Indication of the type of import file

Example code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Custom Import Files](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[File name] [nvarchar](250) NULL,
	[Date] [datetime] NULL,
	[Type] [nvarchar](100) NULL
) ON [PRIMARY]
GO

Table with the contents of the .txt file

Now create a new table to contain the contents of the .txt file we are going to import.. The fields in the file should be large enough to contain whatever is in your test file. All fields are created as NVARCHAR. Transformation to other formats will be done later in the process.

Example code:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Custom Import Files](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Field1] [nvarchar](250) NULL,
	[Field2] [nvarchar](100) NULL,
	[Field3] [nvarchar](100) NULL,
	[Field4] [nvarchar](100) NULL,
	[Date] [nvarchar](10) NULL,
) ON [PRIMARY]
GO

Configuration

Next, create the configuration to select the files that should be imported. For example, we are going to scan folder ‘E:\Data\Import\’ for files which names start with ‘MyImportFile’.
(remember that E: is not your local environment when you run this, it will be E: on the server where you run the software)

Make an entry in table [Tasks]

ID	1	(Just an ID)
Batch ID	10	(ID to identify this action – this ID has to be unique)
Description	‘Import of txt test file’	(description of the process – not mandatory)
Source type	null
Source name	‘E:\Data\Import\MyImportFile*’
Clear destination	0
Active	1
Option	null
Memo	null

And the second entry [import]

ID	2
Batch ID	10	(same ID as in the [Tasks] table entry)
Description	‘Import of txt test file
Table name	‘Custom Import File’
Field name	‘Type’
Source column	null
Operation	‘=’ v
Text	‘Test’
Key	0

This is all the configuration needed. This configuration will make the import task scan ‘E:\Data\Import\’ for files with a name that starts with ‘MyImportFile’. For every file that is found a record will be added to table ‘Custom Import File’ where the name of the file is placed in field [File name] and the field [Type] will be set to ‘Test’

All that is needed to do this in the import job is the ‘Import_Directory’ command (see step 6 Create a job to perform the import).

Create a format file

Next up is creating a format file that describes the field in the import file. This is a text file and simply be created e.g. in Notepad. In this example we name the file ‘TestFormatFile.txt’

Example of what the file would look like for this example:

<?xml version="1.0"?>
<BCPFORMAT
       xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
    <FIELD ID="1" xsi:type="NativeFixed" LENGTH="250"/>
    <FIELD ID="2" xsi:type="NativeFixed" LENGTH="100"/>
    <FIELD ID="3" xsi:type="NativeFixed" LENGTH="100"/>
    <FIELD ID="4" xsi:type="NativeFixed" LENGTH="100"/>
    <FIELD ID="5" xsi:type="NativeFixed" LENGTH="10"/>
    <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="\n"/>
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="Field1" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="2" NAME="Field2" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="3" NAME="Field3" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="4" NAME="Field4" xsi:type="SQLCHAR" />
    <COLUMN SOURCE="5" NAME="Field5" xsi:type="SQLCHAR" />
  </ROW>
</BCPFORMAT>

The file can be placed anywhere as long as it’s available from the server. Best practice is to keep it in a separate folder inside the folder with import files. In this example it will be in ‘E:\Data\Import\FormatFiles\’.

Create a command for the import

For the next step we will use the MA!N Commands Window. We are going to create a cursor containing the names of the files we are going to import and then import them by scrolling through the cursor and import the data in the files which are named in the cursor.

The code would look like this:

-- Declare variables.
DECLARE @FullImportFileName NVARCHAR(300)
DECLARE @cmd NVARCHAR(500)

-- Create cursor.
DECLARE import_files CURSOR FOR
	SELECT	[File name]
	FROM	[Custom Import files] 
	WHERE	[File Name] is not null 
		AND [Date] is null
		AND [Type] = 'Test'

OPEN	import_files
FETCH NEXT FROM import_files INTO @FullImportFilename

WHILE @@FETCH_STATUS = 0
BEGIN
	-- Create Bulk insert statement.
	SET @cmd = N'BULK INSERT [Custom Import EPIC Customers]
	FROM ''' + @FullImportFileName + 
	''' WITH (CODEPAGE = ''ACP'', FORMATFILE = ''' + 'E:\Data\Import\FormatFiles\TestFormatFile.txt'')'
	-- Execute statement.
	EXEC sp_executesql @cmd
FETCH NEXT FROM  import_files INTO @FullImportFileName
END

CLOSE import_files
DEALLOCATE import_files

Once the import job is completed the [Date] field should be filled.

Create a job to perform the import.

For this step you will go to the Jobs Window in MA!N (View > Configuration > Jobs). In this window we will select ‘NEW’. A window should pop-up where you can create the new job.

This command ensures that first step 3 is executed and then step 4. If it’s a large import file we can indicate that the process can use more time by specifying ;999 at the end.

Execute_Non_Query;20 -> Execute_Non_Query;20;999

Add job to scheduler.

The final step is to schedule the Job. To do this we will open the Jobs Scheduled Window ((View > Configuration > Jobs Scheduled). In this window we will select ‘NEW’. A window will pop-up where you can add the new job to the scheduler.