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
– Tab Alleman, Commented Jun 6, 2019 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
– Nick.Mc, Commented Jun 7, 2019 at 4:57
@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
– Hadi, Commented Jun 7, 2019 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
– Yahfoufi, Commented Jun 14, 2019 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
– Tab Alleman, Commented Jun 14, 2019 at 19:55

Hadi · Accepted Answer · 2019-06-07 09:46:22Z

1

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

edited Jun 7, 2019 at 9:46

answered Jun 7, 2019 at 0:26

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hadi Over a year ago

@Tony feel free to mark this answer as accepted by clicking on the mark on the left side of the answer

digital.aaron · Accepted Answer · 2019-06-06 18:22:25Z

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 6, 2019 at 18:22

digital.aaron

5,7152 gold badges27 silver badges46 bronze badges

Collectives™ on Stack Overflow

Getting error in Script Task Editor in SSIS

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related