Using Temporary Tables across different tasks in SSIS

• duggal.priyanka06

  Say Hey Kid

  Points: 684

  More actions

  October 11, 2012 at 12:36 am

  #263739

  Hi,

  We need to create a temporary table in Execute SQL Task and access it in subsequent Data Flow Task. I tried Retain Connection to True and access the temp table in DFT. But the package fails. Any pointers please?

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  October 11, 2012 at 8:46 am

  #1548172

  SSIS needs a concrete table to build metadata from when designing the DFT. Try using a concrete table for design then changing the package to use a temp table at runtime using Variables and Expressions. You may also need to set DelayValidation to True on some components so it does not try validating the metadata until execution.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato