SSIS and Stored procedures using temp tables

  • Michael Cape

    SSC Enthusiast

    Points: 144

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

  • Misha_SQL

    SSCertifiable

    Points: 5385

    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

    Nice article....:)

  • bilx10

    SSC Enthusiast

    Points: 187

    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

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

    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

    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

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

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

  • raymond lew

    SSChasing Mays

    Points: 652

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

    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

    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

    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

    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

    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.

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

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