Please do not cross-post. Answers here please.

You gave us the queries, but please also show what they return (for each one) and also what you desire the 'combined' version to return.

sqlfriends (6/14/2016)

---

So I guess can view the results from report or more advanced to query the SSIS db.

Then what is that output file on the advanced tab?

Also I guess the...

Logs are stored in SSISDB itself.

The ability to run packages directly from T-SQL code is another.

There are some serious advantages.

Native logging.

Project-scoped parameters and connections.

Configuration from SSISDB environment variables.

Are ones which come immediately to mind.

sqlfriends (6/14/2016)

---

I take it back, thanks for your patience.

I tried again with SQL agent job using SQL service account, it works this time.

But if I run it in SSISDB catalog,...

michael.french 172 (6/14/2016)

---

Phil,

I changed the procedure to create the index after the initial insert, but I had to immediately change it back. The while loop is controlled by @@ROWCOUNT....

sqlfriends (6/14/2016)

---

I tried using SQL agent job. Get the same error.

If I run the package in package deploy mode that I saved on the file system, I don't have problem.

I...

michael.french 172 (6/14/2016)

---

Phil,

In normal processing, I completely agree with you on loading the table and then creating an index. However in this case, there is an n-level recursion inserting data...

sqlfriends (6/14/2016)

---

I have spent a lot of time to convert packages from package deploy mode to project deploy mode, and only find this is not working for UNC path, and...

michael.french 172 (6/13/2016)

---

Phil,

I totally agree with points 3 & 4. With points 1 & 5, they are crucial to handle the recursion efficiently as coded. As for point 2, since...

Luis Cazares (6/14/2016)

---

Change your UNION to a UNION ALL, unless you want to eliminate duplicates.

UNION will need to add a sort which is expensive.

If your queries have adequate indexes, I'd...

I'd be tempted to run both, in the same batch, with Include Actual Execution Plan turned on.

Then you'll see each query's relative cost percentage & might be able to identify...

Kerberos delegation may be the issue. See here.