CurrentlyProcessedFileName not showing up

CurrentlyProcessedFileName not showing up

So, you've selected the 'Add Currently Processed Flat File Name Port" property, applied the changes and closed the window - and the port did not show up? Me too.

I opened up the Source Definition in mapping again and verified that the property is checked. Unchecked it and - surprise: I got a message saying that CurrentlyProcessedFileName is not present to be deleted!

Checked it again, clicked "Apply' and switched to "Ports" tab WITHOUT CLOSING the Edit Transformations window and voile! It's there! I clicked "OK" to close the window and... It's gone again!

If you can repeat the above and have some observations - please share by leaving a comment.

Solution... well - a workaround rather

I was not able to find the cause and solve it. However editing the Source Definition in Source Analyzer and checking this property (instead of overriding in Mapping Designer) made the CurrentlyProcessedFileName visible and available for processing.

Using lookup input ports in SQL Override for non-cached lookup

If you need to use lookup input ports in the SQL Overrride for non-cached lookup, you need to use ?name? notation, eg.:

SELECT LAST_NAME FROM EMPLOYEES WHERE EMP_CODE = ?in_emp_code?

Not a recognized ODBC scalar function option with OUTER JOIN in SQ

Getting a "Not a recognized ODBC scalar function option" error while trying to perform an OUTER JOIN on Source Qualifier transformation using User Defined Join property? Make sure you're not running the process with auto-generated SQL Query Override after using the {} (curly braces) syntax in User Defined Join :)


Worth reading:
Outer joins in MS SQL
Execution of ODBC Extensions to SQL

MS SQL Server - Exception vs RaiseError

Below is just an observation that needs further investigation.

It appears that while using ODBC to connect to MS SQL Server there's a difference between RAISERROR and an occurence of an exception.

Performing a SELECT 1/0 within a stored procedure called as Post-SQL query results in an error like:

Database driver error...
...
FnName: Execute Direct -- [Informatica][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Divide by zero error encountered.]

Now, this is perfectly fine. An error occured, the session has failed. But what if we need to store some audit information in a log table in case of error in stored procedure? It's possible with TRY...CATCH. Whenever an error occurs, the CATCH block is executed. We can log the information and do a RAISERROR afterwards to fail the session.

But the session never fails. There's no track of RAISERROR in session log, the session completes successfully.

UPDATE:

The issue is caused with returning any dataset. Any print statement or result set seems to cover the error. Even the information about number of affected rows. Hence, the solution is to:

• remove any SELECT / PRINT statements from within the Stored Procedure
• suppressing the information about affected rows by using the SET NOCOUNT ON

The mapping is potentially unsafe and cannot be imported.

So, you got this error while trying to import your mapping to Powercenter:

The mapping m_your_mapping is potentially unsafe and cannot be imported.

What is wrong and how can it be fixed? What's the root cause? Hard to tell - the error message does not explain a lot.

The Cause
Most likely the XML file has been altered outside Powercenter. It's quite common to export and to some edits in XML before importing. Some renaming, replacing some paths or parameters. However some transformations or mappings have this

CRCVALUE="123123123"

property. If the XML file has been altered, it's very much possible the CRC is no longer correct.

The Solution
While importing the XML try to use the "-s" option. It can't be found in the Informatica Command Reference (at least in all versions I've tried) but it seems to work. Instead of the usual command:

pmrep objectimport -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

try using

pmrep objectimport -s -i /pathtoyourxmlfile/m_your_mapping.xml -c controlFile.txt - l output.log

Remarks
This worked in my case. And I have no idea what the "-s" stands for and what actually happens here...

Hash in output header

When using Output Field Names as Header for a Flat File target, a hash symbol [#] is put as the first one in header.

And the result in the file looks like this:

To get rid of the hash sign, the following custom property has to be set:

MS SQL Server: Stored Procedure error not failing parent Workflow

Stored Procedure error not failing parent Workflow

There's an issue I'm investigating: I have a workflow with a stored procedure executed as a post-load. It fails without failing the parent workflow. I've decided to come up with a simple test that would narrow down the cause.

Stored procedure

Here's the stored procedure I'll use for testing:

CREATE PROCEDURE RaiseErrorTest AS
BEGIN
--PRINT 'started'
--SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

All it does is perform the RAISERROR that should fail the whole process. There are also two disabled statements - we'll get to those later.

Workflow

I've created a simple workflow that executes SELECT 1 on source and uses a FALSE filter, so nothing gets written to target. It also includes a Post-load stored procedure call to the above SP:

Test 1: Native connector, disabled statements before raising error

Ok, lets start it and see what happens:

Great, session has failed as expected raising the defied error. The workflow has failed.

Test 2: Native connector, enabled statements before raising error

Now, let's enable the two statements back:

ALTER PROCEDURE RaiseErrorTest AS
BEGIN
PRINT 'started'
SELECT 1
IF 1=1
BEGIN
RAISERROR ('The servers are busy at this time. Please try again later', 16, 1)
RETURN 1
END
PRINT 'Completed'
SELECT 2
END

Test 3: ODBC, disabled statements before raising error

Let's revert the stored procedure to have disabled lines 3 & 4 and try the ODBC connection:

Workflow failed as expected - please note the difference in error message due to using ODBC instead of SQL Server Native Client

Test 4: ODBC, enabled statements before raising error

Now for the final test: using ODBC connection for the stored procedure that runs some statements before raising error:

Session (and worflow) executed successfully! Great, right? Well, not quite... The stored procedure fails but this is not escalated to PowerCenter.

Conclusion

While this is handled correctly by Native SQL Server connector, any first result set returned by the stored procedure fools the ODBC into treating this a successful execution. Whatever happens later on is discarded and you may never know your stored procedure failed. 

Unfortunatelly I have no idea how to overcome this issue.