question

SamGarth avatar image
0 Votes"
SamGarth asked DivyeshPatel-3985 commented

Unexpected Termination but successful

Hello

I have an SSIS Package that writes to an Excel file.

It works successfully when...

  • Running through Visual Studio under my login and my colleagues

  • Running manually from the SSIS server under my login only

  • Running through a job from another SQL instance under my login only

When my colleague runs it manually or under the job it fails with Unexpected Termination BUT it actually writes to the file.

After testing, the table it copies has over 30K rows. I have changed this to SELECT TOP 100 and it works if the excel file is empty, but if the Excel File already a fairly large size that will fail. (even though it has written to the file).

So it seems to be writing to Excel that is the issue.

Does anyone know why this would be? And why would it work for my login but not two others?

Thanks

Sam


sql-server-integration-services
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I enabled verbose logging and the only differences between me and my colleague is he does not get the following at the end...

72174-image.png




He also gets the following error

Data Flow Task:Warning: Warning: Could not open global shared memory to communicate with performance DLL; data flow performance counters are not available. To resolve, run this package as an administrator, or on the system's console.

0 Votes 0 ·
image.png (34.0 KiB)
SamGarth avatar image
0 Votes"
SamGarth answered DivyeshPatel-3985 commented

Just an FYI the issue is fixed. It was because I was a local admin on the SSIS server. I am not sure if I can get a lower level or rights to get access to global shared memory but will look into that.

· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi SamGarth,

Can you explain more exactly what do you mean. i have the same issue . I have 2016 and 2019 instance on same server exact same package work on 2016 but on 2019 it fails with this error.

0 Votes 0 ·

Hello. The error was due to not being able to create a memory object on the os if I remember correctly. To allow this I had to give the executing user local admin rights on the ssis server. I believe there are alternatives to local admin but haven't tested these.

0 Votes 0 ·

Thank you for the reply. For me account is local admin on server and i have set the account to server admin role. It is still not working. if you can think of any other ticks you may have applied please do share.

0 Votes 0 ·
Monalv-msft avatar image
0 Votes"
Monalv-msft answered Monalv-msft edited

Hi @SamGarth ,

1.Could you please share the detailed error messages?

2.We can set protection level in SSIS package.
To protect the data in an Integration Services package, you can set a protection level that helps protect just sensitive data or all the data in the package. Furthermore, you can encrypt this data with a password or a user key, or rely on the database to encrypt the data.
Please refer to Access Control for Sensitive Data in Packages.

Best regards,
Mona


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.





· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Mona

72141-image.png




72116-image.png


This is a simple package that captures 31,423 rows of the following

72143-image.png

I can see that it actually wrote to the file all 31,423 rows to the file but it failed according to SSIS.

If I ran it as my user it would work.

0 Votes 0 ·
image.png (8.0 KiB)
image.png (104.7 KiB)
image.png (16.4 KiB)
DivyeshPatel-3985 avatar image
0 Votes"
DivyeshPatel-3985 answered

Was this ever resolved if so how .I have the same issue

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.