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 2005 Forums
 SSIS and Import/Export (2005)
 SQL Tast Variable not set

Author  Topic 

Cry
Starting Member

24 Posts

Posted - 2009-01-20 : 07:42:56
Hi,

i have a problem with an output variable. I try to execute a sql command (result: single row, single value, SELECT MAX () AS MaxAGNr ...) and defined the resultset as MaxAGNr - User::MaxNr.
The variable is package-scoped STRING Variable because the MAX-Value
is on a String-Column.

The Problem is, that the new value of the variable is not shown
outside the task. What have i done wrong????

Is there anybody who can help me, please?

Thanks
Joerg

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-20 : 08:06:36
Will you please explain the scenario more?

Rahul Shinde
Go to Top of Page

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-20 : 08:14:27
Result Set: Result Name = 0, Variable Name = User::MaxNr
Go to Top of Page

Cry
Starting Member

24 Posts

Posted - 2009-01-20 : 08:19:28
Hi,

I created an EXEC SQL Task on an OLE DB connection. The task executes a SQL query directly ("SELECT ISNULL(MAX(AG_Agnr), '1000000') AS MaxAGNr FROM NSC_AG"). The resultset is set to "single row". I defined a package-scoped variable named MaxAGNr (Namespace = User, Datatype = String, Value = ""). There are no parameters. On the the resultset-tab i have defined MaxAGNr with variable User::MaxAGNr. Expressions are also not defined.

When i executes the task there are no errors, but the variable
user::MaxAGNr has still the value "".

I hope this explains the situation a little bit more.

thanks
Go to Top of Page

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-20 : 08:31:04
Did you do this
Result Set: Result Name = MaxAGNr, Variable Name = User::MaxNr

instead of this?
Result Set: Result Name = 0, Variable Name = User::MaxNr
Go to Top of Page

Cry
Starting Member

24 Posts

Posted - 2009-01-20 : 08:48:30
In the Resultset i defined:
Result Name = MaxAGNr, Variable Name = User::MaxAGNr

The other version: Result Name = 0 and Variable Name = User::MayAGNr does not work too.
Go to Top of Page

rgombina
Constraint Violating Yak Guru

319 Posts

Posted - 2009-01-20 : 10:39:35
Try default User::MayAGNr with some value and add Breakpoint Pre and PostExecute on the SQL Task. Just making sure you are producing output to begin with.
Go to Top of Page

Cry
Starting Member

24 Posts

Posted - 2009-01-20 : 11:03:01
The table NSC_AG is empty. Because of the ISNULL function the return value is "1000000". I checked the statement via SQL Management Studio. Breakpoints are set "Variable change value". In the watch window i can see the value of the variable. Its constant {} (empty). Also the breakpoints
pre and post execute are of the same result....
Go to Top of Page

Cry
Starting Member

24 Posts

Posted - 2009-01-22 : 09:27:50
seems to be an visualisation problem of SSIS.
Vaiable will be set correct but the change will not be shown in the variable section nor in the debugger...
Go to Top of Page
   

- Advertisement -