SSIS Execute Process Task (PERL Script based) in Azure Data Factory

Question

SSIS Execute Process Task (PERL Script based) in Azure Data Factory

Krishna 40

Hello Team,

We have an SSIS package created including Execute Process Task (which is in control flow task). Execute Process task is placed in for each task. We have a folder in local machine (where SSIS Packages also deployed) where in list of files (files are of different format and are about 50 plus files in count). So, the purpose of Execute Process task is to call each file, parsing data and load it into Azure SQL Database. Since Execute Process task is in For each task, the loop run through as many times as the number of files. In Execute Process task we have used PERL script to call the file, process the data and load into Azure SQL Database.

We have followed the Azure best practices lifting and shift SSIS packages using SSISDB route. Created SHIR to connect to files folder and Perl script, created Catalog database to enable SSISBD to deploy SSIS package, created Azure-SSIS IR for integration joining both SHIR and Azure-SSIS and VNet in the configurations.

Challenge is, SSIS packages run successfully (in 90seconds) in on Prim virtual machine wherein we try to run same package in Integration Runtime (or ADF Pipeline Execute SSIS Package) it is taking forever and not even throwing error (7 hours and more). We check the log files but of no useful information to trace. We are not able recreate the pipeline in ADF so. Any alternative suggestions to identify root cause or to reconfigure is appreciated. Is ADF supports Execute Process Task which is calling PERL script. Are there any options we should check or considered to make this package execute successfully in Azure Data Factory.

Krishna

QuantumCache 20,366 Reputation points Moderator

2023-06-14T19:37:47.3866667+00:00

Hello @Krishna,

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
Krishna 40 Reputation points

2023-06-15T16:56:45.12+00:00

We tried at SSISDB level and SSIS as you suggested selecting verbose option. But we did not find any log related to performance. Each file taking same time but no improvement. We also try increasing nodes but of now improvement, when we look at the IR Usage metrics the node improvement is not showing any change. Please suggest if there any options we can look at.
QuantumCache 20,366 Reputation points Moderator

2023-06-19T18:24:41.2966667+00:00

Hello @Krishna,

Please send an email to azcommunity@microsoft.com with the below details, so that we can work closely on this matter.

Your Email Subject : Attn Satish Boddu

Your Email Body content must include the below:

Thread URL: Link to this thread.

Your Azure Subscription ID: <This is must to be provided to us>

2 answers

Your answer

QuantumCache 20,366 Reputation points Moderator

2023-06-14T19:37:47.3866667+00:00

Hello @Krishna,

Just checking if we are still connected on this discussion? Please let us know if you need to add more info so that we better assist you!
Krishna 40 Reputation points

2023-06-15T16:56:45.12+00:00

We tried at SSISDB level and SSIS as you suggested selecting verbose option. But we did not find any log related to performance. Each file taking same time but no improvement. We also try increasing nodes but of now improvement, when we look at the IR Usage metrics the node improvement is not showing any change. Please suggest if there any options we can look at.
QuantumCache 20,366 Reputation points Moderator

2023-06-19T18:24:41.2966667+00:00

Hello @Krishna,

Please send an email to azcommunity@microsoft.com with the below details, so that we can work closely on this matter.

Your Email Subject : Attn Satish Boddu

Your Email Body content must include the below:

Thread URL: Link to this thread.

Your Azure Subscription ID: <This is must to be provided to us>

Answer 1

QuantumCache 20,366 Moderator

Hello @Krishna

Thanks for reaching out on this forum,

Did you try running the package manually in the Azure environment to see if it runs correctly. And also check if there are any network or firewall issues that might be affecting the performance of the SSIS package in Azure Data Factory.

Sometimes the problem might be related to network latency, Or consider enabling more verbose logging on your SSIS package to see if you can capture more detailed information about what's happening.

Krishna 40 Reputation points

2023-06-19T16:48:19.1666667+00:00

Hi Sathish, great if you provide any updates.

Clarification - In general taking 30 seconds to process a file is acceptable as per best practices. If so can please share any resources or documents for us to refer.

Krishna.
QuantumCache 20,366 Reputation points Moderator

2023-06-21T04:03:02.67+00:00

Hello @Krishna,

Please send an email to azcommunity@microsoft.com with the below details, so that we can work closely on this matter.

Your Email Subject : Attn Satish Boddu

Your Email Body content must include the below:

Thread URL: Link to this thread.

Your Azure Subscription ID: <This is must to be provided to us>
QuantumCache 20,366 Reputation points Moderator

2023-06-22T21:33:26.2133333+00:00

Hello @Krishna,Just checking if we are still connected on this discussion?
Please click "Accept Answer". So that we can close this thread.

Answer 2

Krishna 40

Hello Satish,

We are not able to share the details to Microsoft support.

Appreciate for all your help providing inputs.

Please close this thread.

Sincerely,

Krishna.

Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-07-03T14:40:41.87+00:00

The only one who can close a thread is the user who asked the question by Accepting one of the answers posted. (But you cannot accept your own answer.)

Share via

SSIS Execute Process Task (PERL Script based) in Azure Data Factory

2 answers

Your answer