Pass SQL Execute task output to target tbl(Data flow).

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Integration Services » Pass SQL Execute task output to target...

Pass SQL Execute task output to target tbl(Data flow).

Rate Topic

Display Mode

Topic Options

Author

Message

Saintmount.Sql

Posted Saturday, December 01, 2012 9:59 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 9:03 AM
Points: 28, Visits: 411

Hi,
we need to insert random values to target table.

We are using the query in SQL Execute task with single row result set & in the Result Set tab set with user variable(intRdmid).

declare @PriID int
select top 1 @PriID = code_id from Pri_codes_master where code_desc like '%assignment%'
ORDER BY NEWID()
select @PriID

But getting error as...
[Execute SQL Task] Error: An error occurred while assigning a value to variable "intRdmid": "Unable to find column @PriID in the result set.".

I want to use this value in data flow task to insert in target table.How can I implement this?

can anyone tell me please ?
Thanks,

Post #1391699

Phil Parkin

Posted Sunday, December 02, 2012 5:12 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494

You've made it more complex than it needs to be - no need for the SQL Server variable. Just this should be fine:

select top 1 code_id from Pri_codes_master 
where code_desc like '%assignment%'
ORDER BY NEWID()

By the way - I am presuming that the NEWID() is there to enforce a degree of randomness? Seems like an unusual requirement ...

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1391716

Saintmount.Sql

Posted Monday, December 03, 2012 7:39 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 04, 2013 9:03 AM
Points: 28, Visits: 411

Thanks for your reply,

But I want to insert below selected values to another table by random way.

Ex:
select Distinct code_id from Pri_codes_master
where code_description like '%assignment%'
--ORDER BY NEWID()
Cod_id values are...
372
373
374
375
376

when we use below query only one (374) inserting to target tbl column.
select top 1 code_id from Pri_codes_master
where code_desc like '%assignment%'
ORDER BY NEWID()

Hope that i have mentioned issue clearly.

Please let me if any workaround to achieve.

Thanks.

Post #1392223

joeroshan

Posted Monday, December 03, 2012 8:12 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: 2 days ago @ 4:12 AM
Points: 573, Visits: 1,165

If you need multiple rows you chnage result set to full and use an object type variable to hold the ado resultset .

-- Roshan Joe
*******************************************
Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help
Custom cleanup script for backups

Post #1392226

Phil Parkin

Posted Tuesday, December 04, 2012 3:33 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 9:20 AM
Points: 4,242, Visits: 9,494

Saintmount.Sql (12/3/2012)

OK - what I should have said is that you can use a query as your datasource inside a dataflow. No need for an ExecuteSQL task at all.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1392330

« Prev Topic | Next Topic »

Permissions