SSIS Script Tasks losing code

Question

I have a very strange issue happening that is causing Script Task code to clear out. I have been able to test on 2-3 different machines. We are running SSDT 15.4 preview. The steps to reproduce were as follows.

1. Create a script task inside of a foreach loop container.
2. Create a comment in the script task.
3. Change or add a variable mapping in the foreach.
4. Save package.
5. Close the package.
6. Open the package.
7. Open the script task and the comment will have vanished.

As my last attempt for success,I have upgraded to 15.5.1 and the problem still exists.

Suite

Can't seem to duplicate. Where are you putting the comment? In Main()? – Reuter

I have had Script Tasks lose code on me as well. Your steps are not the same as mine (I lost actual code). I can't confirm the version since I no longer work there. – Ultrasonics

Might sound silly, but are you sure you are clicking 'Ok' on the script task after you edit and close the script itself? If you click cancel on that, it doesn't save the script change. – Becalmed

Yes I am clicking ok. I used just a comment as a demonstration so I didn't have to paste much code in. One thing we discovered is that you have to be sure you are in SQL 2016 compatibility mode within the SSIS project. Change the Project Property "TargetServerVersion" to "SQL Server 2016". – Suite

I'm too having this weird issue. I still have no idea what's causing this. Right now, i took a backup and whenever the code is vanishing, i just restore the code whenever its not there. very frsutrating. – Iatric

This is a common issue, unfortunately, and has been occurring since BIDS (if I recall correctly). As far as i know, it's still a "feature" in SSDT 2017 as well. It's not a solution, but my recommendation is ensure you are always using some kind of version management. Thus, if the script is lost, you can recover it. – Cowbane

Have you tried to click on the save button inside the Script Editor window before closing it? – Forgotten

Windows version? (start-run winver) – Formenti

Yes the save button has been hit. Many others have been able to reproduce. Windows version is 10. But this is not an operating system issue it is a SSDT issue in Visual Studio. I have a uservoice ticket open on it that is getting some upvotes and is under review. feedback.azure.com/forums/908035-sql-server/suggestions/… – Suite

@Suite i up voted your request – Habitual

I have struggled with lost script code for a long time as well. Currently I can reproduce it on an clean* Win 8.1 with newest versions of VS2017, SSDT2017, VSTA2017. (*well almost clean using the VS 'TotalUninstaller' on all previous versions). – Tagliatelle

I addition, I have tried to downgrade to TargetServerVersion='SQL server 2016' under project properties. This causes the script component to use 'Microsoft visual C# 2015', so I installed VSTA2015 as well. Issue persists, even on new projects. – Tagliatelle

Can this be related to Antivirus, blocking the write to disk? (After all it is binary code.) – Tagliatelle

It is not an anti-virus issue. You can disable all and the issue persists. So far a bug that Microsoft has not addressed in several versions. – Suite

Answer 1

If you close/Cancel the Script Task Editor form in Visual Studio after editing code, the code will not save. This will occur even if the code was saved in the other Visual Studio editor - the one where you edit the code.

You MUST click "OK" for the code to save.

Sidero

That had been done but still resulted in the code being lost. – Suite

Answer 2

If there is some error in the script it is not saved. Check your script for errors in the menu Build / Run Code Analysis on Solution or pressing Alt+F11

Thermography

That is not the issue. The issue is reproducible. It happens for script tasks within a foreach container. Not sure on specifics if it is all containers or just for each. I have submitted a uservoice for it with already 32 upvotes. feedback.azure.com/forums/908035-sql-server/suggestions/… – Suite

Answer 3

I had something similar. All code was being stripped when I imported the packages into SQL Server. For us it turned out SSMS was silently stripping code with an incompatible DTS version. SSMS v18 was removing scripts built with DTSv14. Once I opened them in a new version of Visual Studio, moved one "block" 1px over, then saved, it changed the package to DTSv15, which then imported fine using SSMS v18.

Just to verify, I tried using SSMS v17 with the DTSv14 package, and the Script Tasks were left alone and imported fine. But for other reasons we need to be using SSMS v18.

Bio