Workflow hangs during data transformation and CSV generation

[iusa88f]

The initial workflow is executed without any problems, but when it is run again to perform an update and insert of data, it gets stuck.

The operation is as follows: data is extracted from a view in a historical database and a CSV is generated, which is loaded into a stage table. Then, a SQL query is executed where the data is transformed, which is inserted into a CSV and then, using the Bulk load into MSSQL component, it is inserted into a fact table.

It gets stuck in the step where it performs the transformation and generates the CSV, which never finishes generating.

[liosc]

Hi. Does your stream branch out and then get back in one branch? In this case it could be related to the row buffer. The last step before the branch sends a number of rows based on the buffer (default is 10000) and then waits for all the receiving transforms to finish their jobs with that before sending another batch. If the total number of rows is greater than the buffer and one of the receiving transforms needs all the rows to finish, the pipeline keeps waiting forever.

[bamaer]

a screenshot of the pipeline would definitely help.

[iusa88f]

Hello! Thanks for your prompt response.
If the flow is divided into two branches, one that does the transformation and loads the CSV, returns it, and loads the CSV into the table.

[dmainou]

You are trying to do too much.
Split that pipeline into a workflow with distinct pipelines. One that creates the csv and one that reads the csv and loads it into the db.
Likely, the os is not letting hop read the file because hop itself is writing to the file.

Remember, inside a pipeline, everything happens at the same time.