Issue with C# Script task in SSIS

Question

Issue with C# Script task in SSIS

jrgomont

Old Hand

Points: 313
More actions
May 22, 2013 at 7:44 am

#273016

Hi,
I am trying to set the value of a variable in SSIS using a C# script task. This task takes
ReadOnlyVariables: User::Counter,User::FileDate,User::FileExtension,User::Code
ReadWriteVariables: User::FileName
The script task does not return the value. Can someone guide towards the right approach or let me know what I am doing wrong. Thanks
Here is the code to set the value of that variable:
------------------------------------------------------------------------------------------------------------------------------------
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
namespace ST_501dbe371426493eaa6ec55b247902ab.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
String strCode = string.Empty;
String strFileDate = string.Empty;
String strFileExtension = string.Empty;
String strFileName = string.Empty;
Int32 intCounter = 0;
strCode = Dts.Variables["User::Code"].Value.ToString();
strFileDate = Dts.Variables["User::FileDate"].Value.ToString();
strFileExtension = Dts.Variables["User::FileExtension"].Value.ToString();
intCounter = Int32.Parse(Dts.Variables["User::Counter"].Value.ToString());
strFileName = strCode + "_" + strFileDate + "_" + intCounter.ToString("D4") + strFileExtension;
///This message box actually print the value that I am looking for
MessageBox.Show("The Filename is: " + strFileName);
/// But when I assigned this value like this below. It does not set the variable to that value
Dts.Variables["User::FileName"].Value = strFileName;
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
------------------------------------------------------------------------------------------------------------------------------------
I thought this would work But it does not. To verify it I have another script task following that one, that reads the variable value through a message box. The message box shows nothing.
Thanks for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 1

Code looks OK to me, but its hard to say what might be happening without seeing the Package. Please attach it to the thread so we can have a look.

As an aside, it's safer to use the FireInformation method to get debug info out of a Script Task. Too many times people forget to remove the calls to MsgBox and when it goes to production you can imagine what happens when run as an unattended job.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

sqlbi.vvamsi Ten Centuries Points: 1194 More actions · Answer 2

code looks fine..verify whether you have values in dts variables or not

jrgomont Old Hand Points: 313 More actions · Answer 3

Thank you all for your replies. I fixed the issue. I have recreated a new variable and passed the value to the new one and it wirked. Fir some reason the one set before could not get the work done.

Orlando Colamatteo SSC Guru Points: 182276 More actions · Answer 4

jrgomont (5/22/2013)
Thank you all for your replies. I fixed the issue. I have recreated a new variable and passed the value to the new one and it wirked. Fir some reason the one set before could not get the work done.

Happy you got it sorted. Totally guessing here because I cannot see what you had or have now, but I am left wondering if you had the previous variable in a different scope than what you have now.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato