Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Welcome to my blog. I work as Database lead at Synaptic Digital. Hope you find some interesting stuff here.
Browse by Tags · View All
BRH 17
SQL Server 15
#SQL Server 11
#BI 10
#TSQL 8
TSQL 8
BI 7
SSRS 6
#SQLServer 6
SSRS 2008R2 5

Archive · View All
January 2011 6
December 2010 5
September 2012 4
May 2012 4
March 2011 4
November 2012 2
October 2012 2
January 2012 2
February 2011 2
November 2010 2

SSIS and Stored Procedures with Temp Tables in SQL 2012

Dec 7 2012 12:00AM by Chintak Chhapia   

In earlier versions of SQL Server, if we want to use stored procedures in OLEDB source command which contains temporary tables we take some workarounds mentioned below

1. http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/65112/

2. http://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/61824/

In SQL 2012, we can include the result set definition to execute statement by using with result set syntax. By doing this we do not need to take workarounds described in above links. Below is the dome stored procedure

use AdventureWorks2012
go
if object_id('dbo.proc_testTempTableInSSIS','p') is not null
    drop procedure dbo.proc_testTempTableInSSIS
go
Create procedure dbo.proc_testTempTableInSSIS
as
begin
    create table #SSISTest ( c1 int, c2 nvarchar(100))

    insert into #SSISTest (c1,c2)
    select top 10 schema_id, type_desc 
	from sys.all_objects    
	
	select c1
        ,c2    
	from #SSISTest
end
go

Now, we can use WITH RESULT SETS in OLEDB source as below

exec dbo.proc_testTempTableInSSIS
with result sets
(  
	(   
		c1 int,
		c2  nvarchar(100)  
	)
);
image

Hope this helps you somewhere. Thanks for reading.

Tags: SSIS, SQL 2012, Temp Tables


Chintak Chhapia
40 · 5% · 1477
4
 
0
Lifesaver
 
 
 
0
Incorrect



Submit

2  Comments  

  • Very helpful, thanks.

    But how can we handle such kind of logic?

    IF @a=0
    create table #SSISTest ( c1 int  , c2 nvarchar(100))
    ELSE
    create table #SSISTest ( c1 nvarchar(100), c2 nvarchar(100))
    
    commented on Dec 7 2012 12:20PM
    Dubelewskyj Oleksandr
    480 · 0% · 81
  • @Dubelewskyj: Glad that you find this helpful. In case you described, I presume you also want to return data from any table.

    SSIS always needs exact datatype, so in any case you need to output in one datatype.As int can be converted to nvarchar, you can use nvarchar in with result and later in the transformation add logic( map be conditional spit or script transformation ) for further processings.

    commented on Dec 8 2012 2:06AM
    Chintak Chhapia
    40 · 5% · 1477

Your Comment


Sign Up or Login to post a comment.

"SSIS and Stored Procedures with Temp Tables in SQL 2012" rated 5 out of 5 by 4 readers
SSIS and Stored Procedures with Temp Tables in SQL 2012 , 5.0 out of 5 based on 4 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]