Asynchronous Outputs on Script Component Best practice

If you have an output that is not synchronous with the input what is the best way of processing the data.

I am currently using a generic queue, and a custom class. I am creating an instance of the class in the ProcessINputRow and then adding it to the Queue.

The CreateNewOutputRows Dequeues the class instances and creates buffer rows.

Is there a better solution?

ArrayList? I've seen asynch components that cache data in an ArrayList.

-Jamie

|||The problem is having two threads, one putting data into a container and one taking it off. I think the queue is the best solution.

assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

Assigning the for xml query output to a variable

Hi,
I would like to generate an XML query using the 'for xml' clause and assign
that to a text variable for further manipulation.
for e.g., I would like to do some thing like this.
declare @.test text
Set @.test = Select * from test_table for xml auto
But this gives my the syntax error. Can anyone help capturing the xml output
of a query into a variable?
Regards,
ArunHi
Have you looked at the text datatype limitations. You can not use a lot of
the T-SQL string manipulation functions against it, so you can't really do
much with it once you have it.
You can do a SELECT ... INTO ... to write it to a temporary table, then
select it into your @.test variable. I have not tried it, but is a
suggestion.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Arun" <kathir_arun@.hotmail.com> wrote in message
news:ePOh0u8QFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like to generate an XML query using the 'for xml' clause and
> assign
> that to a text variable for further manipulation.
> for e.g., I would like to do some thing like this.
> declare @.test text
> Set @.test = Select * from test_table for xml auto
> But this gives my the syntax error. Can anyone help capturing the xml
> output
> of a query into a variable?
> Regards,
> Arun
>

Assigning query output to a variable

I am running a query that will return 1 field from 1 row. What is the
syntax to assign that to a variable.
Samplecode:
Declare @.DataFilePath varchar(200)
Declare @.DBName varchar(200)
Set @.DBName = 'TestDB'
@.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
@.DBName
Thanks!
Ron@.DataFilePath = select MAX(FileName) From master.dbo.sysaltfiles WHERE name
=
@.DBName
Or MIN. Since you are getting one row, it does not do much, but it does
tell SQL Server that only one value is coming out of the select.
RLF
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Op0ca4p%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am running a query that will return 1 field from 1 row. What is the
>syntax to assign that to a variable.
> Samplecode:
> Declare @.DataFilePath varchar(200)
> Declare @.DBName varchar(200)
> Set @.DBName = 'TestDB'
> @.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
> @.DBName
>
>
> Thanks!
> Ron
>
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml =
(SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)
It's not possible for sql2k.
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegrou ps.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml =
> (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml = (SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)It's not possible for sql2k.
--
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegroups.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml => (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

assign variable to an XML query's result

Can I set output of an XML query to a variable (SQL Server 2000)? The
following query does not parse:
DECLARE @.Xml NTEXT
SET @.Xml =
(SELECT D.*
FROM WSDMS..Documents D
WHERE D.ID = @.DocumentID
FOR XML AUTO)It's not possible for sql2k.
-oj
<bbla32@.op.pl> wrote in message
news:1172081331.938350.4510@.l53g2000cwa.googlegroups.com...
> Can I set output of an XML query to a variable (SQL Server 2000)? The
> following query does not parse:
> DECLARE @.Xml NTEXT
> SET @.Xml =
> (SELECT D.*
> FROM WSDMS..Documents D
> WHERE D.ID = @.DocumentID
> FOR XML AUTO)
>

assign an output of derive column to variable

I have a derive column( sequence) transformation in data flow , i am trying to assign this column to a variable , so that i can use it in the SQLtask control flow... how can i do this? can you show me some examples?

Why are you trying to assign a value from a derived column to a variable? The derived column works for every row passing through it. When passing a variable value back to the control flow, it would only contain the last value in the data flow.

With that said, you can use a script component's PostExecute sub to assign data to a variable.|||

Actually the derived column is in For each loop ( row by row process)...

"script component's PostExecute sub to assign data to a variable"

--> is there a code example that you can show me ?

Thank you for your help

|||

safddddddddddddddddddddd wrote:

Actually the derived column is in For each loop ( row by row process)...

No it isn't. You might have a data flow in the foreach loop.

The foreach loop can assign variables for each iteration. Why doesn't that work for you?|||

you are correct I meant I have a data flow which has the derive column transformation in the foreach loop..

I am tying to tryting to pass the result of derive column ( @.[User::Assessment_Dt] > assessment_dt ? 1 : 0 ) to a variable so that i can use it in SQLTASK ..

I understand i need to use a script task to assign the output of derive column in data flow to a variable ..but i don't know how? is there any examples that i can see?

|||What is the source for the foreach loop?

What is the source in the data flow?

I don't understand what you are trying to achieve, and I'd really like to help you architect this better.|||

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

|||

safddddddddddddddddddddd wrote:

The source of the foreach loop is

Select columns from S_enrollment_assessment

and i am not quite sure how to create the data source for the data flow in the for each loop since it it assign to row by row ?

So you're populating a data set with an execute SQL task and then using a foreach loop against that dataset?|||

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

|||

safddddddddddddddddddddd wrote:

yes.. and i assigned each columns to variables ( 71 of them) and follow the complex loding process..

for example,

FINDSTRING("1,2,3,4",@.TypeofAssessment,1) > 0 in precedence editor - expression

then go the SQLTASK1 otherwise go to SQLTASK2

and there are so many different logics that i need to follow...

so how to use 71 variables as the source of data flow in for each loop?

You don't. You can write a SQL command in a variable expression that maps other variables (from your foreach loop) and then use the SQL variable as your OLE DB Source.

For instance:
@.TypeofAssessment - Source: Foreach loop
@.SQLCommand - Source: Expression: "select * from table where assessment = " + @.TypeofAssessment

OLE DB Source - Set to use sql command from variable - choose @.SQLCommand.|||yes. that is what i was trying to do...but since there are 71 columns that i am passing as variables,it's a kind of time consuming job.. thank you for all your help.. i really appreciate it..