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

• Saintmount.Sql

  Ten Centuries

  Points: 1023

  More actions

  December 1, 2012 at 9:59 pm

  #265600

  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,

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  December 2, 2012 at 5:12 am

  #1564685

  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 ...

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Saintmount.Sql

  Ten Centuries

  Points: 1023

  More actions

  December 3, 2012 at 7:39 pm

  #1565109

  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.

• joeroshan

  SSChampion

  Points: 10377

  More actions

  December 3, 2012 at 8:12 pm

  #1565111

  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[/url]

• Phil Parkin

  SSC Guru

  Points: 246959

  More actions

  December 4, 2012 at 3:33 am

  #1565194

  Saintmount.Sql (12/3/2012)

  ---

  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.

  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.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin