Creating Dynamic Outputs in SSIS to an SQL Database

  • dave-dj

    SSChampion

    Points: 11230

    Comments posted to this topic are about the item Creating Dynamic Outputs in SSIS to an SQL Database

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Bertil Forsberg

    SSC Veteran

    Points: 283

    Hi Dave!

    A nice and clean solution, quite interesting to follow.

    My only objection right now is the phrase 'FEL' in your namings, in swedish that

    means 'WRONG'! 🙂

    Thanks,

    BF

  • fszendzielarz

    SSC Rookie

    Points: 33

    Why not just partition the table? You get parallelism too.

  • 106600.3363

    Grasshopper

    Points: 15

    A very clever solution but I think I must be missing something.

    Why can't all the logic associated with sucking the data in, creating new tables if necessary and appending the new records to the relevant table all be encompassed in one T-SQL block instead of many SSIS flows / tasks?

    Most of the required code would be virtually as Dave Lumley has already written it. That could then be called by SQL Agent? It could become a stored procedure but the performance gain would presumably be small if the quantity of INSERTs is high relative to the "think time" associated with coming up with an execution plan. The solution already requires the use of dynamic SQL and I can't see that can be avoided.

  • dave-dj

    SSChampion

    Points: 11230

    Bertil Forsberg: I've learnt something new today! every day is a school day. 🙂

    fszendzielarz: Partitioning for me was not an option as we are only using SQL Server Standard Edition - Partitioning is an Enterprise edition feature. But also, the transaction types that I refer to in the article represent different information that ideally should have been designed as different tables within a database anyway (source is a 3rd party system).

    106600.3363: Potentially, yes it could be done via T-SQL. I think you touch on a interesting point. Personally, I've used SSIS for a number of reasons:

    1 - visually you can sort of see what is going on (albeit there is some slightly more hidden details, such as defining the connection string

    2 - secondly, this is part a much larger project and the production package I use, contains several other package run related variables and a few additional task.

    3 - SSIS offers better event handling functionality. (again some of this could be done in SQL) ...... horses for courses

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Samuel Vella

    SSCertifiable

    Points: 7995

    fszendzielarz (11/10/2010)


    Why not just partition the table? You get parallelism too.

    Partitioned Tables are Enterprise Edition only

    So the answet to why not is 10's of thousands of dollars!

  • dave-dj

    SSChampion

    Points: 11230

    Bertil Forsberg (11/10/2010)


    Hi Dave!

    A nice and clean solution, quite interesting to follow.

    My only objection right now is the phrase 'FEL' in your namings, in swedish that

    means 'WRONG'! 🙂

    Thanks,

    BF

    Also I guess if I stuck to the MS naming convention suggest then it should really be FELC - but I'm not sure what that would mean in Swedish 🙂

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • fszendzielarz

    SSC Rookie

    Points: 33

    Oh yes. I forgot about that.

    Doesn't it seem ridiculous that functionality that is already implemented is disabled for those who cannot pay for it? The irony is that MS aim to support SME and break into this sphere, so why support enterprises by denying features to smaller organisations?

  • Smendle

    Hall of Fame

    Points: 3855

    I would love to see what the partitioned table solution would look like.

  • Mike Is Here

    Hall of Fame

    Points: 3348

    Are the images not showing up for anyone else?

    Even when I right click and say Show Picture nothing happens.

  • dave-dj

    SSChampion

    Points: 11230

    images are hosted on flickr - but they should be fine.

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • tferguson

    SSC Veteran

    Points: 232

    The images are also not showing up for me, either. When I hover, it says 'screenshot' or 'query extract', but a lot of blank spaces in this article.

  • dave-dj

    SSChampion

    Points: 11230

    Here is a PDF copy of the article with images for those that can't see them. ( I can only guess it's because you can't access the flickr server they are on)

    _____________________________________________________________________________
    MCITP: Business Intelligence Developer (2005)

  • Samuel Vella

    SSCertifiable

    Points: 7995

    fszendzielarz (11/10/2010)


    Oh yes. I forgot about that.

    Doesn't it seem ridiculous that functionality that is already implemented is disabled for those who cannot pay for it? The irony is that MS aim to support SME and break into this sphere, so why support enterprises by denying features to smaller organisations?

    Thats how software pricing works for almost every software company out there.

    You want more features, then you have to pay.

    SME's typically have much smaller data sets, infact even in some of the large multi national organisations I've worked for none of the enterprise features they paid for were used, even with many millions of rows, a non partitioned table structure can be made to behave.

    Where there is a need for some enterprise features, SME's then just have to evaluate the cost benefit. Is paying for the extra features in Enterprise edition cheaper or more expensive than paying someone to code round the issue?

  • tferguson

    SSC Veteran

    Points: 232

    Now I can see them - I'm further enlightened now. Thanks.

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

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