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. https://forums.asp.net/t/1213793.aspx?How+to+do+LTrim+and+RTrim+in+C+ — Tab Alleman, Jun 06 '19 at 19:36
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.Mc, Jun 07 '19 at 04:57
@Nick.McDermaid it looks like the OP is using Microsoft.VisualBasic.dll: https://social.msdn.microsoft.com/Forums/en-US/ff5c9785-b636-4ef5-874e-7b3e0b9e5b42/using-microsoftvisualbasicdll-in-c-projects?forum=csharplanguage but i think it is not recommended — Hadi, Jun 07 '19 at 14:58
@TabAlleman this should work if the user is using `Microsoft.VisualBasic.dll` within a C# script as mentioned in the comment above — Yahfoufi, Jun 14 '19 at 19:20
@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, Jun 14 '19 at 19:55

Hadi · Answer 1 · 2019-06-07T09:46:22.380

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;
        }
    }
}

@Tony feel free to mark this answer as accepted by clicking on the mark on the left side of the answer — Hadi, Jun 14 '19 at 06:43

score 0 · Answer 2 · answered Jun 06 '19 at 18:22

0

Looks like you're missing the reference to DTS runtime assembly. Try adding this to your using block:

using Microsoft.SqlServer.Dts.Runtime;

answered Jun 06 '19 at 18:22

digital.aaron

5,435
2
24
43

Getting error in Script Task Editor in SSIS

2 Answers2