Load Multiple Text Files into SQL Server in Informatica

In this article, we will show you how to Load Multiple Text Files into SQL Server in Informatica with example. For this Informatica Load Multiple Text Files into SQL Server example, we are going to use Four Text files present in our local file system, i.e., D:\FILE SYSTEM TASK FOLDER\

Load Multiple Text Files into SQL Server in Informatica 1

Data present in the Employee1.txt file

Load Multiple Text Files into SQL Server in Informatica 2

Data present in the Employee2.txt file

Load Multiple Text Files into SQL Server in Informatica 3

Below screenshot will show you the data in the Employee3.txt file

Load Multiple Text Files into SQL Server in Informatica 4

Data present in the Employee4.txt file

Load Multiple Text Files into SQL Server in Informatica 5

Data in the Employee file

Load Multiple Text Files into SQL Server in Informatica 37

And the Destination table is Multiple File Destination. As you see, it is an Empty table

Load Multiple Text Files into SQL Server in Informatica 6

Load Data From Multiple Text Files to SQL Server in Informatica

Before we start configuring the Informatica to Load Data from multiple Text Files (or Flat Files) to SQL Server. First, connect to Informatica repository service by providing the Informatica Admin Console Username and password you specified while installing the Informatica Server.

Step 1: Create an Informatica Source Definition to Load Multiple Text Files into SQL Server

Once you connected successfully, Please navigate to Source Analyzer to define your Sources. To load data from multiple files, we have to provide the source definition that is common for all the text files. For this, we are using Employee1, but you can try any one (Employee2, 3, or 4).

To load the data present in our local file system, Please navigate to Source menu and select the Import from File.. option

Load Multiple Text Files into SQL Server in Informatica 7

Once you choose the Import from File.. option, a new window called Open Flat File opened. Please select the Employee1.txt file from your local file system. I suggest you refer Flat File Source in Informatica article to know the following steps.

Load Multiple Text Files into SQL Server in Informatica 8

Once you click the Ok button, a new pop up window called Flat File Import Wizard will open. Here, our text separated by comma delimiter, so we are selecting the Delimited option. Next, we check marked the Import filed names from the first line option. Because our text file contains column names as the first line

Load Multiple Text Files into SQL Server in Informatica 9

Under the Delimiter section, we are selecting Comma. And under the Text Qualifier section, we are selecting No quotes because our flat-file doesn’t have any quotes.

Load Multiple Text Files into SQL Server in Informatica 10

Here, we can edit the Column Name, Data type, Length or Precision, Scale, and Width. Please change the length, width, or scale as per your requirements.

Load Multiple Text Files into SQL Server in Informatica 11

From the below screenshot, you can see our newly created Flat File source in Informatica

Load Multiple Text Files into SQL Server in Informatica 12

Step 2: Create an Informatica Target Definition to Load Multiple Text Files into SQL Server

Please navigate to Target Designer to define the Target. In this example, we use the existing SQL table as our target definition. So, go to the Targets menu and select the Import from Database.. option.

Load Multiple Text Files into SQL Server in Informatica 13

Please select the ODBC connection that will connect the Informatica with the SQL Test Database. To create a new one, please refer to the Informatica ODBC Connection article.

From the below screenshot, we are selecting the Multiple File Destination table from our SQL Test database. You can refer to Create Target table using Source Definition.

Load Multiple Text Files into SQL Server in Informatica 14

Now you can see the target table with required column names.

Load Multiple Text Files into SQL Server in Informatica 15

Step 3: Create Mapping to Load Data From Multiple Text Files to SQL Server

To create a new mapping, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.

Load Multiple Text Files into SQL Server in Informatica 16

It opens the Mapping Name window to write a unique name for this mapping. Let me write m_load_multifiles_to_sql and click OK button.

Load Multiple Text Files into SQL Server in Informatica 17

Drag and drop the Employee1 source definitions from Sources folder to the mapping designer. Once you drag the source, the Power Center designer automatically creates the Source Qualifier Transformation for you.

Load Multiple Text Files into SQL Server in Informatica 18

Next, Drag and drop the target definition (Multiple File Destination) from Targets folder to the mapping designer. Next, connect the source qualifier with the target definition using the Autolink.. option.

Before we close the Mapping, Let us Save, and Validate the mapping by going to Mapping Menu bar, and select the Validate option.

Load Multiple Text Files into SQL Server in Informatica 19

Step 4: Create Workflow to Load Data From Multiple Text Files to SQL Server

After we finish creating the Informatica Load Multiple Text Files into SQL Server Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create workflow.

In this example, we will create the Workflow manually. To do so, Please navigate to Workflows Menu and select the Create option.

Load Multiple Text Files into SQL Server in Informatica 20

It opens the Create Workflow window. Please provide the unique name (wf_load_multifiles_to_SQL) and leave the default settings.

Load Multiple Text Files into SQL Server in Informatica 21

Once we created the workflow, our next step is to create a session task for our mapping.

Step 4(a): Create Session to Load Data From Multiple Text Files to SQL Server

There are two types of sessions in informatica:

For this Informatica Load Multiple Text Files into SQL Server example, we are creating a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option

Load Multiple Text Files into SQL Server in Informatica 22

Please provide a unique name (s_load_multifiles_to_sql) for this session.

Load Multiple Text Files into SQL Server in Informatica 23

Once you click on the Create button, a new window called Mappings will open. Here you have to select the mapping (i.e., m_load_multifiles_to_sql) that you want to associate with this session.

Load Multiple Text Files into SQL Server in Informatica 24

Please link the Start Task and the Session Task.

Load Multiple Text Files into SQL Server in Informatica 25

Double click on the Session task will open the Edit Tasks window. Within Properties Tab, we have configured the $Target connection value. This property stores the relational source information in the $Target variable.

Load Multiple Text Files into SQL Server in Informatica 26

So, click on the Arrow we marked above, and select the SQL Test as the target information.

Load Multiple Text Files into SQL Server in Informatica 27

Within the mappings tab, we have to configure the Source and Target Connections. First, let us set the Target connection by clicking on the FileDestination.

Within the Connections, click on the Arrow button beside the Relational type, and Use Connection variable that we created in our previous step, i.e., $Target

Load Multiple Text Files into SQL Server in Informatica 28

You can change the Target Load type from Bulk to Normal as per your requirement.

Load Multiple Text Files into SQL Server in Informatica 29

Now, we have to configure the Source Connection. Please click on the SQ_Employee1 present in the Sources folder. Please change the Source Filetype option from Direct to Indirect.

Load Multiple Text Files into SQL Server in Informatica 30
  • Source File directory: Informatica will use the default directory, but you can change the directory by giving the full path. Here we are changing the file directory to D:\File System Task Folder\
  • Source filename: Please specify the file name where you placed the File names along with the extension (Employee.txt).
Load Multiple Text Files into SQL Server in Informatica 31

Next, navigate to Workflows Menu and select the Validate option to validate the Workflow. Now, Let me start the Workflow by selecting the Start Workflow option in Workflows Menu.

Load Multiple Text Files into SQL Server in Informatica 32

Let us open the SQL Server Management Studio to check whether we successfully transfer the data from multiple text files or not.

Load Multiple Text Files into SQL Server in Informatica 33

Let me alter the Employee text file. It means, Informatica has to load data from Employee1.txt file, and Employee4.txt file

Load Multiple Text Files into SQL Server in Informatica 34

Please checkmark the Truncate Target Table option to delete the existing records from the Target table, i.e., Multiple File Destination. I suggest you to refer SQL Truncate to understand the concept.

Load Multiple Text Files into SQL Server in Informatica 35

Let us open the SQL Server Management Studio. As you can see from the below screenshot, Informatica loaded the data from Employee 1 and Employee 4 text files.

Load Multiple Text Files into SQL Server in Informatica 36