CSV from FTP to SQL Database Boomi: A step-by-step guide

Ever wondered how to move customer data from an FTP server into a SQL database without writing endless lines of code? If you're still manually retrieving files from FTP servers and importing them into SQL databases, you're not alone but there's a better way.

Manual data handling slows teams down, introduces risk and creates unnecessary effort. In this article, we walk you through a practical Boomi use case: automatically transferring a customers.csv file from an FTP location directly into a SQL database. It’s a clean, dependable approach that cuts down repetitive tasks and gives your team time back.

From scheduling and error handling to data transformation, we’ll show you how to create a reliable integration that fits neatly into your existing operations.

Prerequisites: What you'll need before you start

Before we begin, make sure you have access to the following:

1. Boomi: if you do not already have access to Boomi, sign up for a trial here: Boomi Free Trial
2. An FTP Server uploaded with the customers.csv file
3. A SQL Database, either one hosted on AWS/Azure or other online platform, or install SQL Express locally which can be downloaded here
4. Create a database called Customers in SQL and add the following table

1CREATE TABLE [dbo].[Customers](
2	[id] [int] IDENTITY(1,1) NOT NULL,
3	[SourceID] [varchar](50) NULL,
4	[Firstname] [varchar](50) NULL,
5	[Lastname] [varchar](50) NULL,
6	[Phone] [varchar](50) NULL,
7	[Email] [varchar](50) NULL,
8	[FullName] [varchar](50) NULL,
9	[Street] [varchar](255) NULL,
10	[City] [varchar](50) NULL,
11	[Postcode] [varchar](50) NULL,
12	[Source] [varchar](50) NULL
13) ON [PRIMARY]

Step-by-step: Configure the Boomi Process

Before building the process, it’s important to understand what we’re trying to achieve. We’ll create a Boomi integration that connects to an FTP server, retrieves a CSV file, transforms the data and inserts it into a SQL database. This approach is common in scenarios where data needs to move reliably from external sources into core systems.

The following steps will walk you through setting up this integration, one component at a time:

Step 1: Log in to Boomi and go to the Integration section, in the directory structure on the left side click on the 3 dots at the end of a folder and select "New Component".

Step 2: Then select “Process” and click "Create".

Step 3: You will be asked to create the start shape which can be set to a connector, we are going to set ours to FTP V2, then click the green + on Connection.

Step 4: Add in the host name and credentials for your FTP Server and Save & Close.

Step 5: You will also need to set up an operation for the FTP V2 connector by clicking the green + symbol next to "Operation" section then click the "Import" button.

Step 6: Select the runtime you wish to use, for this we will use Boomi Cloud Runtime and click "next".

Step 7: Set the object type to "File" if it isn't already and click "next".

Step 8: Set the filter to “filename EQUALS ?” and click "Finish".

Step 9: On the start step click the "Parameters" tab near the top.

And click "Import all", then double click on the imported “filename:EQUALS” , set the static value to “Customers.csv”

Step 10: On the process page in the object menu on the left, click "Connect". Then, search for "Database" and select the "Database (legacy) Connector" and drag it to the workspace.

Step 11: The connector step window should open automatically, create a new connection as we did before for the FTP connection, set the driver to SQL Server (Microsoft), add your host url and credentials and set the database name to Customers.

Then test the connection using the Boomi Cloud runtime.

Step 12: Next we need to set the action to send and setup the Operation, within the operation window add a "new Profile" by clicking the green +.

Set the type to “Dynamic Insert” then click the "Import" button at the top right.

Set the runtime to the Boomi Cloud Runtime the connection should be set to the one you created earlier, then click "Next".

Select the "Customers" table from the table list, then select all the fields and click "Next".

Click "Finish" and "save & close the profile" then "save and close the operation" and press "OK "on the connector step.

Step 13: Connect the Database connector to a stop object, you may have to drag one in from the left hand objects menu.

Step 14: From the objects menu on the left side, drag a map object onto the canvas, which will open the map step. In the map field, create a new map by clicking the green + icon.

In the "Map destination", select "Choose" from the top and select the "Database map" we created when we set up the connection, click "OK".

Click "Choose" in the Map Source, select flat file profile type, create a new profile using the green + icon.

Select the options tab, set "File delimiter" to "Comma Delimited" and check the "Use Headers box", then select "Import "from the top right, select the "Customers.csv file".

Click "Next" and "Finish", Save and Close, your map should look something like this:

Step 15: Map all the CSV fields on the left to their appropriate fields on the right using the following mapping guide.

Your map should look like this now:

Step 16: We need to set the source field in the database, this will be a fixed value for all that come from this CSV, to do this click the drop down arrow on the source field and click "Set Default Value".

Set the value to “Customers.CSV”.

Step 17: In the database there is a "Fullname" field an equivalent does not exist in our CSV so we will have to make it using a combination of the First Name and Last Name fields.
In the centre column (Functions) of the map click the blue + sign, then click "

Step 18: On the inputs side click the + at the top and add 2 fields, Firstname and Lastname, on the Outputs side click the + at the top and create a field called "Fullname". On the center panel click the + at the top and select "String" in the category box.

Select "Append" and press "OK", in the character to append box add a space and click "OK".

Drag the First Name to the Original String of the newly created Append Function.

Create a new function select "String" from the category and then select" String Concat" and press "OK".

Add two inputs using the plus at the top, Firstname and Lastname, and then click "OK" and then "OK" again on the next window.

Connect the result of the String Append function to firstname and the input last name to last name, and the result of the concat function to Fullname on the output, then click "Save & close".

Step 19: In the map connect the inputs first name and last name to the function and the Fullname to the fullname target field.

Step 20: Save and close the map, and connect the FTP source to the map and the Map to the Database target and the database target to the stop object and save.

Save the process and press "Test", this will run the process using your selected Runtime in this instance it would be the Boomi Cloud Runtime.
If the test was successful, it will show each item in the process with a green hue, if the hue is red, that item failed, and you would need to investigate the logs to discover the reason by clicking the item and investigating in the bottom section of the test screen.

In the database there should now be 1000 new records in the customers table.

So let's recap, we created our database and table before starting in Boomi, created a FTP Source that gets the customers.csv file and passes it to our map, the map maps the source and target fields and executes any functions, it then gets sent to our database using the database legacy connector the final result of this is the import of 1000 customer records to the database.

Conclusion

Manually handling data files, chasing missing records, or repeating the same import steps each week, these are the kinds of tasks that slow teams down and introduce risk. With Boomi, you can design a clean, dependable process that does the work for you, every time.

In this guide, we’ve shown you how to set up a practical Boomi integration that collects a .csv file from an FTP server and loads it straight into a SQL database. Just a structured approach that brings consistency and clarity to your operations.

Want to know what else you can simplify with Boomi? Let’s talk. At Claria, we help organisations build smart, scalable integrations that reduce manual work and make systems work together naturally. Contact us for a free consultation or a tailored demo.

Talk to our experts!

Contact our team and discover the cutting-edge technologies that will empower your business.

Get in touch

Chris Fry

Share