Hey there, if you’re knee-deep in ETL work with Pentaho Data Integration (aka Kettle), you’ve probably struggled at some point grabbing files from remote servers. Maybe your team’s stats team dumps CSV sales files on a client’s FTP weekly, or marketing drops ZIP lead lists on a shared server that only speaks FTP. Trying to manually pull those files? Total time drain, and you risk missing updates if you forget. That’s where the FTP Input step in Kettle comes in, and as a Kettle solution provider, I’ve helped so many of our clients stop doing manual file grabs and automate this whole messy process. Today I’m breaking down exactly how to use this step like a pro—no jargon, just real tips we’ve tested with actual projects. Kettle
First, let’s level set: what even is the FTP Input step? It’s built right into Kettle’s Core category of steps, so you don’t need extra plugins or paid add-ons to use it—huge win, right? It’s made to connect to any standard FTP or SFTP server (wait, yes, it works for both) and pull file data straight into your Kettle transformation, no local intermediate save needed if you don’t want it. I’ve seen new users overlook that; they think they have to download the file to their PC first, but nope—this step pulls the data directly into your stream, which is faster and saves you from cleaning up leftover cluttered files.
Let’s start with the basics: opening the step and setting up the connection. When you drag FTP Input onto your transformation canvas and double-click it, the first tab you’ll see is “General”—that’s where you handle all the server deets. First, pick your connection type: FTP or SFTP. Most people default to FTP, but if your server uses SSH File Transfer Protocol (the secure one we recommend for almost all client work now), you’ll want SFTP. Next, enter the server name—like ftp.clientcompany.com—or the IP address, plus the port. FTP usually uses port 21, SFTP uses 22, but always double-check your server’s specs, we’ve had clients accidentally use port 80 for FTP once and spent an hour troubleshooting. Then username and password, easy. Wait, if you’re using SFTP, you can also use a private key instead of a password if your security team enforces that—we love that for enterprise clients because it adds way more security than basic passwords.
Now the part that trips everyone up: defining the files you want to grab. That’s the “File” tab. Here, you tell Kettle exactly what files to pull. First, you can choose to either get a single file or a set of files with a mask. Like, if you only want sales_2024.csv, you put that exact name. If you want all CSVs from last month, you use a mask like sales_2024-0[89].csv—wildcards work here, right? So *.csv grabs every CSV in the folder, which is great if your client dumps multiple report files at once. Pro tip we use all the time: add a last modified filter. If you only want files updated in the last 24 hours, that keeps your transformation from reprocessing old files you already handled. I had a retail client a few months back that was pulling 10-year-old inventory reports every week, filling up their server with junk data—adding a “modified within last 7 days” filter fixed that instantly.
Once you’ve got your files selected, move to the “Content” tab. This is where you tell Kettle what the file structure is like, because FTP Input needs to know how to parse the data. Most files we work with are CSVs, but sometimes they’re fixed-width text files, or even Excel files (wait, yes, it works with Excel too, though we usually recommend CSVs for FTP because they’re simpler). For CSVs, you set the delimiter—comma, tab, semicolon, whatever your file uses. Quote character if there are text fields with commas inside, like “Doe, John” so Kettle doesn’t split that into two columns. Then header line: if your file has a first line with column names, check “skip header line” and set the number to 1. If there’s a header and you don’t skip it, Kettle will treat that first line as data, which is a silly common mistake—we laugh about it now, but it caused a bad sales report for a client once, so that’s a lesson learned.
Wait, what if the files have weird encoding? Like a lot of European clients use ISO-8859-1 instead of UTF-8, or have special characters. The “Content” tab lets you set the input encoding—super important, otherwise you’ll get garbled text with accented names or special symbols. I once had a client in France that had customer names with é and ç, and we pulled all their reports without setting the encoding, so every name looked like ???. That was a quick fix, but it messed up their marketing segmentation, so don’t skip that setting.
Next, the “Fields” tab. This is where you define the data types for each column, right? Kettle can guess them if you check “guess fields” at the top, but for production work, we always manually set them. Why? Because guessing can mess up big numbers (like customer IDs that are 10 digits, Kettle might guess integer and cut off leading zeros) or dates. So if your sales amount is a decimal, set that to Number; if your order date is MM/dd/yyyy, tell Kettle that format. If you leave it to guess, Kettle might turn a phone number like 555-1234 into a number, losing the hyphen, or worse, treat a customer ID as a number and drop leading zeros so you can’t match it to your CRM. That’s the kind of small mistake that causes huge headaches later, so take two minutes to set fields manually.
Now, let’s talk about common pitfalls we see with new users. First, connection timeouts. If your server is slow, Kettle will time out before grabbing large files. To fix that, go to the “Advanced” tab in the FTP Input step and crank up the timeout setting, or increase the connection retry count. We usually set retry to 3 times, so if the server glitches for a second, it doesn’t fail the whole transformation. Second, file permission errors. A lot of clients have FTP folders that only allow read access for their service accounts, so make sure your username has at least read permissions on the files you’re trying to pull. We had a healthcare client once that accidentally locked their FTP folder to only their internal IT team, and we spent an hour figuring out why Kettle couldn’t list files—always confirm permissions first. Third, duplicate files. If you run your transformation more than once, you don’t want to reprocess the same files. So add a step after FTP Input that checks a log table (or even a simple text file) to make sure you only grab files that haven’t been processed before. That’s a quick add-on we build into almost every client’s workflow now, avoids double-counting sales or leads.
Wait, another cool feature: you can pass dynamic variables to the FTP Input step, right? That means you don’t have to hardcode the server name or file mask. Like, if you have a monthly report that pulls files for the current month, you can use a variable like today() to generate the mask automatically—so you don’t have to edit the transformation every month. That’s a game-changer for recurring workflows. For example, sales_2024-$(month).csv, and Kettle will pull the right month’s files automatically. We use this for all our client’s monthly reporting transformations, saves so much time on manual edits.
Also, let’s cover SFTP vs FTP real quick, since that’s a common question. FTP is unsecure, so we only use it for internal servers that aren’t accessible over the public internet. SFTP is the secure version, encrypts all data transfers, so that’s what we recommend for all client-facing or public FTP servers. The setup for SFTP is almost identical, just pick SFTP instead of FTP in the General tab, enter the private key path if you’re using key-based auth, and you’re good to go. We’ve had zero issues with SFTP using Kettle’s FTP Input step, it works way better than a lot of older tools’ SFTP implementations.
Now, let’s walk through a real quick example to make this concrete. Say you need to pull weekly lead files from a marketing agency’s FTP server. Their server is ftp.marketingagency.com, port 22 (SFTP), username is client_lead_user, password we keep in Kettle’s metadata (never hardcode passwords in the step, use Kettle’s password vault—huge security tip). The files are named leads_2024-XX.csv where XX is the two-digit week number, stored in the /weekly_leads folder. The CSVs have a header line, columns are lead_id (string, 10 digits), name (string), email (string), sign_up_date (date, MM/dd/yyyy), and source (string).
Here’s what we’d set up in FTP Input: General tab: connection type SFTP, server ftp.marketingagency.com, port 22, username client_lead_user, password from vault. File tab: remote folder /weekly_leads, file mask leads_2024-* (so it grabs all 2024 lead files), last modified within last 7 days (since we run the transformation weekly). Content tab: file type CSV, delimiter comma, quote character ", skip 1 header line, encoding UTF-8. Fields tab: manually set lead_id to String (length 10), name to String, email to String, sign_up_date to Date (format MM/dd/yyyy), source to String. Save the step, run the transformation, and boom—you’ve got all the new leads pulled straight into your stream, ready to load into your CRM or marketing automation tool.
Wait, what if your files are not CSVs? Like fixed-width, where each column is a set number of characters. The FTP Input step handles that too, just pick “Fixed width” as the file type in the Content tab, then set the column widths. For example, lead_id is 10 characters, name is 50, so you define each width. That’s great for older systems that still use fixed-width files, no extra steps needed. And for Excel files? We usually suggest using the Excel Input step, but FTP Input works if you just need to pull the file first and then run Excel Input on it, or even parse it directly, though Excel is a bit finicky sometimes—stick to CSV when you can, it’s simpler and more reliable for FTP.
Now, let’s talk about best practices we’ve learned over hundreds of client projects. First, never hardcode credentials in the FTP Input step. Kettle has a built-in password vault (called the PDI Password Vault) that lets you store usernames and passwords securely, so no one can open your transformation and see the password. That’s non-negotiable for any client-facing work, especially for regulated industries like healthcare or finance. Second, test with a small set of files first. Don’t run the transformation on 6 months of files the first time—test with one or two to make sure the parsing, encoding, and field settings are right. We had a client that ran a transformation for a quarter of files without testing, and they messed up the date format, so all their leads had sign-up dates of 1970. Fixing that was a huge hassle, so test first. Third, log everything. Add a “Write to log” step or a database table step after FTP Input to log which files were processed, when, and how many records. That makes troubleshooting way easier if a run fails, because you can see exactly which files it tried to pull. Fourth, handle errors gracefully. If the FTP server is down, or a file is missing, don’t let the whole transformation crash. Add a “Filter rows” step or a “Try/catch” block in Kettle to catch FTP errors, log them, and send an email alert to your team so you can fix it without losing all your data.
As a Kettle provider, we see so many teams waste hours on manual file transfers every week, and the FTP Input step is one of those built-in tools that people sleep on. It’s powerful, flexible, and free with Kettle Community Edition, which is why we recommend it to almost all our small to medium clients, and even our enterprise clients use it for routine file pulls. If you’re still manually downloading files, opening them in Excel or Google Sheets, then loading them into your data warehouse, you’re wasting time that your team could spend on actual analysis, not data entry.
If you’re ready to stop messing with manual FTP grabs and automate your file workflow, we can help. Our team has built custom Kettle transformations for dozens of clients to connect to FTP, SFTP, and even cloud storage servers (like Amazon S3, which also works with Kettle, but that’s a different topic). We can help you set up the FTP Input step correctly, fix any parsing or connection issues, add error logging, and make sure your automation runs smoothly. Whether you’re a small team with 10 GB of data or a enterprise with terabytes of sales files, we’ve got the expertise to get your FTP workflow up and running fast. Get in touch with us to chat through your specific needs, no obligation, just real advice for your team’s ETL work.
Regular Citrus Juicer References:
Pentaho Data Integration (PDI) Documentation. (n.d.). FTP Input Step. Retrieved from official Pentaho PDI help repository.
Kettle Community Forum. (2023). Common Troubleshooting Tips for FTP/SFTP Connections in PDI. Retrieved from community.pentaho.com.
ISO/IEC 8859-1 Information Technology – 8-bit Single-Byte Coded Graphic Character Set for Western European Languages. (1998). International Organization for Standardization.
Ningbo Lanka International Trading Co., Ltd.
As one of the most professional kettle manufacturers and suppliers in China, we’re featured by quality products and low price. Please rest assured to wholesale high-grade kettle for sale here from our factory. Also, custom service is available.
Address: No.11 Dongqian Lake Area, Yinxian Avenue, Ningbo, China
E-mail: messi@lanka10.com
WebSite: https://www.lanka-10.com/
