A second thought I do have license of Redgate of data generator as in the toolbelt bundle. Never used this tool, but maybe it can be used to replace columns'...

Thanks all.

For answering this question:  But do you really need the whole production database in  none prod?  Could you get away with using a blank schema and using a data...

Thank you both.

The product is good, but it is expensive. Not sure if we can get approval to have budget for that.

Any other recommendations?

Thanks

• This reply was modified 2 years, 9 months ago by  sqlfriend.

Yes, I totally understand the user cannot run the select query, because he doesnot have permissions on Production schema.

It is the stored procedure permissions that confused me. But Andy's answer:...

Thanks for your recommendations. What I can say is the .exe is calling our local web application that calls vendor's web service end points. Vendor side web service is the...

• This reply was modified 3 years, 2 months ago by  sqlfriend.
• This reply was modified 3 years, 2 months ago by  sqlfriend.

Yes, the step is execute process task that has a executable C:\_SSISFILES\Miscellaneous\eLinkUtility\Transportation.ETL.VersaTransToStaging.exe

The executable call the web service that has endpoints.

Yes,  dev and test servers hitting the same end point for the services, but the web services are hosted on two web servers, one is for dev web server, the...

unfortunately it is not something simple that T-SQL can handle. There are complicated logic to calculate stop corners according to time and direction of the bus etc, that is why...

SSIS only has one execute task that  calls the web service.  I run today, there are 2000 routes to process, it runs about 8 hours on dev, and 10 hours...

• This reply was modified 3 years, 2 months ago by  sqlfriend.

I did see in the .net code the route list is passed in batch to the application, but when it process detailed information in each route, it has to process...

• This reply was modified 3 years, 2 months ago by  sqlfriend.

All the steps in ETL runs pretty quick except this only task called execute process task which calls a web application .exe which is a wrapper that actually called vendors...

Yes, I did analyze the execution log, the slowess happened on each route processed for each stop location.

all this web service is to process each route one by one for stops and students, and street corners. After one route, process another one. It repeats same process for...

Thank you, yes option 1 is my preferred too, thanks for explaining that clearly.

Thank you, yes,  I get to know sysprosses result the same as sp_who2.

But if I run sp_whoIsActive, I click during the lastbatch time, but no records shows.

So I am wondering...