In this short video, I will show you how you can preview and import a set of flat files into SQL Server using DataZen.

Some of the features we will see include:

• automated data type discovery
• automated target schema creation
• manual or scheduled data import
• automated ingestion of new files
• historical data ingestion report

Let's take a quick look at our files. We will import a list of firestations from Florida and Georgia first. These files were taken from public records.

First, we will create a Drive Connection to this folder. Note that we could be creating a Drive Connection to an Azure endpoint, an AWS S3 bucket or an FTP site.

Now, let's define a Data Sync Job. In this case, the job will have a Data Source and a Target System. Let's select the FIRESTATIONS connection.

We need to specify a file pattern, and a format. The CSV format provides additional options. We choose to only load new/updated files, indicate the first row contains headers, uses quoted identifiers and a COMMA delimiter.

We need to click PREVIEW to make sure the data looks right.

Let's choose the FFID and HQ Zip as the unique identifier. We could choose a specific schedule to check for new files, but let's keep this a manual run. Note that you can also make an HTTP request to start the job.

In the Target System tab, let's select an existing database connection: SQL2017. We will type a new table name in the Target Table field, and click on the Run Table Creation Script button. This screen detects the fields and data types of the source file and allows us to create the target table directly.

Once the job is created, let's start it manually. About 900 records were detected across two files and imported in to the database. We can inspect the data using SQL Server Management Studio.

Let's add a file into the folder being monitored by the job: these are firestations from Alabama. Let's run the job again, and we can see that another 800 records were identified. If we run the previous SQL statement, we can see a new total at the bottom right.

Back in the manager application, we can look at the job history. There are two job execution entries, showing us how many records were extracted from the CSV files, and the number of records imported. The graph shows us a visual of the same information.

Thank you for your time. Feel free to reach out to us for more information.