how to run SP which internally conatain Temp tables in ssis

• mvenkatareddy06

  Newbie

  Points: 1

  More actions

  June 26, 2013 at 11:16 am

  #275600

  Hi,

  I have one sp which internally using 10 temp tables.

  I need to load the result set from sp into one new table using SSIS

  I tried delay validation=true and retain same connection=true properties also

  Please help me.

  Thanks in Advance

• michal.lisinski

  SSCertifiable

  Points: 6933

  More actions

  July 10, 2013 at 9:41 am

  #1631279

  Hi

  There's no problem run sp, you have to do a few steps:

  1. Create a new Data Flow Task.

  2. Add OLE DB Source in DFT.

  3. Put EXEC "your_sp_name" in SQL Command text.

  4. Mark output columns.

  5. Add OLE DB Destination and join with new table.

  6. Join OLE DB Source with OLE DB destination.

  If there are more then one select statements in your sp, it will show columns from the first one, but

  it's not a big case.

  Br.

  Mike