0

Recently we have upgraded our sql server from 2008 to 2016. We have an ETL project that picks up information on an Oracle db, transforms it, and post it on a Data Warehouse hosted on this SQL Server 2016. The ETL project works fine on sql 2008. Meanwhile we are having problems on sql 2016. It seems that the ETL process stucks in the TRANSFORM stage. On this stage we have a list of stored procedures that are executed one by one. The ETL always stucks in the last procedure. For example: If I have procedures A, B, C, D then the process stucks in D procedure. If I remove D procedure then the process stucks in C procedure and so on. The ETL never goes to the LOAD stage.

Please can you help me with any hint on how can I resolve this issue?

Thanks, KLEDI

kledi sejko
  • 61
  • 5
  • Take the all request and responses from database procedure and connections using SQL Server Profiler. Which will gives you better understanding for, Where its getting stuck? – JERRY Sep 14 '18 at 07:53
  • Are you saying the package hangs or that it generates an error? Can you replicate this issue on a pre-prod environment? – Mazhar Sep 14 '18 at 08:22
  • @Mazhar: Tha package hangs and it does not generate any error at all (based on the etl logs). I have the same identical project on SQL 2008 and it works pretty fine. – kledi sejko Sep 14 '18 at 09:06
  • @JERRY: Thanks Jerry. I will install the sql profiler and see if it helps me. – kledi sejko Sep 14 '18 at 09:07
  • If you can replicate this on a Pre-prod environment then I would run the package with the steps that are hanging disabled and then investigate why the stored procedures that are hanging outside of the package – Mazhar Sep 14 '18 at 10:46
  • Hi Mazhar. The problem is that if I execute the steps manually outside the project they are being executed successfully. If I execute the whole project then it hangs on this specific step that I mentioned earlier. Now that I am analyzing the logs on SQL Profiler I see that the sp_reset_connection procedure (which is a system procedure) is being executed multiple times. Is that normal? – kledi sejko Sep 14 '18 at 14:02
  • Please, can anybody help? The projects still hangs. I have also made changes based on these answers: https://stackoverflow.com/questions/15508594/ssis-data-flow-task-hangs-on-excecution-of-pre-excecute-phase but no success. Thanks. – kledi sejko Sep 21 '18 at 10:53

0 Answers0