Linda Lin

Problem Description: Several days ago when I ran a PowerCenter session, I encountered an issue of populating column value of money data type from one Microsoft SQL Server table to another. For example, the original source value is 1234.1250, but after I loaded it to another table, it became 1234.1136. In Informatica, the column data […]

Scenario We want to create a target flat file name with the current system date. Solution 1 The first method is renaming the target file name in the post session command as bellow: Example: The following command will rename the file in the format filename_MMDDYYYY.out in Windows: move $PMTargetFileDir\filename $PMTargetFileDir\filename_%date:~4,2%%date:~7,2%%date:~10%.out

Solution Writing column names in the first row of a flat file target using PowerCenter (version 8.1 or update) session, do the following: Edit the session with the flat file target in Workflow Manager. Select the Mapping tab. Select the target file. Scroll to the Header Options attribute. Select Output Field Names from the attribute list. NOTES: By default, this option […]

SQL Server 2012 introduces new analytical functions LEAD () and LAG (). These functions accesses data from subsequent row (for lead) and previous row (for lag) in the same result set without the use of a self-join. The syntax for the Lead and Lag functions is: Note that: −        “Partition by clause” is optional, “order […]

Duplicate records are occasionally found in source data. Due to primary key constraints on a target database, only one version of a duplicate source record should be loaded into the target. The following methods demonstrate some of the most efficient ways to deal with duplicate source data. 1. Aggregator Transformation When to use Any time […]

Problem: Following error occurs when a Common Table Expression (WITH) has been used as SQL override, when connecting to SQL database: RR_4035 : SQL Error [Microsoft OLE DB Provider for SQL Server: Statement(s) could not be prepared. SQL State: 42000 Native Error: 8180 State: 1 Severity: 16 SQL Server Message: Statement(s) could not be prepared. Microsoft OLE DB Provider for SQL […]

Target update override is also like source qualifier override. It is useful to update the target without using the update strategy transformation. You can find the Target Update Override option in the target definition properties tab. The syntax of update statement to be specified in Target Update Override is UDATE TARGET_TABLE_NAME SET TARGET_COLUMN1 = :TU.TARGET_PORT1, […]

We can use FOR XML PATH to combine rows.  For example, have the following data:   And I want to combine the value of column SRC_ID_SEED from all rows to a single row as:   We can achieve this by using FOR XML PATH(): select stuff((select ‘,’+cast(src_id_seed as varchar(20))          from DATA_SOURCES          FOR XML PATH ( […]

  Scenario: Data source is a flat file and we want to load this data into the target. The problem is the source file name keeps on changing.  Here we go: Use a pre-session script that creates a file list. Windows dir /b part_of_file_name*.txt > FILELIST_NAME.LST UNIX ls part_of_file_name* > FILELIST_NAME.LST                 In Workflow Manager […]

A variable defined in a parameter file cannot be used in a post-session command alone. It must be used along with other session properties like Output file directory or Source file directory for it to be used in the post-session command.

A variable defined in a parameter file cannot be used in a post-session command alone. It must be used along with other session properties like Output file directory or Source file directory for it to be used in the post-session command. Example Consider a variable $OutputFileRepSybase that is defined as /home/test in the parameter file. If this variable is used in a post-session command alone, as […]

Step 1. Create and configure the Excel worksheet. In Microsoft Excel do the following: Select the required rows to be read into PowerCenter. Select Insert > Name > Define (Formulas > Name Manager in Excel 2007). Enter a name for the range. Click OK. Save the worksheet.