Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

SSIS and Stored procedures using temp tables Expand / Collapse
Author
Message
Posted Monday, December 29, 2008 10:20 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, March 05, 2014 10:14 AM
Points: 477, Visits: 727
Raymond,

the IF block is used to place a 'unexecuted' SELECT at the beginning of the stored procedure. Placing the conditional test in a WHERE clause without the IF block will return a result... albeit an empty one. The technique described in the article is being used to 'trick' the SSIS OLE DB provider into using the first SELECT it sees, while allowing the stored procedure to ignore the same SELECT when it runs.

Clear as mud?

--SJT--



Post #626659
Posted Monday, December 29, 2008 12:06 PM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 27, 2009 10:01 AM
Points: 48, Visits: 123
In Reply to STJ, you're right in that I left out the enclosing block that made the contract SQL ninja-like. I was just suggesting a way to cast off all the NULLs in the contract SQL; reading the datatypes from the db instead hardcoding them. It would be easier to maintain and easier to code for slackers like me.



FROM:
IF 1 = 2
BEGIN
SELECT CAST(NULL AS INT) AS ContactID
,CAST(NULL AS NVARCHAR(50)) AS FirstName
, CAST(NULL AS NVARCHAR(50)) AS MiddleName
, CAST(NULL AS NVARCHAR(50)) AS LastName
, CAST(NULL AS NVARCHAR(10)) AS Suffix
, CAST(NULL AS NVARCHAR(50)) AS EmailAddress
END

TO:
IF 1 = 2
BEGIN
SELECT ContactID
, FirstName
, MiddleName
, LastName
, Suffix
, EmailAddress
FROM Person.Contact
where 2 = 4
END
Post #626738
Posted Monday, December 29, 2008 7:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 24, 2012 11:04 AM
Points: 3, Visits: 52
Would this not work and be a little less wacky?

Create table #Contact
(contactID int,
firstName Nvarchar(50),
MiddleName Nvarchar(50),
Lastname Nvarchar(50),
Suffix Nvarchar(50),
EmailAddress Nvarchar(50))

INSERT INTO #Contact
Select ContactID, Firstname, MiddleName,
LastName, Suffix, EmailAddress
FROM Person.Contact
Post #626953
Posted Thursday, January 01, 2009 6:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:39 PM
Points: 8, Visits: 297
Yes. The alternative select statement directly from the table would work as well for this example. However, in some circumstances the CAST(NULL AS ...) may be needed. Just remember that the NULL must be cast to a desired datatype, otherwise I think SQL Server will default the datatype to INT.
Post #628546
Posted Thursday, January 01, 2009 6:31 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:39 PM
Points: 8, Visits: 297
For this simplistic example the alternative you suggest would probably work. However, on the job the stored proc probably won't be as simple. At my job we frequently use intermediate temp tables in the process of building a final result. This means that there are SELECT statements near the beginning of the procedure which do not echo the final SELECT statement.
Post #628548
Posted Friday, January 09, 2009 5:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 27, 2009 3:47 PM
Points: 41, Visits: 31
Thanks, Michael. I had to apply your workaround today. I've got a parameterized stored procedure returning hundreds of thousands of rows. It needs a temporary table for performance reasons; table variables just don't cut it, due to lack of indexes. For the same reason, executing this procedure 5 times isn't very appealing either. So the 1=2 workaround is perfect. SSIS doesn't appear to know the difference, so long as the fields and datatypes are identical. Awesome!
Post #634021
Posted Tuesday, February 10, 2009 10:22 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 19, 2011 6:36 AM
Points: 2, Visits: 16
Hi,
Thanks for this good article. it really helpful for newcomer in ssis. I was facing problem with columns when I was trying to call stored procedure in ssis.
once againg thank you.

regards,
mayur
Post #654420
Posted Thursday, February 19, 2009 12:17 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, March 10, 2010 10:03 AM
Points: 1, Visits: 34
Thanks for the research. This helps me to avoid having to create and drop a physical table in the database to provide the "contract" for DTSPipeline.

I prefer to using a table variable for the output of procedure. For me the first thing to do in this sort of stored procedure is to declare table a table viarable for output following by your "Wacky" solution. After this setup, you can still use temp tables in your procedure.

Declare @tblOutput Table (
...
)


If 1=2
Begin
Select * from @tblOutput
End
Post #660698
Posted Tuesday, February 02, 2010 7:46 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
Micheal,

I know you wrote this article a while ago but I wanted to say "thanks". Today is my first "real" day with SSIS and it's nice to see that SSIS has some "whacky" features like T-SQL does. Neat stuff and a nicely written article. Thanks!


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #858369
Posted Tuesday, November 23, 2010 3:56 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 16, 2011 8:41 AM
Points: 1, Visits: 2
Thanks for this nice article, it solve my problem !
I used variable tables in place of temp tables => do not forget "SET NOCOUNT ON" at the beginning of the stored procedure.

Thanks again !!!!
Post #1024992
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse