SSIS Package Protection Level Batch Update

Change protection level in multiple SSIS packages at once.

If you are working on packages with multiple developers it is usefull to set the protectionlevel of a project and the packages to Protection Level 2 (Secure sensitive data with password). By default it is set to Level 1 (Secure sensitive data with user key), this clears the sensitive data if another user opens the package. When using level 2 other users can enter the password and keep sensitive data in the package intact.
The following steps need to be done to alter all packages at once:
  1. If you are using Source Controle make sure you check out the packages so you can edit the files.
  2. Close the Solution
  3. Navigate to you workspace directory where the dtsx files are stored that you need to change.
  4. Open a command prompt at the folder with the packages you want to modify
  5. Use the following command to set the property:
    • for %f in (*.dtsx) do dtutil.exe /file %f /encrypt file;%f;2;{strongpassword} /Q
      • The addition of /Q makes sure that it doesn’t prompt to overwrite the existing package
      • replace {strongpassword} with an actual password
      • The number needs to be changes to the level you want to set. (0,1,2,3)
  6. Open de .dtproj file in a text editor and Find & Replace the string:
    • Name=”ProtectionLevel”>1
      • Replace with: Name=”ProtectionLevel”>2
        • The number at the end of the string depends on the current and target level (0,1,2,3)
  7. Open the Solution again
  8. Change the protection level of the project to the same level as the packages.
  9. Build the Solution
  10. You are done!

Informatica Powercenter: How to get current variable value during session run using SetVariable()

During a project I was working on I needed to have a central variable in a mapping that I could set and also retrieve during the session run. There are 4 functions that manipulate mapping variables.

When you create a mapping variable you have to choose an aggregation function for this variable, you can choose MIN,MAX and COUNT.

  • When Choosing MIN the minimal value assigned during the session run will be returned at the end of the mapping and stored in the repository.
  • The MAX option will store the MAX value that has passed through the variable during the session run.
  • The COUNT option is used to create a count variable that can be incremented by the SetCountVariable() function.

I knew I could create a variable in the mapping and set it to the value I want with the SetVariable() function. The problem is to get the current value of this variable during your sessions run.

If you want to retrieve the current value of the mapping variable you cannot just simply refer to it as $$variable_name, this will return the initial value. If you want to get the current value of a mapping variable during your session run you need to pass the SetVariable() function the following parameters :

  • SetVariable($$variable_name,NULL)

This will return the current value in stead of the initial value.

Please keep in mind that if you want to reset your variable to the initial value on every session run you need to assign the initial value in a presession variable assignment.

 

