Creating Dynamic Outputs in SSIS to an SQL Database

Question

Post reply

Creating Dynamic Outputs in SSIS to an SQL Database

dave-dj

SSChampion

Points: 11297
More actions
November 9, 2010 at 9:20 pm

#228690

Comments posted to this topic are about the item Creating Dynamic Outputs in SSIS to an SQL Database
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

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

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

Bertil Forsberg SSC Veteran Points: 283 More actions · Answer 1

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 More actions · Answer 2

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

106600.3363 Grasshopper Points: 15 More actions · Answer 3

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: 11297 More actions · Answer 4

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 SSCrazy Eights Points: 8055 More actions · Answer 5

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: 11297 More actions · Answer 6

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 More actions · Answer 7

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: 3969 More actions · Answer 8

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

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 9

Are the images not showing up for anyone else?

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

dave-dj SSChampion Points: 11297 More actions · Answer 10

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

_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)

tferguson SSC Veteran Points: 263 More actions · Answer 11

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: 11297 More actions · Answer 12

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 SSCrazy Eights Points: 8055 More actions · Answer 13

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: 263 More actions · Answer 14

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