InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 01:56:28 GMT20http://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxEXCELLENT ARTICLE !!!!!Saved my day.Thank Michael Capehttp://www.sqlservercentral.com/articles/Integration+Services+%28SSIS%29/65112/Wed, 27 Jun 2012 09:38:21 GMTtonycompuhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxThanks for this nice article, it solve my problem !I used variable tables in place of temp tables =&gt; do not forget "SET NOCOUNT ON" at the beginning of the stored procedure.Thanks again !!!!Tue, 23 Nov 2010 03:56:49 GMTdindonsquathttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxMicheal,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!Tue, 02 Feb 2010 19:46:46 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxThanks 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 BeginSelect * from @tblOutput EndThu, 19 Feb 2009 12:17:43 GMTdujunbrucehttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxHi, 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,mayurTue, 10 Feb 2009 22:22:09 GMTmayurshendge1http://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxThanks, 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!Fri, 09 Jan 2009 17:43:27 GMTBrian Pondhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxFor 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.Thu, 01 Jan 2009 18:31:06 GMTMichael Capehttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxYes. 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.Thu, 01 Jan 2009 18:23:01 GMTMichael Capehttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxWould 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 #ContactSelect ContactID, Firstname, MiddleName,LastName, Suffix, EmailAddressFROM Person.ContactMon, 29 Dec 2008 19:06:11 GMTsginnhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxIn 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.[quote]FROM:IF 1 = 2BEGIN 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 EmailAddressENDTO:IF 1 = 2BEGIN SELECT ContactID , FirstName , MiddleName , LastName , Suffix , EmailAddress FROM Person.Contact where 2 = 4END[/quote]Mon, 29 Dec 2008 12:06:20 GMTraymond lewhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxRaymond,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--Mon, 29 Dec 2008 10:20:24 GMTSJTerrillhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxGood article. I'm not using SSIS currently, but I like the path of exploring and researching in finding a solution.I'm wondering if this would work in making the contract SQL simpler to build than the IF block.[font="Courier New"]SELECT ContactID , FirstName , MiddleName , LastName, Suffix , EmailAddress FROM #Contact where 1=2[/font]Mon, 29 Dec 2008 09:32:03 GMTraymond lewhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxA kludge, but a very clean and clever one! Thanks.Mon, 29 Dec 2008 09:28:54 GMTDavidLhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxExcellent article. Although this is not a situation I have encountered yet in SSIS, it is a good workaround to be aware of.Now that I have used SSIS for many ETL processes, I too see the value in placing some of the logic in stored procs. Especially logic which may require minor tweaks from time to time. I find it much easier to update the sproc for these small changes, than to load up the dtsx and redeploy the SSIS package.Of course, the downside is that you're splitting up the business logic and making the process just a bit harder to understand. It's not an approach I follow blindly - it's just another tool in the bag that I use when I think it makes sense.Mon, 29 Dec 2008 08:21:14 GMTEd Zannhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspx[quote]If you were using a SELECT statement as a data source, you would have to re-build and re-deploy your SSIS, which is a bit of a pain[/quote]Thanks for the explaination! I've never used SSIS for ETL so I didn't know but I'll try to keep in mind there are these gotchas if I ever do.Sun, 28 Dec 2008 18:38:41 GMTmagarityhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspx[quote][b]magarity (12/28/2008)[/b][hr]I supposed this is a dumb newbie question but I have to ask: Why use a stored procedure for such a simple select? Wouldn't a view be better and avoid the problem in the first place? I think some people use them for security reasons but I haven't figured out why permissions on the view does not suffice. Excuse my tangent![/quote]First of all the article was simply illustrating a useful technique. Secondly, using stored procedures as data sources has it's own merits. For example, let's say the underlying database schema has changed and some column got re-named. If you were using a SELECT statement as a data source, you would have to re-build and re-deploy your SSIS, which is a bit of a pain. With a stored procedure as a data source, you would simply have to modify the proc, thus likely avoiding having to make any SSIS changes. Similar benefits could be accomplished by using a view, but if the logic is complex and requires several intermediate steps, the view may not be good enough and in this case you would have to use a stored procedure.Sun, 28 Dec 2008 10:25:24 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxI supposed this is a dumb newbie question but I have to ask: Why use a stored procedure for such a simple select? Wouldn't a view be better and avoid the problem in the first place? I think some people use them for security reasons but I haven't figured out why permissions on the view does not suffice. Excuse my tangent!Sun, 28 Dec 2008 04:47:09 GMTmagarityhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxHi,This was a valuable post and reply - I didn't know about the temp table and table variable problems and the two workarounds were both good ways to handle the problem depending on the circumstances. I'm on Christmas Holiday at the moment but when I go back to work I'll be starting on a set of new SSIS packages where I will probably be looking to use a number of stored procedures so this has given me advance warning of problems I might get and workarounds so they are probably going to save me a lot of headaches and grief. So thanks for your article and comment - they are really appreciated,Bill EdeSat, 27 Dec 2008 03:44:14 GMTbilx10http://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxNice article....:)Sat, 27 Dec 2008 02:58:34 GMTAnipaulhttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxMichael, thank you for all your research. We had this exact problem several month ago while converting from DTS to SSIS. The stored procedure, which was the data source, ran for 1 hour (which was OK under the circumstances), however, as you can imagine, we couldn't have it run 3 or 5 times. That would be unacceptable. We ended up cheating by temporary replacing the real stored procedure with a fake one, which did nothing but return a 1-row result set in a required format. This allowed us to build our SSIS package and do the necessary mapping. After the package was built and deployed, we replaced the "fake" stored procedure with a real one. Of course this method has a lot of downsides, for example any modifications to the source procedure, would require performing the same trick again, you couldn't do this if the procedure were used by a live application, etc. Thank you for the article! I have some new tricks under my belt now :)Fri, 26 Dec 2008 18:35:19 GMTmishalubahttp://www.sqlservercentral.com/Forums/Topic625715-1441-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/SSIS/65112/">SSIS and Stored procedures using temp tables</A>[/B]Thu, 25 Dec 2008 21:04:16 GMTMichael Cape