Getting error in Script Task Editor in SSIS

Question

I am trying to upgrade SSIS package from 2013 to 2017. But, I am getting error for the below lines of code. Can anyone please resolve this?

I am new so I haven't tried anything yet.

using System;
namespace ST_3e6cc55d375c472785d01c446ea4bf8b
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        public object Now { get; private set; }

        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };
        
        public void Main()
        {
            // TODO: Add your code here
            Dts.Variables("FileNameCSV").Value = Format(Now, "yyyyMMddHHmmss") + "_MailPieces_" + LTrim(RTrim(Dts.Variables("FrequencyType").Value)) + ".csv";
            Dts.Variables("FileNameZIP").Value = Format(Now, "yyyyMMddHHmmss") + "_MailPieces_" + LTrim(RTrim(Dts.Variables("FrequencyType").Value)) + ".zip";

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

I am getting these below errors. Please help.

Error CS1955 Non-invocable member 'ScriptObjectModel.Variables' cannot be used like a method.

Error CS0103 The name 'Format' does not exist in the current context

Error CS0103 The name 'LTrim' does not exist in the current context

Error CS0103 The name 'RTrim' does not exist in the current context

I don't see how this script could have ever worked in any version of SQL Server. Ltrim and Rtrim are not valid C# commands. forums.asp.net/t/1213793.aspx?How+to+do+LTrim+and+RTrim+in+C+ — Tab Alleman
Leading on from this comment - the first thing to do is confirm if it works in the old environment, and also confirm whether this was written in VB.Net or C#. It looks like a bit of both (and therefore wouldn't actually run) — Nick.McDermaid
@Nick.McDermaid it looks like the OP is using Microsoft.VisualBasic.dll: social.msdn.microsoft.com/Forums/en-US/… but i think it is not recommended — Hadi
@TabAlleman this should work if the user is using Microsoft.VisualBasic.dll within a C# script as mentioned in the comment above — Yahfoufi
@Yahfoufi perhaps, but if that were the case, wouldn't there be a using directive for it? The code snippet contains using System; and nothing else. Then again, maybe that's the problem. — Tab Alleman

Hadi Hadi · Accepted Answer · 2019-06-07T00:26:00

Three suggestions:

Replace Now with DateTime.Now
Use ToString(<format>) instead of Format()
Use Trim() instead of LTrim(RTrim())

Try using the following code:

using System;
namespace ST_3e6cc55d375c472785d01c446ea4bf8b
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {


        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure

        };

        public void Main()
        {
            // TODO: Add your code here
            Dts.Variables("FileNameCSV").Value = DateTime.Now.ToString("yyyyMMddHHmmss") + "_MailPieces_" + Dts.Variables("FrequencyType").Value.ToString().Trim() + ".csv";
            Dts.Variables("FileNameZIP").Value = DateTime.Now.ToString("yyyyMMddHHmmss") + "_MailPieces_" + Dts.Variables("FrequencyType").Value.ToString().Trim() + ".zip";

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

Getting error in Script Task Editor in SSIS

2 Answers