tag:blogger.com,1999:blog-9109385660486825237Mon, 20 Apr 2026 22:09:47 +0000InformaticaScriptingAdministrationAutomationpmrepJob SchedulingwebserviceData_Integration_HubInformatica SequenceInformatica_10RepositorySAPaggregationemailRamblings on Informatica PowerCenter and SQLhttp://jeffkinzer.blogspot.com/noreply@blogger.com (J. Kinzer)Blogger25125tag:blogger.com,1999:blog-9109385660486825237.post-4950279489333641581Thu, 27 Apr 2017 16:41:00 +00002017-04-27T11:57:17.834-05:00In the Target Table session properties there is an option to Truncate existing data before loading. It is important to realize Informatica does not always use the TRUNCATE function to clear the table, sometimes a DELETE statement is issued, this decision is made at runtime depending on different factors, noted in the table below.   Depending on your needs, TRUNCATE may be what you want http://jeffkinzer.blogspot.com/2017/04/informatica-truncate-target-table.htmlnoreply@blogger.com (J. Kinzer)1tag:blogger.com,1999:blog-9109385660486825237.post-7071346070857908732Sun, 16 Oct 2016 02:28:00 +00002016-11-21T19:34:08.780-06:00Calling JDE Business Services (BSSV), aka Web Services, can be tricky and there is little information on Oracle's or Informatica's website showing how. In fact, importing the JDE CustomerManager.wsdl into an Informatica web service consumer creates a large confusing, near impossible to understand transformation. Here's a high level overview to create a usable JDE 9.0 Customer Manager BSSV, be http://jeffkinzer.blogspot.com/2016/10/web-service-consumer-for-jde-bssv.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-8762904786115058794Tue, 11 Oct 2016 14:44:00 +00002016-11-21T19:34:55.444-06:00Here's a solution to an obscure issue. A workflow/session completes successfully when ran manually but gives error LM_44127 Failed to prepare the task when ran from the scheduler. If you are using Versioning, ALL objects must be checked in. Use the option Versioning > Find Checkouts to see any checked out objects. Check in all objects. Also, IMPORTANT, if using shortcuts for sources, targets http://jeffkinzer.blogspot.com/2016/10/scheduled-workflow-fails-but-completes.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-1051419301038419737Wed, 24 Aug 2016 02:46:00 +00002016-11-21T19:35:13.696-06:00This page will be a running list of tips and workarounds for Data Integration Hub 10. Error: unable to create all staging tables for topic This error can occur if you have a reserved word as a column name. Session must be established for 'end' This error can occur if the Web Service Hub (WSH) is down. Log into the Informatica Console (not DIH console), check all the services. Restart anyhttp://jeffkinzer.blogspot.com/2016/08/data-integration-hub-errors.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-2814742879932981985Sun, 21 Aug 2016 01:10:00 +00002016-11-21T19:35:31.635-06:00Data_Integration_HubInformatica_10This issue was found in Informatica Data Integration Hub 10 on Windows Server 2012 using SQL Server 2012 as the Repository. The PowerCenter Workflow DX_ETL can abort in mapping m_SET_CURRENT_CUTOFF giving the errors: Timestamp parameters with a scale, must have a scale less than ten and a precision equal to 20 plus the scale. You specified a precision of 999 and scale of 9. Fractional second http://jeffkinzer.blogspot.com/2016/08/data-integraton-hub-dxetl-datetime.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-6282986279359524613Fri, 27 May 2016 16:09:00 +00002016-11-21T19:35:55.612-06:00I created this simple PowerCenter mapplet to format phone numbers. Informatica Developer (IDQ) may have a different or easier way to do it but I wanted something I could use in PowerCenter without the hassle of maintaining a separate object in IDQ and having to import it into PowerCenter. You can alter the mapplet to format other data like postal codes, SSNs, etc. Download the mapplet and givehttp://jeffkinzer.blogspot.com/2016/05/formatting-telephone-numbers.htmlnoreply@blogger.com (J. Kinzer)1tag:blogger.com,1999:blog-9109385660486825237.post-7334582872499865636Sun, 18 Jan 2015 16:37:00 +00002017-01-07T07:30:00.296-06:00SAPWhen extracting large tables from SAP, you can limit the amount of data selected by using Informatica parameters.  Here are two examples. Use a parameter to limit the extract to retrieve only a specified set of data. Your target uses a truncate and load scenario. Use parameters as in the example above but also use a persistent cache lookup on the target table. Append new rows to the table.http://jeffkinzer.blogspot.com/2015/01/limit-extracts-from-sap-tables-using.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-7936794643719948025Sun, 18 Jan 2015 16:19:00 +00002016-11-21T19:37:55.083-06:00AdministrationInformaticawebserviceHere are some notes on an Informatica PowerCenter 9.6.1 64 bit server install.  This also includes the setup of the Informatica Cloud Secure Agent (64) Most of the install went smooth.  However I want to list some of the problems I encountered. Something unique about this install is the PowerCenter server name was in this format: SERVERT.corpt.tginternaltest.dev.  There was http://jeffkinzer.blogspot.com/2015/01/diary-of-informatica-916-hf1-install.htmlnoreply@blogger.com (J. Kinzer)2tag:blogger.com,1999:blog-9109385660486825237.post-6254710880374994513Thu, 21 Jun 2012 01:00:00 +00002016-11-21T19:40:29.339-06:00AutomationInformaticaScriptingUsing Excel files as the source for Informatica mappings can be more trouble than it's worth. You have to setup a connection and name a range in the Excel document which can be difficult to maintain. I much prefer to read text or CSV files, they're simpler to work with and less error prone. I created a VB script for the purpose of converting Excel to CSV. You can use the script to convert anhttp://jeffkinzer.blogspot.com/2012/06/vbscript-to-convert-excel-to-csv.htmlnoreply@blogger.com (J. Kinzer)26tag:blogger.com,1999:blog-9109385660486825237.post-3809897928750434284Tue, 22 May 2012 01:32:00 +00002016-11-21T19:40:55.855-06:00AdministrationRepositoryHere are some SQL scripts you can use to query the repository. Most of these I got from an Informatica employee, others I put together. Use them as a base to create your own queries or reports. One word of caution, use a profile with READ ONLY permission to access the repository and NEVER update the repository using SQL unless advised to do so by Informatica support. The scripts use MS SQL http://jeffkinzer.blogspot.com/2012/05/querying-repository.htmlnoreply@blogger.com (J. Kinzer)5tag:blogger.com,1999:blog-9109385660486825237.post-1056425670733713492Sun, 29 Jan 2012 20:40:00 +00002016-11-21T19:41:49.016-06:00AutomationInformaticaScriptingHere’s the companion script for the “Using pmrep to export an Informatica folder” article. Use this script to import objects into PowerCenter. One use for scripting an import would be to schedule it during off hours, for example moving development code to production during off hours. You may also need to export some objects, then do a "mass change" or Find/Replace on the XML file, then export http://jeffkinzer.blogspot.com/2012/01/using-pmrep-to-import-informatica.htmlnoreply@blogger.com (J. Kinzer)13tag:blogger.com,1999:blog-9109385660486825237.post-6849729220941651152Thu, 01 Dec 2011 02:07:00 +00002016-11-21T19:42:12.724-06:00InformaticaTry this tip for managing PowerCenter connections. Parametrize connections and use one parameter file to define them. Use the one parameter file in all your workflows. This will bring consistency to development and make it easy when connection changes are required. Parameter files are also useful for defining “logic” related parameters or variables but define those in a separate parameter filehttp://jeffkinzer.blogspot.com/2011/11/managing-connections-using-parameter.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-6411891912198215001Sat, 09 Jul 2011 20:24:00 +00002016-11-21T19:43:27.659-06:00ScriptingHere's a companion tip to the "VBScript to convert CSV to XLS" post. This script will combine two Excels files into one workbook. The files will be treated as separate worksheets inside the workbook. The worksheet names will be the same as the file names. You may find this useful when an Inforamtica process outputs multiple flat files and you have converted them into native Excel format but http://jeffkinzer.blogspot.com/2011/07/vbscript-to-combine-multiple-xls-files.htmlnoreply@blogger.com (J. Kinzer)7tag:blogger.com,1999:blog-9109385660486825237.post-2761922934609104325Fri, 15 Apr 2011 02:21:00 +00002016-11-21T19:44:51.275-06:00InformaticaHere is a sample mapping to illustrate how the Transaction Control transformation can dynamically create multiple flat file outputs, each with a unique name and data set. The mapping reads a source table whose key is “Group Id”, and outputs a separate target file for each Group Id data set. The file names are also generated dynamically based on the Group Id. There are two “secrets” to the http://jeffkinzer.blogspot.com/2011/04/the-transaction-control.htmlnoreply@blogger.com (J. Kinzer)2tag:blogger.com,1999:blog-9109385660486825237.post-985535688972906250Sat, 12 Feb 2011 15:58:00 +00002016-11-21T19:45:37.490-06:00aggregationemailInformaticawebserviceInformatica allows options for sending email at the session level (on Session Success or Failure) but there is not a standard way to send email from the mapping. By using a web service (written in a third party language such as .Net or Java) to send email, you can generate email from a mapping based on conditional logic and include relevant "pipeline" data in the email body. An additional http://jeffkinzer.blogspot.com/2011/02/using-web-service-to-send-e-mail.htmlnoreply@blogger.com (J. Kinzer)5tag:blogger.com,1999:blog-9109385660486825237.post-3269663330284893052Tue, 11 Jan 2011 03:20:00 +00002016-11-21T19:46:52.583-06:00AdministrationAutomationJob SchedulingpmrepHere is a technique that can be used to unschedule workflows automatically or conditionally. This process uses the pmrep command to unschedule any workflows specified in a CSV file. I have a copy of this workflow scheduled on our development server using the scheduling option "Run on Integration Service initialization". I do this because any restart of the Informatica service will “re-schedulehttp://jeffkinzer.blogspot.com/2011/01/unschedule-workflow-script.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-6997879923682956998Mon, 01 Nov 2010 17:00:00 +00002016-11-21T19:47:20.554-06:00InformaticaHere is a reusable email task you can use for the “On Failure E-Mail” property for any Informatica session. If the session fails an email is sent to an address you specify. The content of the email is generic but you get all relative information about the session’s failure, plus the session log as an email attachment. Download the task and import it into a PowerCenter folder. Next set the “On http://jeffkinzer.blogspot.com/2010/11/session-failure-emails.htmlnoreply@blogger.com (J. Kinzer)1tag:blogger.com,1999:blog-9109385660486825237.post-2053931278182096123Mon, 04 Oct 2010 10:49:00 +00002016-11-21T19:47:49.194-06:00AdministrationAutomationpmrepScriptingNote: This tip is part of a series on administration scripts. These scripts automate the process to grant permissions to PowerCenter Folders, Connections and Query objects. In my company’s PowerCenter development environment, we allow each developer to access all folders and connection objects. Our security structure is designed so all developers are in a security group, these scripts grant http://jeffkinzer.blogspot.com/2010/10/assign-object-permissions-pmrep.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-7635242766256853082Sat, 04 Sep 2010 12:58:00 +00002016-11-21T19:48:15.318-06:00Informatica SequenceThe Sequence Generator transformation is great sometimes but I cannot find a way to reset the generated sequence based on external conditions. For example, when a value in the current row is different from the value in a previous row, I would like to reset the Sequence number. Using some information I found on Informatica’s Knowledge base, I created a reusable Expression that will increment or http://jeffkinzer.blogspot.com/2010/09/sequence-number-generation.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-1613172955590488157Sun, 01 Aug 2010 12:00:00 +00002016-11-21T19:48:32.484-06:00AdministrationAutomationInformaticaSummary:   This tip will show you how to augment the pmrep command's backup function with environment variables to dynamically create the backup file names.  I've included a visual basic script that can be used to delete old backup files from any folder.  The result is a script you can schedule on your Windows server for unattended repository backups. An examplehttp://jeffkinzer.blogspot.com/2010/05/using-script-to-automating-informatica.htmlnoreply@blogger.com (J. Kinzer)5tag:blogger.com,1999:blog-9109385660486825237.post-5135046659276687187Fri, 11 Jun 2010 00:48:00 +00002016-11-21T19:48:51.067-06:00InformaticapmrepScriptingSometimes you need to export the contents of a PowerCenter folder for offline storage. This post will show you how to quickly export a PowerCenter folder's contents using the pmrep command's ObjectExport function. The nice thing about this tip is I give you a script (download here) that will export a folder's contents as individual XML files and export one XML file containing all the folder's http://jeffkinzer.blogspot.com/2010/06/using-pmrep-to-export-informatica.htmlnoreply@blogger.com (J. Kinzer)41tag:blogger.com,1999:blog-9109385660486825237.post-4002839591290229605Thu, 03 Jun 2010 00:24:00 +00002022-10-07T08:34:15.179-05:00InformaticapmrepScriptingInformatica can easily output target data in comma separated value format (CSV) but sometimes you require output formatted in native Excel.  Here's a reusable Visual Basic Script that will convert a CSV file into a nicely formatted native Excel file. The script can be ran individually or as this tip will demonstrate, can be evoked from a PowerCenter Session's "Post-Session Success Commandhttp://jeffkinzer.blogspot.com/2010/06/vbscript-to-convert-csv-to-xlsx.htmlnoreply@blogger.com (J. Kinzer)30tag:blogger.com,1999:blog-9109385660486825237.post-6622942318291562154Mon, 10 May 2010 23:09:00 +00002016-11-21T19:54:29.747-06:00InformaticaThe "Dependences" report in PowerCenter is great for showing where objects (especially sources and targets) are used. Recently however, one of my team members almost deleted a Flat File source definition because it showed NO dependencies. After double checking they found where the Flat File object was used as a lookup!! Has anyone else had issues with the "Dependences" report not showing all http://jeffkinzer.blogspot.com/2010/05/powercenter-dependences-report.htmlnoreply@blogger.com (J. Kinzer)0tag:blogger.com,1999:blog-9109385660486825237.post-1980636503170288159Thu, 06 May 2010 01:39:00 +00002016-11-21T19:52:47.766-06:00InformaticaScriptingIf you frequently use scripts to run PowerCenter commands pmrep and pmcmd, make sure you are using server environment variables for repetitive command parameters. Benefits of using environment variables: Eliminate the need to change scripts when migrating from test to production. Reduce need to change scripts when upgrading to new version of PowerCenter. Better security: user id and passwords http://jeffkinzer.blogspot.com/2010/05/using-informatica-server-environment.htmlnoreply@blogger.com (J. Kinzer)18tag:blogger.com,1999:blog-9109385660486825237.post-1033489753561448488Fri, 30 Apr 2010 16:56:00 +00002016-11-21T19:52:24.988-06:00InformaticaJob SchedulingIf you need a workflow to run every day, but only during working hours, for example you need a workflow to only run if the time is between. 8:00 a.m. to 6:00 p.m., try this technique. Use a Decision task to compare a workflow's start time to a range of hours/minutes you specify. The general idea is, using a Decision task you compare the workflow start time with a predetermined time, then http://jeffkinzer.blogspot.com/2010/04/running-informatica-workflow-hourly-but.htmlnoreply@blogger.com (J. Kinzer)5