Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

SSIS Script tasks losing code

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. 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.

87 votes
Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)

We’ll send you updates on this idea

DataNerd shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

17 comments

Sign in
(thinking…)
Sign in with: Microsoft
Signed in as (Sign out)
Submitting...
  • Ran Ye commented  ·   ·  Flag as inappropriate

    This issue has already been fixed. May you have a try with the latest SSDT for VS2017?

  • Anonymous commented  ·   ·  Flag as inappropriate

    I've used code for years that writes a script task and it contains no loops. Now my SSIS packages won't compile and the error is the binary code for the script is not found. Don't make give up using our scripted SSIS creation, please fix this issue.

  • Harshala Desai commented  ·   ·  Flag as inappropriate

    I had a similar issue wherein the Variables and the code vanished.
    But I got a quick fix by adding a sequence container and within that I used the Script task and it works fine for me.

  • Lawrence JM commented  ·   ·  Flag as inappropriate

    We have been dealing with this problem intermittently for several years, and recently conducted a thorough investigation of the issue. Here's what we found:

    The problem occurs when:

    - There is a Foreach Loop or For Loop Container,
    - Which contains child Script Tasks,
    - And the user opens the Foreach Loop or For Loop Editor.

    The problem is now triggered; when the user next saves, Visual Studio will fail to write the following properties of each child Script Task to file:

    - ReadOnlyVariables,
    - ReadWriteVariables,
    - Any and all Script Task code.

    The insidious part of the problem is that even though the file on disk no longer contains these properties, *the Visual Studio UI keeps them in memory and will act as if this data still exists*. It is only after the project is closed and reopened will it become apparent that this data has been lost.

    We do not have a solution at this time. However, we have successfully instituted the following workarounds:

    1. Avoid using the Foreach Loop and and For Loop Editors (i.e. where possible, change properties in the Properties panel instead).

    2. If the Editors must be used, then: move all child objects outside of the relevant container; make your changes; save; and then return the child objects back to the container.

    Final note: this problem does not appear to occur with Sequence Containers (which have no Editor interface). It may affect child objects other than Script Tasks - we did not test extensively in this area.

    Additional Resources:

    - https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s

    - https://social.msdn.microsoft.com/Forums/sqlserver/en-US/059012f7-180c-43a0-a0b7-3a40b8569a6f/script-task-drops-code-in-ssis-package?forum=ssdt

  • Lenin commented  ·   ·  Flag as inappropriate

    Sometimes this happens with NO warnings others something like this shows up There was an exception while loading Script Task from XML: System.Exception: The Script Task "ST_" uses version 15.0 script that is not supported in this release of Integration Services. To run the package, use the Script Task to create a new VSTA script. In most cases, scripts are converted automatically to use a supported version, when you open a SQL Server Integration Services package in %SQL_PRODUCT_SHORT_NAME% Integration Services.
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.ScriptTask.LoadFromXML(XmlElement elemProj, IDTSInfoEvents events)

  • KK commented  ·   ·  Flag as inappropriate

    I am strongly recommending my company to not use SSIS package for ETL work. Really sucks when all 4 months of script task effort is gone. I had nearly 20 script task and was able to test it successfully until yesterday.. Suddendly all the code inside script task has disappeared now..

  • William commented  ·   ·  Flag as inappropriate

    OK - I don't know how to solve it, but I found a workaround, if somebody maybe interested:
    1. instead of having script task, use Execute Package Task.
    2. Put your script task into new package (just one task in the package), create parameters that your parent package is going to supply when calling it.
    3. In your script, add lines to assign values to your Parent package (child package has access to your parent package variables at runtime). Btw, you don't need to specify any read-only, read-write variables in the GUI of the script task. use the following template in the script:
    public void Main()

    {
    Variables vars = null;
    VariableDispenser variableDispenser = Dts.VariableDispenser;
    variableDispenser.LockForWrite("User::ParentPackageVariable");
    variableDispenser.LockForRead("User::ChildPackageVariable");
    variableDispenser.GetVariables(ref vars);

    vars["User::ParentPackageVariable"].Value = "new value"; //whatever your code does

    vars.Unlock();

    Dts.TaskResult = (int)ScriptResults.Success;
    }

  • Anonymous commented  ·   ·  Flag as inappropriate

    This is very disappointing that a product can be realeased with such a major issue! First thought is was related to the default script language. I have since seen that this is not the case. I seem to have to keep a copy of the scripts so I can paste them in when they get lost. Microsoft please comment on this issue!

  • Kelly commented  ·   ·  Flag as inappropriate

    We have seen Script Tasks lose code regardless of whether the task is in a foreach loop container or not. We also have seen the Script Task code disappear when deploying from VS.

    My team is running VS 15.3.3; SSDT 15.1.16710.120.
    This is causing serious development and production issues.
    Please advise ASAP.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Also see this if I generate an ISPAC and attempt to deploy my SSIS package from there. If I deploy using VS, it works fine. I'm glad I have access to a box that can do this for production or we'd have a lot of issues.

  • Anonymous commented  ·   ·  Flag as inappropriate

    Same issue, Code lost in script task. Re creation steps:

    1. Create new Script task within a SQL 2016 SSIS Project), to replace code which has now vanished.
    2. Re enter script code. (in my case C# 2015).
    3. Build and save.
    4. Save package.
    3. Execute package in SSDT - Works.
    4. Now, just re-execute package - Script has now vanished!

    This is a massive issue for our developments as it has stopped us deploying solutions for our month end processes. Please review urgently.

  • David commented  ·   ·  Flag as inappropriate

    Same issue here. I can reproduce easily/quickly with the following steps.

    1. Change project TargetServerVersion to SQL Server 2016
    2. Add Foreach Loop Container
    3. Add Script Task to Foreach Container
    4. Add any variable to Script Task ReadOnlyVariables (ScriptLanguage should say 2015)
    5. Change a property of the Foreach Container.
    6. Save package
    7. Close package
    8. Open package
    9. Check Script Task, and your variable is gone. Any code will clear as well.

    From what I can tell, changing the Foreach Container is the event that causes the contents of the Script Task to clear.

    Microsoft Visual Studio Community 2017
    Version 15.5.6
    Microsoft SQL Server Integration Services Designer
    Version 14.0.3002.68

  • DataNerd commented  ·   ·  Flag as inappropriate

    I do want to add, make sure you close and re-open the package between step 4 & 5. Also ensure "TargetServerVersion" is set to "SQL Server 2016".

Feedback and Knowledge Base