Informatica Powercenter: Remove hash (#) character from header output in a flatfile target

When you have a flatfile as a target for your ETL Powercenter prefixes the header of the file with a hash (#) character when you choose to have Powercenter generate the header from the target definition port names. Usually you do not want this hash to be present in your file. The easiest way to get rid of this is to add a custom property to your session configuration.

Navigate to the ‘Config Object’ tab and add the following property.

RemoveOutputHeaderHash=Yes

RemoveOutputHeaderHash

Click image for larger view

 

How to enable Examine (Diagnostics menu) in Oracle eBS

To be able to use the examine option in Oracle eBS you need to set the following Profile settings :

  • Profile option name :

Hide Diagnostics menu entry

Value :

No

  • Profile option name :

Utilities:Diagnostics

Value :

No

After that switch responsibility or logout and login and you will have a new menu item under the “Help” menu.

To also have the “About this page” link in the OAF Pages also set the following profiles options:

FND: Diagnostics = “Yes”

Personalize Self-Service Defn = “Yes”

FND: Personalization Region Link Enabled = “Yes”

Disable Self-Service Personal = “No” (Only at Site)

 

 

ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated

When you encounter the following issue when creating a trigger :

Error report –
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [kqlidchg0], [], [], [], [], [], [], [], [], [], [], []
ORA-00604: error occurred at recursive SQL level 1
ORA-00001: unique constraint (SYS.I_PLSCOPE_SIG_IDENTIFIER$) violated
00603. 00000 – “ORACLE server session terminated by fatal error”
*Cause: An Oracle server session was in an unrecoverable state.
*Action: Log in to Oracle again so a new server session will be created
automatically. Examine the session trace file for more
information.

Try the following solutions :

ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:NONE';

Or in Oracle SQL Developer:

  1. Go to Tools | Preferences
  2. Select Database | PL/SQL Compiler
  3. Change the PLScope identifiers from All to None
  4. Click on Ok

Informatica Powercenter : Default Target load type

You can set the default Target load type for your sessions to ‘Bulk’ or ‘Normal’. This way you do not have to edit them all when the default is set to the wrong value 🙂

To do this open de Workflow manager and click on the Tools menu > Options  and then select Miscellaneous, you can select the default value there.

Informatica Powercenter : Convert String to timestamp with milliseconds

When you want to convert a String to a timestamp with milliseconds and you are only getting zero’s for milliseconds in your database make sure that the following option is disabled in the session (Config Object tab) :

2015-05-21 12_35_18-Edit Tasks

If this is enabled the milliseconds will be truncated.

You can use the following formula to convert a string to a timestamp with milliseconds :

to_date(‘2013-09-01 12.14.16.627000’,‘YYYY-MM-DD HH24.MI.SS.US’ )

 

Informatica Powercenter : SQL Hints in lookup SQL Override

By default you cannot use the following characters in an lookup sql override : ‘/*’

Because of this you cannot enter SQL Hints. You will get an error message when running your mapping. This is because the informatica parser doesn’t recognize these special characters.

To enable SQL hints in a lookup sql override you can set a custom property in the Integration Service in the Administrator Console.

  • Select the Integration Service.
  • Under the Properties tab, click Edit in the Custom Properties section.
  • Enter a new property
  • Name : LookupOverrideParsingSetting
  • Value : 1.
  • Click OK.

In version 9.5 there is no need to restart the IS.

Do check if other existing overrides still function as normal because i came accros an override that was going into error when we added the property. There was a TO_CHAR funtion in that query and it got messed up by informatica.

Another solution is to use a Source Qualifier as the source of your lookup. A SQ does support ‘/*’ characters by default.

Where to find Oracle Business Intelligence Applications (OBIA) Release 11.1.1.8.1 Documentation

You can find the most important OBIA documentation at the following locations :

Oracle Business Intelligence Applications (OBIA) Release 11.1.1.8.1 Documentation Library 

Major topics :

  • Install Guide
  • Configuration Guide
  • ETL Guide
  • Administrator’s Guide
  • Security Guide
  • Release Notes

OBIA 11g: Data Lineage Documentation for Oracle Business Intelligence Applications 11.1.1.8.1

BI Applications 11.1.1.8.1 Data Model Reference Guide

Major Topics :

The information provided in this document includes data modeling concepts, nomenclature, table and column descriptions, and star schema diagrams. This information is useful for understanding the functional areas that are mapped in the Oracle Business Analytics Warehouse.

BI Applications 11.1.1.8.1 Content Guide

Content Guide includes following topics for BIApps 11.1.1.8.1:

Section 1, “Oracle Business Intelligence Applications – Licenses and Products
Provides a list of all Products within Oracle Fusion BI Applications

Section 2, “Oracle Business Intelligence Applications – Dashboard
Provides a list of dashboards for each application along with their paths

Section 3, “Oracle Business Intelligence Applications – Dashboards and Reports
Provides a list of dashboards and reports for each application along with their exact paths

Section 4, “Oracle Business Intelligence Applications – All Reports
Provides a complete list of reports including those not exposed in any dashboard page

Section 5, “Oracle Business Intelligence Applications – Subject Areas
Provides a high level overview of the subject areas included in each application

Section 6, “Oracle Business Intelligence Applications – Facts & Dims by Application
Provides a list of fact and dimension relationships for each presentation catalog. The user can filter on a particular dimension table and find all its fact tables across presentation catalogs and vice versa

Section 7, “Oracle Business Intelligence Applications – Webcatalog Function Security
Provides a list of report name, path, associated users, duty/roles and permissions

 

Convert UTC datetime from SQL Server to local timezone (CET) in Oracle.

For a project at a customer I had to load data from a SQL SERVER database to a Oracle database. It turned out that the SQL SERVER database stored the date and time data in UTC. This became apparent when the data was reported via Business Objects and all the date time data was off by 1 or 2 hours depending on the daylight saving time. So how do we fix this ?

We cannot just add +2 hours (we are in +2 timezone) because this will fail when daylight saving is active.
It would be possible to keep track when daylight saving is active but that changes overtime and that would mean that you have to maintain the function.

So this is how i fixed it :

Step 1:

Create a connection in powercenter for your target database and set the connection attribute ‘Connection Environment SQL’ to :

ALTER SESSION SET TIME_ZONE=’UTC’

This will make sure that the data is written as UTC datetime stamp in Oracle and not as the local timezone.
If you need to convert from another timezone you need to enter that timezone instead of UTC.

Step 2:

Read the datetime column from SQL SERVER via powercenter and insert this data into a oracle column with datatype ‘Timestamp with local timezone’ using the connection defined above.

Step 3:

When you query the data from Oracle you need to make sure you set your session to (im my case) CET.
Because the datatype is ‘Timestamp with local timezone’ Oracle will internally convert the UTC datetime to CET. CET is also daylight saving time aware so the dates are converted correctly with or without daylight saving active.
When queried it will display the data in the local timezone (CET in this case).
If you are using Business Objects to read the data you can set your session in the universe and the report will show the correct data.

Example Daylight saving :

  • 18-08-2014 12:00:00 is entered in the application (daylight saving active so +1)
  • This is stored in SQL SERVER as 18-08-2014 11:00:00 (UTC)
  • Set writing session to UTC.
  • Insert into oracle as 18-08-2014 11:00:00 (UTC)
  • Set query session to CET
  • Query Oracle and 18-08-2014 12:00:00 will be displayed.(daylight saving active so +1)

 

Example No Daylight saving :

  • 01-01-2014 12:00:00 is entered in the application (Timezone +2)
  • This is stored in SQL SERVER as 01-01-2014 10:00:00 (UTC)
  • Set writing session to UTC.
  • Insert into oracle as 01-01-2014 10:00:00 (UTC)
  • Set query session to CET
  • Query Oracle and 01-01-2014 12:00:00 will be displayed.(daylight saving not active so +2)

 

Load more

By continuing to use the site, you agree to the use of cookies. more information

The cookie settings on this website are set to "allow cookies" to give you the best browsing experience possible. If you continue to use this website without changing your cookie settings or you click "Accept" below then you are consenting to this.

Close