table variable in SSIS

• SQL006

  SSCertifiable

  Points: 5071

  More actions

  September 30, 2015 at 7:46 am

  #308400

  Hi SSIS Experts

  How to use table variable in SSIS 2012, is it possible to use table variable in SSIS.

  I want to insert some results from EXECUTE SQL TASK to this table variable and use this variable in OLEDB SOURCE task in data flow where it is used in SQL query with IN Operation.

  The table variable contain multiple values like '100','234','XYZ'

  Is it possible to do or is there any other solution to achieve this

  Thanks

• Phil Parkin

  SSC Guru

  Points: 246988

  More actions

  September 30, 2015 at 8:06 am

  #1830455

  SQL006 (9/30/2015)

  ---

  Hi SSIS Experts

  How to use table variable in SSIS 2012, is it possible to use table variable in SSIS.

  I want to insert some results from EXECUTE SQL TASK to this table variable and use this variable in OLEDB SOURCE task in data flow where it is used in SQL query with IN Operation.

  The table variable contain multiple values like '100','234','XYZ'

  Is it possible to do or is there any other solution to achieve this

  Thanks

  A more robust alternative is to write a stored proc to do all of the work and return the columns from there. Use that as your OLEDB source.

  I'd recommend using a temp table rather than a table variable if there is anything more than a handful of rows, for reasons of performance.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel

• Alvin Ramard

  SSC-Forever

  Points: 41190

  More actions

  September 30, 2015 at 8:18 am

  #1830461

  I believe a table variable in SSIS would end up being a recordset stored in an object variable. This is not the simplest thing to work with.

  I'd give Phil's idea some serious consideration before trying to handle a table variable in SSIS.

  ---

  
  Alvin Ramard
  Memphis PASS Chapter[/url]

  All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

  For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]