Load Data From Text File to SQL Server in Informatica

In this article, we will show you how to Load Data From Text File to SQL Server in Informatica with an example. For this Informatica Load Data From Text File to SQL Server example, we are going to use the below show text file data (Employee table)

Load Data From Text File to SQL Server in Informatica 1

and the Destination table is File Destination. As you can see, it is an Empty table

Load Data From Text File to SQL Server in Informatica 2

Load Data From Text File to SQL Server in Informatica

Before we start configuring the Informatica to Load Data from Text File (or Flat File) 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 a Source Definition for Informatica Load Data From Text File to SQL Server

Please navigate to Source Analyzer to define your Sources. As we said before, we are using the Employee table present in the local File System as our source definitions. So, Please navigate to the Source menu and select the Import from File..

Load Data From Text File to SQL Server in Informatica 3

Once you select the Import from File.. option, a new window called Open Flat File will open. Please select the Employee.txt file from your local file system and click the Open button. I suggest you refer Flat File Source in Informatica article to under the following steps.

Load Data From Text File to SQL Server in Informatica 4

Once you click the Open button, a new pop up window called Flat File Import Wizard will open.

  • Choose the file type that best describes your data: Please specify how your column values are separated (either Delimited or Fixed Width) option. Our text separated by comma delimiter, so we are selecting a Delimited option.
  • Import filed names from the first line: This option allows us to import the column names from the first line of a text file. If your text file contains column names as the first line of data like our example file then checkmark this option
Load Data From Text File to SQL Server in Informatica 5

Step 2:

  • Under the Delimiter section, please select the delimiter used in the text file. Our text separated by comma delimiter so, we are picking Comma.
  • Under the Text Qualifier section, we are selecting No quotes because our flat-file doesn’t have any citations. Please change as per your requirements.
Load Data From Text File to SQL Server in Informatica 6

Here, we can edit the Column Name, Data type, Length or Precision, Scale, and Width. In general, we don’t do that, but if you want, you can do it.

Load Data From Text File to SQL Server in Informatica 7

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

Load Data From Text File to SQL Server in Informatica 8

Step 2: Create Target Definition for Informatica Load Data From Text File to SQL Server

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

Load Data From Text File to SQL Server in Informatica 9

Please select the ODBC connection that connects the Informatica with the SQLTest Database. To create a new one, please refer to the Informatica ODBC Connection article.

From the below screenshot, you can observe that we are selecting the File Destination table from our SQLTest database. You can refer to Create Target table using Source Definition to understand the target definition

Load Data From Text File to SQL Server in Informatica 10

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

Load Data From Text File to SQL Server in Informatica 11

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

To create a new mapping for Informatica Load Data From Text File to SQL Server, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.

Load Data From Text File to SQL Server in Informatica 12

It opens the Mapping Name window to write a unique name for this mapping (m_load_text_to_SQL) and click OK button.

Load Data From Text File to SQL Server in Informatica 13

Drag and drop the Employee source definitions from the Flat File Sources folder to the mapping designer. Once you drag the source, the PowerCenter designer automatically creates the Source Qualifier Transformation for you.

Load Data From Text File to SQL Server in Informatica 14

Next, Drag and drop the target definition (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 Data From Text File to SQL Server in Informatica 15

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

After we finish creating the Mapping, we have to create the workflow for it. Power Center Workflow manager provides two approaches to create a workflow.

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

Load Data From Text File to SQL Server in Informatica 16

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

Load Data From Text File to SQL Server in Informatica 17

Once we created the Informatica Load Data From Text File to SQL Server workflow, our next step is to create a session task for our mapping.

Step 4(a): Create Session to Load Data From Flat File to SQL Server in Informatica

There are two types of sessions in Informatica:

For this Informatica Load Data From Flat File to SQL Server example, we created a Non-reusable Session. To create Non-reusable Session, Please navigate to Tasks Menu and select the Create option as shown below.

Load Data From Text File to SQL Server in Informatica 18

Please provide a unique name for this session (s_load_text_to_sql)

Load Data From Text File to SQL Server in Informatica 19

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

Load Data From Text File to SQL Server in Informatica 20

Please link the Start Task and the Session Task.

Load Data From Text File to SQL Server in Informatica 21

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 Data From Text File to SQL Server in Informatica 22

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

Load Data From Text File to SQL Server in Informatica 23

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 before, i.e., $Target

Load Data From Text File to SQL Server in Informatica 24

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

Load Data From Text File to SQL Server in Informatica 25

Now, we have to configure the Source Connection. Please click on the SQ_Employee present in the Sources folder.

Load Data From Text File to SQL Server in Informatica 26
  • Source Filetype: Whether you are using the Direct source or not. Indirect means, Source is from Multiple text Files.
  • 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 Examples\
  • Source filename: It automatically detects. If not, please specify the file name along with the extension
Load Data From Text File to SQL Server in Informatica 27

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

Load Data From Text File to SQL Server in Informatica 28

Once you select the Start Workflow option, Workflow monitor opened to monitor the workflow. From the below screenshot, our Informatica Load Data From Text File to SQL Server workflow executed without any errors.

Load Data From Text File to SQL Server in Informatica 29

Let us open the SQL Server to check whether we successfully transfer the data from a source or not.

Load Data From Text File to SQL Server in Informatica 30