<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by Michael Cape
»
SSIS and Stored procedures using temp tables
21 posts, Page 1 of 3
1
2
3
»
»»
SSIS and Stored procedures using temp tables
Rate Topic
Display Mode
Topic Options
Author
Message
Michael Cape
Michael Cape
Posted Thursday, December 25, 2008 9:04 PM
Forum Newbie
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:39 PM
Points: 8,
Visits: 297
Comments posted to this topic are about the item
SSIS and Stored procedures using temp tables
Post #625715
mishaluba
mishaluba
Posted Friday, December 26, 2008 6:35 PM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525,
Visits: 617
Michael, 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 :)
Post #626073
Anipaul
Anipaul
Posted Saturday, December 27, 2008 2:58 AM
SSCarpal Tunnel
Group: General Forum Members
Last Login: Wednesday, May 22, 2013 1:35 AM
Points: 4,789,
Visits: 1,336
Nice article....:)
Post #626108
bilx10
bilx10
Posted Saturday, December 27, 2008 3:44 AM
Forum Newbie
Group: General Forum Members
Last Login: Monday, May 13, 2013 6:53 AM
Points: 9,
Visits: 128
Hi,
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 Ede
Post #626115
magarity
magarity
Posted Sunday, December 28, 2008 4:47 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, November 30, 2009 6:40 PM
Points: 127,
Visits: 66
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!
Post #626253
mishaluba
mishaluba
Posted Sunday, December 28, 2008 10:25 AM
Mr or Mrs. 500
Group: General Forum Members
Last Login: Wednesday, May 15, 2013 5:54 PM
Points: 525,
Visits: 617
magarity (12/28/2008)
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!
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.
Post #626300
magarity
magarity
Posted Sunday, December 28, 2008 6:38 PM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Monday, November 30, 2009 6:40 PM
Points: 127,
Visits: 66
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
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.
Post #626372
Ed Zann
Ed Zann
Posted Monday, December 29, 2008 8:21 AM
SSC-Addicted
Group: General Forum Members
Last Login: Yesterday @ 7:26 AM
Points: 471,
Visits: 1,055
Excellent 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.
Post #626587
DavidL
DavidL
Posted Monday, December 29, 2008 9:28 AM
SSC-Enthusiastic
Group: General Forum Members
Last Login: Tuesday, May 14, 2013 3:45 PM
Points: 119,
Visits: 470
A kludge, but a very clean and clever one! Thanks.
Post #626633
raymond lew
raymond lew
Posted Monday, December 29, 2008 9:32 AM
SSC Rookie
Group: General Forum Members
Last Login: Friday, March 27, 2009 10:01 AM
Points: 48,
Visits: 123
Good 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.
SELECT ContactID
, FirstName
, MiddleName
, LastName, Suffix
, EmailAddress
FROM #Contact
where 1=2
Post #626636
« Prev Topic
|
Next Topic »
21 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
