In this article, we will show you, How to Load Data From Text File to SQL Server in Informatica with an example. For this example, we are going to use the below show text file data (Employee table)
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
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 credential.
TIP: Here you have to provide the Admin Username and password that you specified while installing the Informatica Server.
Step 1: Create a Source Definition
Once you connected successfully, Please navigate to Source Analyzer to define your Sources. As we said before, we are using Employee table present in the local File System as our source definitions. So, Please navigate to Source menu and select the Import from File.. option as shown below
Once you select the Import from File.. option, a new window called Open Flat File will be opened as shown below. 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.
Once you click the Open button, a new pop up window called Flat File Import Wizard will be opened as shown below.
- 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 is separated by comma delimiter as we shown earlier 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 text file. If your text file contains column names as the first line of data like our example file then checkmark this option
Within Step 2:
- Under the Delimiter section, please select the delimiter used in the text file. Our text is separated by comma delimiter so, we are selecting Comma.
- Under the Text Qualifier section, we are selecting No quotes because our flat file doesn’t have any quotes. Please change as per your requirements.
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.
From the below screenshot you can observe that, you can see our newly created Flat File source in Informatica
Step 2: Create Target Definition
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 as shown below.
Please select the ODBC connection that will connect the Informatica with the SQLTest Database. In order to create a new one, please refer 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 process of creating a target definition
Now you can see the target table with required column names.
Step 3: Create Mapping to Load Data From Text File to SQL Server
To create a new mapping, Please navigate to Mappings menu in Menu Bar, and select the Create.. option.
This will open the Mapping Name window to write a unique name for this mapping. Let me write m_load_text_to_SQL and click OK button.
TIP: Please refer Informatica Mapping article to understand the procedure to create Mapping
Drag and drop the Employee source definitions from Flat File Sources folder to the mapping designer. Once you drag the source, Power Center designer will automatically create the source qualifier for you. I suggest you refer the Source Qualifier Transformation article
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. Please use the Autolink.. option to connect them.
Before we close the Mapping, Let us Save, and Validate the mapping by going to Mapping Menu bar, and select the Validate option.
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.
This will open Create Workflow window as shown below. Please provide the unique name (wf_load_text_to_SQL) and leave the default settings.
Once we created the workflow, our next step is to createa session task for our mapping.
Step 4(a): Create Session to Load Data From Flat File to SQL Server
There are two types of sessions in informatica:
For this example, we created a Non-reusable Session. In order to create Non-reusable Session, Please navigate to Tasks Menu and select the Create option as shown below.
Please provide a unique name for this session. Here, we are naming it as s_load_text_to_sql
Once you click on the Create button, a new window called Mappings will be opened. Here you have to select the mapping that you want to associate with this session i.e., m_load_text_to_sql.
Please link the Start Task, and the Session Task.
Double click on the Session task will open the Edit Tasks window. Within Properties Tab, we have configure $Target connection value. This property will store the relational source information in $Target variable.
So, click on the Arrow we marked above, and select the SQL Test as the target information.
Within the mappings tab, we have to configure the Source, and target Connections. First, let us configure the Target connection by clicking on the FileDestination.
With in 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
You can change the Target Load type from Bulk to Normal as per your requirement.
Now, we have to configure the Source Connection. Please click on the SQ_Employee present in the Sources folder.
- 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 will automatically detect. if not, Please specify the file name along with the extension.
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.
Once you select the Start Workflow option, Informatica PowerCenter Workflow monitor will be opened to monitor the workflow. From the below screenshot you can observe that, our workflow is executed without any errors.
Let us open the SQL Server Management Studio to check whether we successfully transfer the data from a source or not.
Thank You for Visiting Our Blog