0

I have a master package which execute 3 packages. All sub-packages has data flow task in which it extract data from excel file which is placed on a SHARED LOCATION, and load it to SQL SERVER database table. Each sub package load different file to different table. No file or table is used in more than one package.

In BIDS we never face this issue, it runs smoothly in BIDS.

But if we execute the package from SQL Server it hang on execution phase and we have to cancel that after long long wait.

The solution we found is if we open the excel files once in edit mode then close them. and re-run the package it runs successfully.

The problem is we have to deploy it on Production server where we don't have access neither package nor file. And we can't ask client to open the file once in edit mode and close it and then execute the package.

First of all i can't understand the error and secondly what is the solution. Please advise. Thank you

Everything is setup on virtual machines.

here is log image where package hang enter image description here

Abid Sikandar
  • 11
  • 2
  • How do you call the child packages? Via "Execute package task"? Just asking because I ran into a similar problem when executing the packages e.g. from within a script task. Additionally: what exactly do you mean with "open once in edit mode"? Did you open it on the server? In other words: from WHICH instance (local / server) did you call Excel and on WHICH instance was the file (local / server)? Somehow it's not clear to me, how opening the excel first should solve the problem. Last but not least: did you try to create a copy of the XLS first (in the package) and work with the copy? – Tyron78 Jul 12 '16 at 16:18
  • I am executing Child packages by "Execute Package Task". Open the file and click Enable editing . if we open the file its open in read only mode and a button on top showing with label Enable Editing. I don't know how opening the file once solve the the issue That is also my question. No i did not create any copy of XLS in package. why and how we do that. – Abid Sikandar Jul 13 '16 at 07:11
  • I am using a Virtual Machine (VM). No i did not open the file on server i open it on my VM. – Abid Sikandar Jul 13 '16 at 07:15
  • The idea of copying the file is that the copy should have the correct privilege settings for you - that is it should be editable by you without explicitly prompting for it. You can create a copy via the "file system task" in SSIS. But this is only an idea - I didn't have time to test it yet! – Tyron78 Jul 13 '16 at 08:01
  • okay i am going to test it. will update you about result – Abid Sikandar Jul 13 '16 at 10:19
  • issue is still there. – Abid Sikandar Jul 13 '16 at 13:47

0 Answers0