SSIS and Stored procedures using temp tables

Question

SSIS and Stored procedures using temp tables

Michael Cape

SSC Enthusiast

Points: 144
More actions
December 25, 2008 at 9:04 pm

#383637

Comments posted to this topic are about the item SSIS and Stored procedures using temp tables

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 1

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 🙂

Anipaul SSC-Insane Points: 24681 More actions · Answer 2

Anipaul

SSC-Insane

Points: 24681

December 27, 2008 at 2:58 am

#917996

Nice article....:)

bilx10 SSC Enthusiast Points: 187 More actions · Answer 3

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

magarity Ten Centuries Points: 1037 More actions · Answer 4

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!

Misha_SQL SSCertifiable Points: 5407 More actions · Answer 5

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.

magarity Ten Centuries Points: 1037 More actions · Answer 6

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.

Ed Zann SSCrazy Eights Points: 8694 More actions · Answer 7

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.

DavidL Hall of Fame Points: 3582 More actions · Answer 8

A kludge, but a very clean and clever one! Thanks.

raymond lew SSChasing Mays Points: 652 More actions · Answer 9

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.

[font="Courier New"]SELECT ContactID

, FirstName

, MiddleName

, LastName, Suffix

, EmailAddress

FROM #Contact

where 1=2[/font]

SJTerrill SSCrazy Points: 2938 More actions · Answer 10

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--

raymond lew SSChasing Mays Points: 652 More actions · Answer 11

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

sginn SSC Rookie Points: 31 More actions · Answer 12

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

Michael Cape SSC Enthusiast Points: 144 More actions · Answer 13

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.

Michael Cape SSC Enthusiast Points: 144 More actions · Answer 14

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.