Creating Dynamic Outputs in SSIS to an SQL Database

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Dave Lumley » Creating Dynamic Outputs in SSIS to an SQL...

29 posts, Page 1 of 3

1 2 3 »»»

Creating Dynamic Outputs in SSIS to an SQL Database

Rate Topic

Display Mode

Topic Options

Author

Message

dave-dj

Posted Tuesday, November 09, 2010 9:20 PM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)

Post #1018362

Bertil Forsberg

Posted Wednesday, November 10, 2010 12:19 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, September 28, 2012 9:00 AM
Points: 11, Visits: 156

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'! Smile

Thanks,
BF

Post #1018389

fszendzielarz

Posted Wednesday, November 10, 2010 12:51 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 6:59 AM
Points: 3, Visits: 13

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

Post #1018397

106600.3363

Posted Wednesday, November 10, 2010 1:49 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 2:39 AM
Points: 1, Visits: 67

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.

Post #1018413

dave-dj

Posted Wednesday, November 10, 2010 3:00 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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

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)

Post #1018448

Samuel Vella

Posted Wednesday, November 10, 2010 3:01 AM

Old Hand

Group: General Forum Members
Last Login: Saturday, June 15, 2013 10:38 AM
Points: 329, Visits: 1,869

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!

Post #1018449

dave-dj

Posted Wednesday, November 10, 2010 3:02 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, June 13, 2013 5:09 AM
Points: 380, Visits: 1,020

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'! Smile

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 Smile

_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)

Post #1018450

fszendzielarz

Posted Wednesday, November 10, 2010 5:00 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, June 05, 2013 6:59 AM
Points: 3, Visits: 13

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?

Post #1018489