AETLE hangs when trying to open a transformation

Didn't know you could do that.
Here it is - I've edited it because the SQL statement had many fields in it but I don't think it will change the meaning.
Looks like my assumption was correct - it reads from a SQL DB and writes to 2 files. One has entries in it and one does not

Information 11/1/2021 1:17:10 AM Starting Transformation...
Information 11/1/2021 1:17:10 AM Map Name: JCK - Get Prospect Events for Unify Export
Information 11/1/2021 1:17:10 AM Author:
Information 11/1/2021 1:17:10 AM Version:
Information 11/1/2021 1:17:10 AM Description:
Information 11/1/2021 1:17:10 AM Preparing Writers...
Information 11/1/2021 1:17:10 AM Writer: {ProspectEventToUnify} Connection Type is: Text
Information 11/1/2021 1:17:10 AM Writer: {ProspectEventToUnify} Writer { ProspectEventToUnify } Is Ready
Information 11/1/2021 1:17:10 AM Writer: {ProspectEventToUnify} Connection Type is: Text
Information 11/1/2021 1:17:10 AM Writer: {ProspectEventToUnify} Writer { ProspectEventToUnify } Is Ready
Information 11/1/2021 1:17:10 AM All Writers are Ready
Information 11/1/2021 1:17:10 AM Processing Data...
Information 11/1/2021 1:17:10 AM Reader:{JCK - Utility DB} Connection Type is: MS Sql Server
Information 11/1/2021 1:17:10 AM Reader:{JCK - Utility DB} Source Select Statement: SELECT <field>
,<other fields>
FROM <table>
where <condition>
Information 11/1/2021 2:07:23 AM Reader:{JCK - Utility DB} Using Bulk Fetch
Information 11/1/2021 2:07:30 AM 1 Target file: <filepath>.txt
Information 11/1/2021 2:07:30 AM Reader:{JCK - Utility DB} Read: 31 Records/Lines/Messages (in total)
Information 11/1/2021 2:07:30 AM Reader:{JCK - Utility DB} Records/Lines/Messages per second: 0
Information 11/1/2021 2:07:30 AM Reader:{JCK - Utility DB} Rejected: 0 Record(s)
Information 11/1/2021 2:07:30 AM Reader:{JCK - Utility DB} Time Taken: 00:50:19
Information 11/1/2021 2:07:30 AM Writer: {ProspectEventToUnify} Wrote : 31 Line(s)
Information 11/1/2021 2:07:30 AM Writer: {ProspectEventToUnify} Wrote : 0 Line(s)
Information 11/1/2021 2:07:30 AM Transformation Completed

You might have issues with SQL server access rights.

the agent is using user "John Smith" to run the transformation
John Smith has all required access rights to the SQL server

You are running AETL as a different user.
This user has no access rights to the SQL server = It takes forever to open transformation.

Find SQL server connection this particular transformation is using and test it

As my user I have tested the connection that the transformation uses to retrieve data (JCK - Utility DB) and it connected fine.
There is a service user which runs the job agent but it is not possible to connect to the box interactively as that user so I can't check it directly.
There are other transformations which also use this connection and which are also scheduled.
I just checked the logs for one of them and it wrote over 2200 records within a couple of seconds so I don't think there is a fundamental problem with the service user using that connection. I am also able to view/edit this transformation.

The issue I have is that I cannot view or edit the transformation that I mentioned first. When I double click on it in the object explorer or R click/edit AETLE always hangs. Is there anything else I can do to find out why this is happening? I can find the transformation in the repository DB but the object data and parameters are obfuscated. I am guessing that it is somehow corrupted and that is why it won't open so is there any way of telling if this is the case in the repo DB?

We understand the issue but unfortunately not enough logs, screenshots were provided so we can point the exact problem.

We have to guess

Let say for example you have a very complex SQL which, takes a very long time to execute.

select * from table

So you open the transformation and it hangs because SQL is very slow.
In this case, we recommend our users use global variables.

set global variable to <condition> 1=2
and within the package set it to <condition> to 1=1

select * from table where <condition>

so when you open transformation the following SQL is executed
select * from table where 1=2

when you run it for the package
select * from table where 1=1.

the log file you provided has the following line
where <condition>
You may be using this approach or maybe not because the log file was modified.

It is possible to save packages and transformations into the file.
Unfortunately, they were not proved to us so it is very difficult to provide support.
Package screenshots will help as well

Hi Peter
Thanks for getting back to me. I do appreciate that supporting a query like this is always going to be difficult. If I can provide you with more information to help you then please ask for it and I will do my best (but please understand that I have only been using AETLE for a few weeks now and I am not familiar with how it all works yet).

With regards to the condition in the query it is here:
where [prospect_created_date] > (select ExportTime from SyncStatus where Process = 'BV_PROSPECT_EVENT_TO_UNIFY')
So, as you can see, it does not use global variables.
Could you explain why they would help in terms of opening the transformation in AETLE please? Does that SQL get executed when the transformation is opened? If not then I don't understand why the complexity of it would make any difference.

Here is a screen shot of the package which contains the transformation.



From the log that I sent earlier it looks like the transformation is trying to create 2 files.
The first FTP step uploads one of those files and the second step uploads the second file.
The SQL step updates a process table to show when this last ran and the Delete step deletes the files that have been uploaded.

Please let me know if you need any more information

Thanks

Does that SQL get executed when the transformation is opened?

Yes, this is correct.

(Assuming that you are working with an SQL server you can see the actual SQL being executed using the SQL Server profiler)