Passing global variables - script task

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

Passing global variables - script task

Author

Topic

stevenbe1
Starting Member

2 Posts

Posted - 2011-03-12 : 14:56:43

I am trying to write a script task in c# that converts a global date variable with the format of '2011.JAN' and converts it and passes back a global variable with the format of '201101%' which is to be used as a parameter in the where clause of a tsql script.

I am having trouble with the references to the variables. It gives me a variety of token not recognized errors on the global variable lines. Here is my code, can someone please help me find what is wrong?

/*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{
// Declare Variables
{
string time_in = (string) Dts.Variables["TIMEDIM"].Value;}}
//string time_out;
private string ReformatDate(time_in val);

// Body

string dtStr = val.ToUpper().Replace(".", "-");
DateTime dtTemp = Convert.ToDateTime(dtStr);
string time_out = dtTemp.ToString("yyyyMM");

string time_out = time_out + "%";

return (string) Dts.Variables["TIMEOUT"].Value;

Dts.TaskResult = (int)ScriptResults.Success;
}
}
}} /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{
// Declare Variables
{
string time_in = (string) Dts.Variables["TIMEDIM"].Value;}}
//string time_out;
private string ReformatDate(time_in val);

// Body

string dtStr = val.ToUpper().Replace(".", "-");
DateTime dtTemp = Convert.ToDateTime(dtStr);
string time_out = dtTemp.ToString("yyyyMM");

string time_out = time_out + "%";

return (string) Dts.Variables["TIMEOUT"].Value;

Dts.TaskResult = (int)ScriptResults.Success;
}
}
}} /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{
// Declare Variables
{
string time_in = (string) Dts.Variables["TIMEDIM"].Value;}}
//string time_out;
private string ReformatDate(time_in val);

// Body

string dtStr = val.ToUpper().Replace(".", "-");
DateTime dtTemp = Convert.ToDateTime(dtStr);
string time_out = dtTemp.ToString("yyyyMM");

string time_out = time_out + "%";

return (string) Dts.Variables["TIMEOUT"].Value;

Dts.TaskResult = (int)ScriptResults.Success;
}
}
}} /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples.

To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true);

To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;";

Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

To open Help, press F1.
*/

public void Main()
{
// Declare Variables
{
string time_in = (string) Dts.Variables["TIMEDIM"].Value;}}
//string time_out;
private string ReformatDate(time_in val);

// Body

string dtStr = val.ToUpper().Replace(".", "-");
DateTime dtTemp = Convert.ToDateTime(dtStr);
string time_out = dtTemp.ToString("yyyyMM");

(string) Dts.Variables["TIMEOUT"].Value = time_out + "%";

return (string) Dts.Variables["TIMEOUT"].Value;

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-13 : 18:14:30

Looks like you have copied and pasted the same code multiple times. You have the curly brackets placed incorrectly which would certainly cause the complaints about the unrecognized tokens.

All you should need to do is to replace your Main with the code below - I have tried to retain your code intact to the extent possible:

public void Main()
{
   string time_in = (string) Dts.Variables["TIMEDIM"].Value;

   string dtStr = time_in.ToUpper().Replace(".","-");
   DateTime dtTemp = Convert.ToDateTime(dtStr);
   string time_out = dtTemp.ToString("yyyyMM");
   time_out = time_out + "%";
   Dts.Variables["TIMEOUT"].Value = time_out;

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

I don't have a way of testing it, but at least this should take care of the complaint about unrecognized tokens. If this works and you want to wrap the reformatting portion into a function, in BIDS/VS select the 5 lines in the middle and from the right click menu select Refactor -> Refactor Method

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources